Help me clean up my DB connection (trying to use parameterized query)

[darrel]

I'm tring to modify an existing DB connection/query into a parameterized
one. But, in doing so, I get an error stating that I am not defining the
variable I am using in the query.

This is what I have:

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

Dim DS As New DataSet

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

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

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

Dim objCommand As New System.Data.OleDb.OleDbCommand(strChk, objConnect)
objCommand.Parameters.Add("@categoryID", categoryID)

Dim objOleDbAdapter As New System.Data.OleDb.OleDbDataAdapter(strChk,
strConnect)
objOleDbAdapter.Fill(DS, "webPages")

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

I'm guessing it has to do with my Connection not passing the command to the
DB?

-Darrel

 

[Karl Seguin [MVP]]

try:

objCommand.Parameters.Add("@categoryID", OldDb.Numeric).Value = categoryId


karl

 

[darrel]

objCommand.Parameters.Add("@categoryID", OldDb.Numeric).Value = categoryId

Karl, thanks. Alas, OldDb isn't declared in my app. What is that referring
to?

-Darrel

 

[Karl Seguin [MVP]]

It's a typo

OleDbType is what it should say.

sorry about that

karl

 

[darrel]

OleDbType is what it should say.

Oh...ha! I should have figured that one out. I was thinking Old Database?
;o)

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

I assume that this adds one more step to the data validation process,
telling the parameter what format it is in?

Unfortunately, I am still getting a "Must declare the variable
'@categoryID'. Microsoft OLE DB Provider for SQL Server" error. Is the
problem perhaps in my SQL query syntax?

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)"

-Darrel

 

[Karl Seguin [MVP]]

it might..

if you don't specify it, .NET goes through quite a bit of trouble infering
the type from the value. I've tried to following the internal code from the
SqlCommand and it gets quite complicated (and in some cases slow)..so that's
why i mostly include it.

Karl