ExecuteScalar error

[SCG]

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

objSQLCommand = New OleDb.OleDbComm and
objSQLCommand.C onnection = objConnection
objSQLCommand.C ommandText = "SELECT PK_ID FROM tblBaz WHERE
FK_Item_ID=" & lintItemID & " AND Time_Sold=" &
cstrDateDelimit er & ldatDate & cstrDateDelimit er
lintID = objSQLCommand.E xecuteScalar()

(where cstrDateDelimit er is a constant "#")

I get the following error message on the ExecuteScalar command:
"A first chance exception of type 'System.Data.Ol eDb.OleDbExcept ion'
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


Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------

http://www.usenet.com

[Herfried K. Wagner [MVP]]

Re: ExecuteScalar error

* Sarah@another-place.freeserve .co-dot-uk.no-spam.invalid (SCG) scripsit:[color=blue]
> Am trying to read the primary key from an Access database table using
> ExecuteScaler:
>
> objSQLCommand = New OleDb.OleDbComm and
> objSQLCommand.C onnection = objConnection
> objSQLCommand.C ommandText = "SELECT PK_ID FROM tblBaz WHERE
> FK_Item_ID=" & lintItemID & " AND Time_Sold=" &
> cstrDateDelimit er & ldatDate & cstrDateDelimit er
> lintID = objSQLCommand.E xecuteScalar()
>
> (where cstrDateDelimit er is a constant "#")
>
> I get the following error message on the ExecuteScalar command:
> "A first chance exception of type 'System.Data.Ol eDb.OleDbExcept ion'
> occurred in system.data.dll "[/color]

You will more likely get an answer here:

<news:microsoft .public.dotnet. framework.adone t>

--
Herfried K. Wagner [MVP]
<http://dotnet.mvps.org/>

[Tom Leylan]

Re: ExecuteScalar error

"SCG" <Sarah@anothe r-place.freeserve .co-dot-uk.no-spam.invalid> wrote...
[color=blue]
> objSQLCommand = New OleDb.OleDbComm and
> objSQLCommand.C onnection = objConnection
> objSQLCommand.C ommandText = "SELECT PK_ID FROM tblBaz WHERE
> FK_Item_ID=" & lintItemID & " AND Time_Sold=" &
> cstrDateDelimit er & ldatDate & cstrDateDelimit er
> lintID = objSQLCommand.E xecuteScalar()
>
> (where cstrDateDelimit er is a constant "#")[/color]
[color=blue]
> 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!?[/color]

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
cstrDateDelimit er. 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

[Ron Allen]

Re: ExecuteScalar error

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.C ommandText = "SELECT PK_ID FROM tblBaz WHERE (
FK_ItemID = ?) AND (Time_Sold = ?)"
objSQLCommand.P arameters.Add(" item", OleDbType.Int). Value = lintItemID
objSQLCommand.P arameters.Add(" dt", OleDbType.DateT ime).Value = ldatDate
==============
If you want to just do this once you probably need lintItemID.ToSt ring() and
ldatDate.ToStri ng() assuming that the variables are an integer and a
DateTime respectively.

Depending on what is stored in your database you may need to use
ToShortDateStri ng() 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.publi c.dotnet.framew ork.adonet.

Ron Allen
"SCG" <Sarah@anothe r-place.freeserve .co-dot-uk.no-spam.invalid> wrote in
message news:40488360$1 _2@Usenet.com.. .[color=blue]
> Am trying to read the primary key from an Access database table using
> ExecuteScaler:
>
> objSQLCommand = New OleDb.OleDbComm and
> objSQLCommand.C onnection = objConnection
> objSQLCommand.C ommandText = "SELECT PK_ID FROM tblBaz WHERE
> FK_Item_ID=" & lintItemID & " AND Time_Sold=" &
> cstrDateDelimit er & ldatDate & cstrDateDelimit er
> lintID = objSQLCommand.E xecuteScalar()
>
> (where cstrDateDelimit er is a constant "#")
>
> I get the following error message on the ExecuteScalar command:
> "A first chance exception of type 'System.Data.Ol eDb.OleDbExcept ion'
> 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
>
>
> Posted Via Usenet.com Premium Usenet Newsgroup Services
> ----------------------------------------------------------
> ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
> ----------------------------------------------------------
> http://www.usenet.com[/color]