Updating SQL fields with vb6

21 Feb 2009 6:01 PM

DorkyGrin

Using VB6, I'm trying to pass a local variable to the SQL field but I
keeping the following error:

'invalid column name RollNoValue'.

I can see that the SQL statement is not right - it's looking for a
column instead of using my local variable. I can't figure out how to
pass the local variable named 'RollNoValue'

Any hints?

Code:

'======================================
Private Sub txtSetRollNo_Change()
RollNoValue = txtSetRollNo.Text
End Sub

Private Sub Command3_Click()
'Create Connection Object to SQL server table
Dim Fortis241DSN As String

MsgBox "roll number value is " & RollNoValue

' Error Handling Variables
    Dim Errs1 As Errors
       Dim errLoop As Error
       Dim strTmp As String

       On Error GoTo AdoError ' Full Error Handling which traverses
                               ' Connection object

    Dim con As ADODB.Connection
    Set con = New ADODB.Connection

       ' Don't assume that we have a connection object.
       On Error GoTo AdoErrorLite

con.Open "DSN=Fortis241DSN;" & "Uid=sysadm;" & "Pwd=westtech#1"

Dim stSQL As String

'Here is where the rubber meets the road
stSQL = "UPDATE [Microfilm] SET [RollNumber] = RollNoValue WHERE
RollNumber = 'xyz'"

con.Execute stSQL
end sub

21 Feb 2009 6:41 PM

Brad Kunkel

You have to build the SQL command string from the values stored in the
variables you want to use. Try this:

stSQL = "UPDATE [Microfilm] SET [RollNumber] = '" & RollNoValue & "' WHERE
RollNumber = 'xyz'"

HTH,
Brad Kunkel
Integrated Business, Inc.

Show quoteHide quote

"DorkyGrin" <jchicker***@gmail.com> wrote in message
news:0f201fe5-fc79-42b6-9c94-8aaf76e765d5@p20g2000yqi.googlegroups.com...
> Using VB6, I'm trying to pass a local variable to the SQL field but I
> keeping the following error:
>
> 'invalid column name RollNoValue'.
>
> I can see that the SQL statement is not right - it's looking for a
> column instead of using my local variable. I can't figure out how to
> pass the local variable named 'RollNoValue'
>
> Any hints?
>
> Code:
>
> '======================================
> Private Sub txtSetRollNo_Change()
> RollNoValue = txtSetRollNo.Text
> End Sub
>
> Private Sub Command3_Click()
> 'Create Connection Object to SQL server table
> Dim Fortis241DSN As String
>
>
> MsgBox "roll number value is " & RollNoValue
>
> ' Error Handling Variables
>    Dim Errs1 As Errors
>       Dim errLoop As Error
>       Dim strTmp As String
>
>
>
>       On Error GoTo AdoError ' Full Error Handling which traverses
>                               ' Connection object
>
>    Dim con As ADODB.Connection
>    Set con = New ADODB.Connection
>
>       ' Don't assume that we have a connection object.
>       On Error GoTo AdoErrorLite
>
>
> con.Open "DSN=Fortis241DSN;" & "Uid=sysadm;" & "Pwd=westtech#1"
>
> Dim stSQL As String
>
> 'Here is where the rubber meets the road
> stSQL = "UPDATE [Microfilm] SET [RollNumber] = RollNoValue WHERE
> RollNumber = 'xyz'"
>
> con.Execute stSQL
> end sub

21 Feb 2009 7:33 PM

DorkyGrin

That worked! Thanks !!!

23 Feb 2009 12:09 AM

Bill McCarthy

Hi Dorky,

Although that may have solved your immediate problem it can in fact set you
up for much larger problems that can compromise your data integrity when
your app is run. You should avoid direct replacement of user provided
variables into SQL strings as this leaves you open to SQL injection attacks.
Instead you should use parameters.

Show quoteHide quote

"DorkyGrin" <jchicker***@gmail.com> wrote in message
news:b80fc190-2ff8-484d-bcbd-6a91dc58af0a@j38g2000yqa.googlegroups.com...
>
> That worked! Thanks !!!