Parameterized Update query in VB6

[Andrzejek]

I have a table in Oracle (MyTable) where – among other fields – I have:[pre]
NEEDS_ID NUMBER(2),
OBJ_ID NUMBER(2),
OBJ_COMMENTS VARCHAR2(250 BYTE),
TIME21_ID NUMBER(2)[/pre]

I do have a (ADODB) connection to my data base (Cn), and so far I’ve been using something like:

[pre]
strSQL = "Update MyTable Set " _
& " NEEDS_ID = " & cboNeeds.Text & ", " _
& " OBJ_ID = " & cboObj.Text & ", " _
& " OBJ_COMMENTS = ‘" & txtComments.Text & "’, " _
& " TIME21_ID = " & cboTime.Text & _
& " WHERE SomeID = " & intSomeID

Cn.Execute strSQL
[/pre]
It’s about time I would use parameterized query instead to Update those fields to avoid an SQL injection.

I did my search on the Web, but there are many confusing examples out there.
Any (simple) help would be much appreciated.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Andy,

How about searching ADO HELP?

Search for ADO*.chm on your hard drive.

Lots of good examples.

 

[Andrzejek]

Thank you Skip, but searching all the ADO chm files is like searching the Web. :-(

Anyway, I did manage to put some logic together and it works:

Dim strSQL As String
Dim cmd As New ADODB.Command

strSQL = "UPDATE MyTable SET " & vbNewLine _
    & " NEEDS_ID     = ?, " & vbNewLine _
    & " OBJ_ID       = ?, " & vbNewLine _
    & " OBJ_COMMENTS = ?, " & vbNewLine _
    & " TIME21_ID    = ?  " & vbNewLine _
    & " WHERE ID     = ?"

With cmd
    .ActiveConnection = Cn
    .CommandText = strSQL
    .Parameters.Append .CreateParameter("NEEDS_ID", adInteger, adParamInput, 2, 12)
    .Parameters.Append .CreateParameter("OBJ_ID", adInteger, adParamInput, 2, 23)
    .Parameters.Append .CreateParameter("OBJ_COMMENTS", adVarChar, adParamInput, 250, "Some text")
    .Parameters.Append .CreateParameter("TIME21_ID", adInteger, adParamInput, 2, 34)
    .Parameters.Append .CreateParameter("ID", adInteger, adParamInput, 18, 456)
    .Execute
End With

I guess the main deal here is to Append/Create parameters in the order they are used in the SQL for Command’s CommandText, and the names for parameters don’t matter that much. And 4th parameter in CreateParameter should match the size of the Field in the table.

If I did any 'Ooops' in my logic/syntax, I would appreciate any 'constructive criticism'

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Huh? I got ONE on mine, but it's a new laptop (my retirement gift) and maybe does nor have previous versions that an older system might have.

Maybe the latest one? Anyhow, it's a valuable resource if yer coding ADO objects from time to time.

 

[gmmastros]

try this:

Dim strSQL As String
Dim cmd As New ADODB.Command

strSQL = "UPDATE MyTable SET " & vbNewLine _
    & " NEEDS_ID     = [!]@NEEDS_ID[/!], " & vbNewLine _
    & " OBJ_ID       = @OBJ_ID, " & vbNewLine _
    & " OBJ_COMMENTS = @OBJ_COMMENTS, " & vbNewLine _
    & " TIME21_ID    = @TIME21_ID, " & vbNewLine _
    & " WHERE ID     = @WHEREID"

With cmd
    .ActiveConnection = Cn
    .CommandText = strSQL
    .Parameters.Append .CreateParameter("[!]@NEEDS_ID[/!]", adInteger, adParamInput, 2, 12)
    .Parameters.Append .CreateParameter("@OBJ_ID", adInteger, adParamInput, 2, 23)
    .Parameters.Append .CreateParameter("@OBJ_COMMENTS", adVarChar, adParamInput, 250, "Some text")
    .Parameters.Append .CreateParameter("@TIME21_ID", adInteger, adParamInput, 2, 34)
    .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, 18, 456)
    .Execute
End With

With this change, I suspect that the order in which you create the parameters will not matter.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dilettante]

You can usually bypass the need to fiddle with the parameters collection by passing a Variant containing an array of parameter values:

With cmd
    .CommandText = "UPDATE MyTable SET NEEDS_ID = ?," _
                 & "OBJ_ID = ?," _
                 & "OBJ_COMMENTS = ?," _
                 & "TIME21_ID = ? "_
                 & "WHERE ID = ?"
    Set .ActiveConnection = Cn
    .Execute , _
             Array(CLng(cboNeeds.Text), _
                   CLng(cboObj.Text), _
                   txtComments.Text, _
                   CLng(cboTime.Text), _
                   intSomeID), _
             adCmdText Or adExecuteNoRecords
End With

Better make up your mind about data types though, i.e. [tt]adInteger[/tt] corresponds to a VB6 [tt]Long[/tt] and not an [tt]Integer[/tt].

 

[Andrzejek]

gmmastros,
I did try your suggestion of using the (red) named parameters, but I've got 'missing expression' error on Execute statement. I then try to eliminate some @ signs (I saw it in some examples parameters’ names with and without @) but that gave me the same ‘missing expression’ error. So I am back to '?'

dilettante,
Thank you for that tip, I have to remember about adInteger vs Long
I did try your suggestion and it worked part of the time, but I had some weird errors when I had to update fields to NULL


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[fredericofonseca]

George code had a minor error on it

Dim strSQL As String
Dim cmd As New ADODB.Command

strSQL = "UPDATE MyTable SET " & vbNewLine _
& " NEEDS_ID = @NEEDS_ID, " & vbNewLine _
& " OBJ_ID = @OBJ_ID, " & vbNewLine _
& " OBJ_COMMENTS = @OBJ_COMMENTS, " & vbNewLine _
& " TIME21_ID = @TIME21_ID, " & vbNewLine _
& " WHERE ID = @WHEREID"

With cmd
.ActiveConnection = Cn
.CommandText = strSQL
.Parameters.Append .CreateParameter("@NEEDS_ID", adInteger, adParamInput, 2, 12)
.Parameters.Append .CreateParameter("@OBJ_ID", adInteger, adParamInput, 2, 23)
.Parameters.Append .CreateParameter("@OBJ_COMMENTS", adVarChar, adParamInput, 250, "Some text")
.Parameters.Append .CreateParameter("@TIME21_ID", adInteger, adParamInput, 2, 34)
.Parameters.Append .CreateParameter("@[highlight #8AE234]WHEREID[/highlight]", adInteger, adParamInput, 18, 456)
.Execute
End With

using named parameters means order isn't important - otherwise yes they need to be on the order they show up on the sql.

using @ is better in a way as if you are using SP's you do need to use them in any case, so for clarity using them always is best practice

Datatypes is what will "kick you" - sometimes you will need to pass a variable as a string and convert it on the sql to the desired datatype.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886