Still stuck with paramaterized SQL.

[darrel]

I've been struggling with getting paramaterized SQL to work for a few days.
I got a bit of help last week and seemed to heading in the right direction,
bu I keep getting a 'must declare @cateogoryID variable' error when trying
to execute the following. Does anyone see something obviously wrong with my
syntax?

===============================

Dim strConnect As String
strConnect =
System.Configuration.ConfigurationSettings.AppSettings("DBConn")


Dim objConnect As New System.Data.OleDb.OleDbConnection(strConnect)
objConnect.Open()

Dim strChk As String
strChk = "SELECT CAT.categoryID, CAT.categoryParentID, CAT.categoryName,
PARENTCAT.categoryName AS parentCategoryName FROM We_about_categories CAT
LEFT OUTER JOIN We_about_categories PARENTCAT ON CAT.categoryParentID =
PARENTCAT.categoryID WHERE(CAT.categoryID = @categoryID)"

Dim objCommand As New System.Data.OleDb.OleDbCommand(strChk, objConnect)

Dim objOleDbAdapter As New System.Data.OleDb.OleDbDataAdapter(strChk,
strConnect)

objCommand.Parameters.Add("@categoryID",
System.Data.OleDb.OleDbType.Numeric).Value = categoryID

objOleDbAdapter.SelectCommand = objCommand

objOleDbAdapter.Fill(DS, "webPages")

===============================

 

[Darren Kopp]

Need to define the parameter before you try to assign the value.

objCommand.Parameters.Add("@categoryID",
System.Data.OleDb.OleDbType.Numeric)
objCommand.Parameters("@categoryID").Value = categoryID

HTH,
Darren Kopp
http://blog.secudocs.com/

 

[Darren Kopp]

Need to define the parameter before you try to assign the value.

objCommand.Parameters.Add("@categoryID",
System.Data.OleDb.OleDbType.Numeric)
objCommand.Parameters("@categoryID").Value = categoryID

HTH,
Darren Kopp
http://blog.secudocs.com/

 

[darrel]

Need to define the parameter before you try to assign the value.

objCommand.Parameters.Add("@categoryID",
System.Data.OleDb.OleDbType.Numeric)
objCommand.Parameters("@categoryID").Value = categoryID

Still get the same error.

-Darrel

 

[Darren Kopp]

blast my infernal skimming... i don't know about oledb commands a whole
lot, but it may need something in the sql statement. i know with like
stored procedures you declare the variable @categoryID int. i think
you need to declare it in text if possible... and i say if possible
because of this link, showing that what you are doing you would just
have to use ? as the placeholder.

http://msdn.microsoft.com/library/d...DataOleDbOleDbCommandClassParametersTopic.asp

HTH,
Darren Kopp
http://blog.secudocs.com/

 

[darrel]

and i say if possible

because of this link, showing that what you are doing you would just
have to use ? as the placeholder.

YES! THAT WAS IT!

Whew. Thanks, Darren!

I imagine most of the examples I saw were perhaps MySQL centric.

Again, thanks!

-Darrel

 

[Darren Kopp]

Yes, and most of the ones that I had seen like the one you had done
were Sql Server commands. Glad to hear that it's working.

Happy .NETing,
Darren Kopp
http://blog.secudocs.com/