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

  • Thread starter Thread starter darrel
  • Start date Start date
D

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
 
try:

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


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

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

-Darrel
 
OleDbType is what it should say.
Click to expand...

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
 
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
 
Back
Top