INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Parameterized Update query in VB6

Parameterized Update query in VB6

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

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

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
 
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.

RE: Parameterized Update query in VB6

Andy,

How about searching ADO HELP?

Search for ADO*.chm on your hard drive.

Lots of good examples.

RE: Parameterized Update query in VB6

(OP)
Thank you Skip, but searching all the ADO chm files is like searching the Web. sad

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

CODE

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.

RE: Parameterized Update query in VB6

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.

RE: Parameterized Update query in VB6

try this:

CODE

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

RE: Parameterized Update query in VB6

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

CODE

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. adInteger corresponds to a VB6 Long and not an Integer.

RE: Parameterized Update query in VB6

(OP)
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.

RE: Parameterized Update query in VB6

George code had a minor error on it

Quote:


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("@WHEREID", 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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close