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.AppSett ings("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

--
http://www.openmymind.net/
http://www.fuelindustries.com/
"darrel" <no*****@nowhere.com> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl...

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.AppSett ings("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

[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

--
http://www.openmymind.net/
http://www.fuelindustries.com/
"darrel" <no*****@nowhere.com> wrote in message
news:OD****************@TK2MSFTNGP15.phx.gbl...

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

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

-Darrel

[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
--
http://www.openmymind.net/
http://www.fuelindustries.com/
"darrel" <no*****@nowhere.com> wrote in message
news:%2***************@TK2MSFTNGP15.phx.gbl...

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