ExecuteScalar error

S

SCG

Am trying to read the primary key from an Access database table using
ExecuteScaler:

objSQLCommand = New OleDb.OleDbCommand
objSQLCommand.Connection = objConnection
objSQLCommand.CommandText = "SELECT PK_ID FROM tblBaz WHERE
FK_Item_ID=" & lintItemID & " AND Time_Sold=" &
cstrDateDelimiter & ldatDate & cstrDateDelimiter
lintID = objSQLCommand.ExecuteScalar()

(where cstrDateDelimiter is a constant "#")

I get the following error message on the ExecuteScalar command:
"A first chance exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll"

Can anyone a) tell me what I'm doing wrong or b) tell me how to get a
better idea of what the error is all about!?

Many Thanks

Sarah
 
H

Herfried K. Wagner [MVP]

* (e-mail address removed)-dot-uk.no-spam.invalid (SCG) scripsit:
Am trying to read the primary key from an Access database table using
ExecuteScaler:

objSQLCommand = New OleDb.OleDbCommand
objSQLCommand.Connection = objConnection
objSQLCommand.CommandText = "SELECT PK_ID FROM tblBaz WHERE
FK_Item_ID=" & lintItemID & " AND Time_Sold=" &
cstrDateDelimiter & ldatDate & cstrDateDelimiter
lintID = objSQLCommand.ExecuteScalar()

(where cstrDateDelimiter is a constant "#")

I get the following error message on the ExecuteScalar command:
"A first chance exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll"
Click to expand...

You will more likely get an answer here:

<
 
T

Tom Leylan

SCG said:
objSQLCommand = New OleDb.OleDbCommand
objSQLCommand.Connection = objConnection
objSQLCommand.CommandText = "SELECT PK_ID FROM tblBaz WHERE
FK_Item_ID=" & lintItemID & " AND Time_Sold=" &
cstrDateDelimiter & ldatDate & cstrDateDelimiter
lintID = objSQLCommand.ExecuteScalar()

(where cstrDateDelimiter is a constant "#")
Click to expand...
Can anyone a) tell me what I'm doing wrong or b) tell me how to get a
better idea of what the error is all about!?
Click to expand...

I think I can help you narrow it down. Don't know what you have tried so
far but _any time_ you have a complex statement that generates an error it
is a good idea to simplify that statement.

You would be much better off using parameters (so look into that after you
find the problem) but the first thing you should try is to hardcode your
command text string. Don't concatenate anything and just type constants
where your variables are. You want to make certain the basic form is
correct, that the columns are spelled correctly and such. When you get that
working put back just one of the variables, for instance the lintItemID...
and again the point is to make incremental changes until you can find the
point where it breaks.

BTW, you might consider a helper function in place of concatenating
cstrDateDelimiter. Little helper functions are handy for formatting SQL
dates and strings and such. But as I mentioned earlier you should consider
using the parameters feature which eliminates concatenation altogether.

I assume lintID is of the same datatype as PK_ID right? And I assume you
have successfully accessed the file in some way previously? Your connection
string is properly formed correct?

Tom
 
R

Ron Allen

Sarah,
My first thought would be to make this a command with parameters to
avoid having to work with the concatenation each time.
ex
==============
objSQLCommand.CommandText = "SELECT PK_ID FROM tblBaz WHERE (
FK_ItemID = ?) AND (Time_Sold = ?)"
objSQLCommand.Parameters.Add("item", OleDbType.Int).Value = lintItemID
objSQLCommand.Parameters.Add("dt", OleDbType.DateTime).Value = ldatDate
==============
If you want to just do this once you probably need lintItemID.ToString() and
ldatDate.ToString() assuming that the variables are an integer and a
DateTime respectively.

Depending on what is stored in your database you may need to use
ToShortDateString() for the DateTime variable or you won't get equality due
to the time part.

I'd reccomend that you get a copy of David Sceppa's book "ADO.NET Core
Reference" if you are going to be doing a lot of ADO.NET programming. It
has many good examples in both C# and VB.NET along with reasons for doing
the coding that way.

ADO.NET questions will receive better responses in
microsoft.public.dotnet.framework.adonet.

Ron Allen
SCG said:
Am trying to read the primary key from an Access database table using
ExecuteScaler:

objSQLCommand = New OleDb.OleDbCommand
objSQLCommand.Connection = objConnection
objSQLCommand.CommandText = "SELECT PK_ID FROM tblBaz WHERE
FK_Item_ID=" & lintItemID & " AND Time_Sold=" &
cstrDateDelimiter & ldatDate & cstrDateDelimiter
lintID = objSQLCommand.ExecuteScalar()

(where cstrDateDelimiter is a constant "#")

I get the following error message on the ExecuteScalar command:
"A first chance exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll"

Can anyone a) tell me what I'm doing wrong or b) tell me how to get a
better idea of what the error is all about!?

Many Thanks

Sarah
Click to expand...
 
Top