Adding more data to MSSQL database using VBScript

Question

I have the code:

Dim conn, SQL, rs
    Const DB_CONNECT_STRING = "Provider = SQLOLEDB.1; Data Source = DJ-PC; Initial Catalog = Baza_NC; user id = 'user_baza_nc'; password = 'Password1'"
    Set myConn = CreateObject ( "ADODB.Connection")
    Set MyCommand = CreateObject ( "ADODB.Command")
    myConn.Open DB_CONNECT_STRING
    Set myCommand.ActiveConnection = myConn

    myCommand.CommandText = "UPDATE Klienci_NC SET Klienci_NC.Klient = '" & & Klient_niceform' 'WHERE Klienci_NC.ID =' "& ID_zmienna &" ' "

    myCommand.Execute
    myConn.Close

I would like to write to MSSQL database further data relating to the address in the column "Klienci_NC.adres" using & Adres_niceform & the VBScript looks like this:

myCommand.CommandText = "UPDATE Klienci_NC SET Klienci_NC.adres = '" & & Adres_niceform' 'WHERE Klienci_NC.ID =' "& ID_zmienna &" ' "

however, using

Dim conn, SQL, rs
    Const DB_CONNECT_STRING = "Provider = SQLOLEDB.1; Data Source = DJ-PC; Initial Catalog = Baza_NC; user id = 'user_baza_nc'; password = 'Password1'"
    Set myConn = CreateObject ( "ADODB.Connection")
    Set MyCommand = CreateObject ( "ADODB.Command")
    myConn.Open DB_CONNECT_STRING
    Set myCommand.ActiveConnection = myConn

    myCommand.CommandText = "UPDATE Klienci_NC SET Klienci_NC.Klient = '" & & Klient_niceform' 'WHERE Klienci_NC.ID =' "& ID_zmienna &" ' "

    myCommand.CommandText = "UPDATE Klienci_NC SET Klienci_NC.adres = '" & & Adres_niceform' 'WHERE Klienci_NC.ID =' "& ID_zmienna &" ' "

    myCommand.Execute
    myConn.Close

It is performed only the first line:

myCommand.CommandText = "UPDATE Klienci_NC SET Klienci_NC.Klient = '" & & Klient_niceform' 'WHERE Klienci_NC.ID =' "& ID_zmienna &" ' "

How to join the script:

myCommand.CommandText = "UPDATE Klienci_NC SET Klienci_NC.adres = '" & & Adres_niceform' 'WHERE Klienci_NC.ID =' "& ID_zmienna &" ' "

the two worked properly?

You can use several items in the set clause like this: update table set col1 = val1, col2 = val2 where ... but your code is possibly also vulnerable to SQL injection attacks and you should use ADODB command parameters to inject the values instead of raw concatenation. I don't quite remember the proper syntax though (which is why this is a comment and not an answer). — jpw
– jpw, Commented May 25, 2016 at 13:51

John Cappelletti · Accepted Answer · 2016-05-25 13:54:27Z

1

Combine both statements?

myCommand.CommandText = "UPDATE Klienci_NC SET Klienci_NC.Klient = '" & Klient_niceform  & "',Klienci_NC.adres = '" & Adres_niceform &"' WHERE Klienci_NC.ID ='" & ID_zmienna &"'"

answered May 25, 2016 at 13:54

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

3 Comments

John Cappelletti Over a year ago

I agree with jpw regarding SQL Injection, however, if you really want to build the string, you may want to trap possible single quotes. For example: replace(Klient_niceform,"'","''")

Sean Lange Over a year ago

Single quote replacement is nowhere good enough to prevent sql injection. It does at least allow for actual data that includes a single quote to work though.

John Cappelletti Over a year ago

No argument from me here. Trapping the single quote was my immediate concern. If he is committed to building the string, I didn't want him chasing a ghost.

Collectives™ on Stack Overflow

Adding more data to MSSQL database using VBScript

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related