ASP/MySQL - Parameterized Query(s)

In my example code below, you can see that I have been trying to suss-out parameterized queries in ASP and MySQL.

I am doing something wrong here and would like to know what it is. In my example, you can see two queries. If I leave off the last query (under the '//////// line), this script works. As soon as I add the last query, I get the following error:

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

I'm really not sure what I am doing wrong. I googled the error and it said something about data types but it didn't register in my empty head!

Am I declaring the parameters (.createParameter) in the right place, as I'm processing multiple queries? Do they have to be declared before all the queries?

My 开发者_高级运维Code

Set connContent = Server.CreateObject("ADODB.Connection") 
connContent.ConnectionString="...blah..blah..blah..."
connContent.Open

Set cmdContent = Server.CreateObject("ADODB.Command")
Set cmdContent.ActiveConnection = connContent

cmdContent.Prepared = True

Const ad_varChar = 200
Const ad_ParamInput = 1
Const ad_Integer = 3
Const ad_DBDate = 133 
Const ad_DBTimeStamp = 135

theNumber = 23
theText = "Hello there!"
theDate = "2011-10-15"

SQL = " INSERT INTO testTable (integerCol) VALUES (?); "

Set newParameter = cmdContent.CreateParameter("@theNumber", ad_Integer, ad_ParamInput, 50, theNumber)
cmdContent.Parameters.Append newParameter

cmdContent.CommandText = SQL
cmdContent.Execute

' ////////////

SQL = " INSERT INTO testTable (varCharCol) VALUES (?); "

Set newParameter = cmdContent.CreateParameter("@theText", ad_varChar, ad_ParamInput, 50, theText)
cmdContent.Parameters.Append newParameter

cmdContent.CommandText = SQL
cmdContent.Execute

UPDATE:

Well I got both queries to work but I had to set another command object and active connection, shown below. Although it works, is this the right thing to do with my type of connection? Do I need to set the command object to nothing after each query then?

' ////////////

Set cmdContent = Server.CreateObject("ADODB.Command")
Set cmdContent.ActiveConnection = connContent

SQL = " INSERT INTO testTable (varCharCol) VALUES (?); "

Set newParameter = cmdContent.CreateParameter("@theText", ad_varChar, ad_ParamInput, 50, theText)
cmdContent.Parameters.Append newParameter

cmdContent.CommandText = SQL
cmdContent.Execute

---

I believe your problem is because both insert statements are using the same command object. Because of that, the second command will have both parameters in it and that is what I believe causes the exception you are seeing.

To fix the problem, add:

Set cmdContent = Server.CreateObject("ADODB.Command")
Set cmdContent.ActiveConnection = connContent

after your //// comment and things should start working.