Question Parametized Queries

[SeanK]

Reference this thread from yesterday.

I'm trying to write my first parametized INSERT query. I have an ADODB.Command oject defined as cn. However, I'm getting no intellisense listings for ComandText or Parameters, etc. A snippet of the code from the referenced thread is

sSQL = "INSERT INTO movieInfo (title, actor, producer, comment) VALUES (?,?,?,?)
 
objCommand.CommandText = sSQL
objCommand.CommandType = adCommandText
objCommand.Parameters.Add objCommand.CreateParameter("title",advarchar,50, txtTitle.Text)
objCommand.Parameters.Add objCommand.CreateParameter("actor",advarchar,50, txtactor.Text)
objCommand.Parameters.Add objCommand.CreateParameter("producer",advarchar,50, txtproducer.Text)
objCommand.Parameters.Add objCommand.CreateParameter("comment",advarchar,50, txtComment.Text)
 
objCommand.Execute

Although I can't find where objCommand is declared in that thread, I assumed it was as ADODB.Connection. If not, then what is it? What do I need for the .Parameters.Add and .CommandType, etc?

[si_the_geek]

You've got it backwards - objCommand should be an ADODB.Command, and cn should be a connection (tho you dont seem to be using it).

[SeanK]

You've got it backwards - objCommand should be an ADODB.Command, and cn should be a connection (tho you dont seem to be using it).

Now, I'm only semi confused.

So, I need a variable, call is adoCmd declared as ADODB.Command, and that is going to give me all of the things I need to build the query (I just tested it, and that works fine - thanks si_the_geek )

However, were I to not use a parametized (how do you spell that word anyway? ) query, I would write a standard INSERT INTO table (blah, blah) VALUES (text1.text, text2.text) and I would execute this using the execute method of the connection object: cn.Execute ssql

I can execute an INSERT query with a command object????

[techgnome]

Sean - Yes you can....

In your referenced thread, objCommand is an ADODB.Command object (hence the Command part of objCommand). The other assumption in that thread is that you also set the objCommand.ActiveConnection to a valid, open connection before performing the execute.

If I can find a decent one, I'll post a real-world example of how this is all done.

-tg

[SeanK]

If I can find a decent one, I'll post a real-world example of how this is all done.

-tg

That would be VERY cool. In addition to the security aspects that you and Shuja Ali pointed out in the other thread, the INSERT I have to do deals with 71 fields. It would be a whole heck of a lot easier to put 71 question marks in the VALUES clause that putting 71 textbox names in the VALUES clause and hope you got the right number, in the right order, with the right puncutation.

[techgnome]

Darn right it would be.... I'll get one as soon as I can...
I won't forget...

-tg

[SeanK]

Darn right it would be.... I'll get one as soon as I can...
I won't forget...

-tg

Did you ever get a chance to put together some examples of using parameteized queries for UPDATES, DELETES, INSERTS and/or SELECTS?

[Hack]

Did you ever get a chance to put together some examples of using parameteized queries for UPDATES, DELETES, INSERTS and/or SELECTS?

He is putting together something for the codebank.

[techgnome]

It's been a slow painful process... life keeps getting in the way, it is certanly on my to do list.

-tg