Database

Re: SQLDMO for SQL Server 2000

22-Jan-06 20:25

usernamed wrote:
srv.Connect(this.cmbServer.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);

If you are using Windows Authentication why are you passing a user name and password? With Windows Authentication your credentials are passed automatically, this is sending a specific username and password which SQL Server will interpret as being for a SQL Server Account (not a windows account).

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

usernamed22-Jan-06 23:38

usernamed

22-Jan-06 23:38

Hi Colin,

Thanks for responding. I should have written in my initial post that I'd tried not passing a username or password, but was getting the following error:

No overload for method 'Connect' takes '1' arguments(CS1501)

As such, I thought that the Connect Method forced me to put in a username and password, even if they shouldn't be required.

Problem with SQL statement

Ahmed Al-Lakani22-Jan-06 11:45

Ahmed Al-Lakani

22-Jan-06 11:45

Hi,

I’m trying to run the following SQL statement against Oracle, but it fails
It generate the following error message
“missing double quote in identifier”

The code :

stBuilder = new StringBuilder();<br />
stBuilder.Append("UPDATE TABLE ");<br />
stBuilder.Append("SET COL_NAME = '{0}' , ");<br />
stBuilder.Append("COL_ADRESS = '{1}' ");<br />
stBuilder.Append("WHERE ");<br />
stBuilder.Append("COL_ID = '{2}' ");<br />
<br />
stUpdate = String.Format(stBuilder.ToString(), <br />
drRow["COL_NAME"].ToString(),<br />
drRow["COL_ADRESS"].ToString(),<br />
drRow["COL_ID"].ToString());

thanks Much

Lakani

Colin Angus Mackay22-Jan-06 20:21

22-Jan-06 20:21

You are injecting values directly into the SQL statement. This will make your application vulnerable to a SQL Injection Attack. You should learn how to prevent that. See SQL Injection Attack and Tips on How to Prevent Them.[^] (Although this article is aimed as SQL Server 2000 - the advice it gives is applicable to all database systems, includint Oracle)

My guess is that the error is caused by you blindly injecting values into the SQL statement. Values which may need some characters to be escaped in order to be interpreted correctly. The above article will also help to correct that error.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Ahmed Al-Lakani23-Jan-06 2:49

Ahmed Al-Lakani

23-Jan-06 2:49

hello ColinMackay.net,
Well this article didn’t resolve my issue,
Anyway, I’ve discovered what its all about,

The data was having special characters in it,
Ex : COL_NAME = “Baryy’@123”

So I did something like this

stUpdate = String.Format(stBuilder.ToString(),
drRow["COL_NAME"].ToString().Replace("'", "''"),
drRow["COL_ADRESS"].ToString().Replace("'", "''"),
drRow["COL_ID"].ToString());

Thanks ColinMackay.net for your help,

Colin Angus Mackay23-Jan-06 3:31

23-Jan-06 3:31

Ahmed El-Lakani wrote:
Well this article didn’t resolve my issue,
Anyway, I’ve discovered what its all about,

I suggested it because your code had serious security issues that someone could use to attack your database. No bloody wonder the media is full of reports of security problems in software if software developers, when having their security holes pointed out to them do nothing about it!

Also, as I said the article was geared towards SQL Server but the advice is still valid and sound for Oracle, or any other database system. Using parameterised queries would have solved your problem.

Ahmed El-Lakani wrote:
So I did something like this

If you took the general advice in the article it would have solved your problem and the security issue in one. Please, please, please learn how to use PARAMETARIZED QUERIES in Oracle.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Ahmed Al-Lakani23-Jan-06 5:21

Ahmed Al-Lakani

23-Jan-06 5:21

Thanks Much ColinMackay.net,

I know your concerns well, and me and my Company really crazy about security holes we get in, because of careless code,

But Unfortunately I’m tide to our customer database, we are not allowed to modify it Frown | :(

Thanks Much for your help,

Colin Angus Mackay23-Jan-06 7:00

Re: date error in sql server

23-Jan-06 7:00

Ahmed El-Lakani wrote:
But Unfortunately I’m tide to our customer database, we are not allowed to modify it

That is not an excuse. Oracle support parametarised queries so you can use them. A quick search on the web for Paramerarized query oracle .NET[^] returns a number of results about how to use paramerarized queries (and therefore follow the advice in the article I linked to earlier)

On the first page of results is Securing a .NET application on the Oracle Database[^] written by Oracle themselves. In it there is a section on "SQL Injection Attacks" which shows you how to use OracleParameter object in .NET

Does this help?

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

date error in sql server

md_refay22-Jan-06 4:18

md_refay

22-Jan-06 4:18

hi i create school database and i make sp to insert employees information but
i can't insert date i want to know how to write the dates in sql server
like #1/1/2005# or '1/1/2005' or what

second

i want to modify the date apperance when i reterive it from table instead of
mdy it display ion dmy
answer me quickly thanks

ma_refay

Colin Angus Mackay22-Jan-06 20:17

CManualAccessor - No rows if using properties

22-Jan-06 20:17

SQL Server can accept dates in various formats, but for consistency and locale neutrality I always use ISO Format - 'YYYY-MM-DD'

ma_refay wrote:
i want to modify the date apperance when i reterive it from table instead of
mdy it display ion dmy

Retrieve it to where? In .NET applications the date is retrieved into a DateTime object which will display it in what ever format you like.

It is a poor design descision to get the database to perform UI functions (such as rendering the date in a specific format).

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Guybrush21-Jan-06 6:12

Guybrush

21-Jan-06 6:12

Hello all, I have been looking everywhere for a solution to this, but I couldn't find it.
I am trying to access a database using OLEDB Consumer Templates (of which I have little experience) in a WTL project.
Ok, the problem is that when I open a CCommand object passing a DBPropSet structure I get no rows in return.

Let me try to explain with the code..

With the following code everything works fine. I get columns and rows correctly (I removed a lot of stuff such as error handling, to simplify). The code it’s more or less the same used in the MSDN sample DBViewer:

<code>
CCommand<CManualAccessor> oCmd;
oCmd.Open(m_session, sSQL, NULL, &nRows, DBGUID_DEFAULT, FALSE)

if (oCmd.m_spRowset == NULL)
{
//ERROR STUFF
}

if (oCmd.GetColumnInfo(&ulColumns, &pColumnInfo, &pStrings) != S_OK)
ThrowOleDBError(oCmd.m_spRowset, IID_IColumnsInfo);
struct MYBIND* pBind = new MYBIND[ulColumns];

oCmd.CreateAccessor(ulColumns, &pBind[0], sizeof(MYBIND)*ulColumns);
for (ULONG l = 0; l < ulColumns; l++)
oCmd.AddBindEntry(l + 1, DBTYPE_STR, sizeof(TCHAR)*255, &pBind[l].szValue, NULL, &pBind[l].dwStatus);

oCmd.Bind();

ULONG ulFields = oCmd.GetColumnCount();

while(oCmd.MoveNext() == S_OK)
{
for (ULONG j = 1; j <= ulFields; j++)
{
lpszString = pBind[j-1].szValue;
// PRINTING STUFF
}
}
</code>

Now, if I add properties to the command using this:

<code>
CDBPropSet pset( DBPROPSET_ROWSET );
pset.AddProperty(DBPROP_IRowsetView, true);
pset.AddProperty(DBPROP_IRowsetScroll, true);
pset.AddProperty(DBPROP_IRowsetChange, true);
pset.AddProperty(DBPROP_UPDATABILITY,
DBPROPVAL_UP_INSERT | DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_DELETE);
pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

/*…*/

oCmd.Open(m_session, sSQL, &pset, &nRows, DBGUID_DEFAULT, FALSE)

/*…*/
</code>

I get the columns shifted right (like if there is an empty column before all the others), and no records.
The m_spRowset member however it’s not null, as I check for it after the call to Open()

Can you please help me out, or at least tell me why this happens?

Thank you very much!

Guybrush Threepwood Smile | :)

-- modified at 12:14 Saturday 21st January, 2006

Connect to DB through proxy?

veljkoz21-Jan-06 5:15

veljkoz

21-Jan-06 5:15

The application is running behind a proxy server which is needed for internet access. The database needed for the application is on a remote server.

How can I make a connection to the database through this proxy?

The browser (IE) that is set up for proxy has access to the internet that way.

When I try the application from a computer that doesn't use proxy, it's working just fine.

I've tried something like:

<br />
System.Net.GlobalProxySelection.Select = New System.Net.WebProxy("proxy.xxxxx.xx.xx.xx", 8080)<br />
Dim conn As SqlConnection = New SqlConnection("server=xxxx;uid=testdata;pwd=sa;database=testdb")<br />
conn.Open()

but, it doesn't work...

Thank you in advance!
Veljko

Northwind

TheEagle21-Jan-06 4:53

TheEagle

21-Jan-06 4:53

Hi..
I have installed SQL server 2000 personal edition but i couldnt find the Northwind database how could i install it and where could i find it?

Re: Northwind

Dave Kreskowiak21-Jan-06 18:07

Dave Kreskowiak

21-Jan-06 18:07

Did you try looking on the front page of Microsoft's SQL Server 2000 Downloads[^] site?

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

-- modified at 0:08 Sunday 22nd January, 2006

Re: Northwind

TheEagle21-Jan-06 21:02

TheEagle

21-Jan-06 21:02

You are right i found them there..
I needed those samples to understand my SQL books lessons
thank you very very much Smile | :)

Error -2147217887 Dataset used by another user

cosma21721-Jan-06 0:24

cosma217

21-Jan-06 0:24

Good morning
First, the error message is free translated because I'm german and I could not find the english translation.

My Problem. I have three table
- Temp_HD_serial_Number with the fields > ID & Harddisk_Number
- Temp_Media_Stored_Path with the fields > ID & Media_Path
- Temp_Media_Base with the fields > ID & Rel_HD_Serial_Number & Rel_Media_Stored_Path & MediaName

Table Temp_HD_serial_Number field ID has a 1:n relation to
table Temp_Media_Base field Rel_HD_Serial_Number.

Table Temp_Media_Stored_Path field ID has a 1:n relation to
table Temp_Media_Base field Rel_Media_Stored_Path.

My code

Dim rs, SQL, AutoID

rs = CreateObject("ADODB.Recordset")
SQL = "SELECT Temp_Media_Base.ID, Temp_Media_Base.Rel_HD_Serial_Number, _
Temp_Media_Base.Rel_Media_Stored_Path, Temp_Media_Base.MediaName FROM Temp_Media_Base;"

rs.open(SQL, ConString, 3, 3)

rs.AddNew()
rs.Fields("Rel_HD_Serial_Number").Value = G_HDserialID
rs.Fields("Rel_Media_Stored_Path").Value = arg_idMediaPath
rs.Fields("MediaName").Value = arg_MediaFile
rs.Update()

AutoID = rs.Fields("ID").Value

rs.Close()
rs = Nothing

The error -2147217887 occours during rs.Update(). The surprisingly, sometimes up to 20 records can be stored in the tables and sometimes less, but never 0.

I surved the internet a long time but this special behave I could not find. Is there anybody who can assist me to solve that problem ?

I'm a programming newbe.

Thanks in advance Theo

Mike Dimmick22-Jan-06 0:27

Mike Dimmick

22-Jan-06 0:27

-2147217887 is 0x80040E21 in hex. Looking this up in oledberr.h in the Platform SDK shows that this error is DB_E_ERRORSOCCURRED. The text description of the error is: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

You should examine the Errors collection on your Recordset's Connection property to see the errors that actually occurred.

Stability. What an interesting concept. -- Chris Maunder

cosma21722-Jan-06 10:07

cosma217

22-Jan-06 10:07

And this is my problem. I'm not the messages in the error collector. (NewBie)

But I forgot to mention that I have 2 conection to the access database open.

I tried it with just one open connection.. and it works. That means I have to
access the table sequential and everything is working. But I don't know whether this
behave is OK. Do you have any idea why 2 connection parallel are not working ?

WoutL22-Jan-06 22:28

WoutL

22-Jan-06 22:28

The problem is the way Access locks records. You can read about this on Technet[^]

Wout Louwers