ASP/MySQL - Parameterized Query(s)

Question

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

Jeff S · Accepted Answer · 2011-10-14 01:25:52Z

2

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.

answered Oct 14, 2011 at 1:25

Jeff S

7,49432 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TheCarver Over a year ago

haha I literally just found that out and updated my question at the same time! Do I need to set my last 'activeConnection' and 'command' object to 'nothing' after each query? My ADO connection knowledge is limited..

Jeff S Over a year ago

No, no need to do anything to the command object. You might also want to enclose your Connection (not commands) in a Try/Finally so that you can make sure the connection gets closed.

TheCarver Over a year ago

That's interesting. Could I ask you very nicely for a small example of this?

TheCarver Over a year ago

I understand what try, catch and error do and how to use them but I don't understand where you advised me to place them in regards to my example.

TheCarver Over a year ago

Is Try/Finally actually in ASP/VBScript - I don't think it is

|

Collectives™ on Stack Overflow

ASP/MySQL - Parameterized Query(s)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related