I need to do a SQL UPDATE query via VB code? 2

[kskinne]

I have created the following code in a VB project:


Dim myConnection As New ADODB.Connection
Dim myRecordset As New ADODB.Recordset
Set myConnection = New ADODB.Connection

myConnection.Open "Provider=SQLOLEDB.1;Password=abkosam;Persist Security Info=True;User ID=sa;Initial Catalog=carroll;Data Source=CARROLLRETAIL"

Set myRecordset = myConnection.Execute("SELECT * FROM Cashier WHERE Number = " & posSession.Cashier.Number)

MsgBox "my password is" & myRecordset(6)
MsgBox "my privileges are" & myRecordset(11)

myRecordset.Close
myConnection.Close
Set myConnection = Nothing

I need this modified so that instead of doing a select and returning data from the database, it changes data in a particular record via an UPDATE query. How do I go about doing this? What is the correct syntax?

Thank you,
Kevin

 

[jebenson]

First, this line is redundant:

Set myConnection = New ADODB.Connection

because you already created a new Connection object in this line:

Dim myConnection As New ADODB.Connection


As to the UPDATE, the SQL syntax is:

UPDATE <TableName> SET <FieldName>=<NewValue> WHERE <SomeField>=<SomeValue>

In your case, to change the password:

Dim SQLStr = "UPDATE Cashier SET password='" & NewPassword & "' WHERE Number = " & posSession.Cashier.Number

myConnection.Execute(SQLStr)

Some clarifications:

1) The correct use of a WHERE clause is vitally important in an UPDATE, because not using one or using it incorrectly can update more than one record - maybe even all the records - or no records. A good practice is to test your WHERE clause in a SELECT statement first, to make sure it is only affecting the records you want to change without actually making any changes.

2) String values must be enclosed in single quotes; numeric values do not have any quotes.

3) Note that in executing the UPDATE SQL you do not assign the result to a recordset. This is because an UPDATE statement does not return any records.




I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[kskinne]

ok i made those changes and this is what i have, but after running the code, i go and check the data for that record in the database, and it is not changed. what am i doing incorrectly....thanks,
kevin



Dim myConnection As New ADODB.Connection
Dim myRecordset As New ADODB.Recordset

myConnection.Open "Provider=SQLOLEDB.1;Password=sql;Persist Security Info=True;User ID=sa;Initial Catalog=carroll;Data Source=CARROLLRETAIL"

SQLStr = "UPDATE Cashier SET Privileges = '4597654' WHERE Privileges = '4596118' AND Number = " & posSession.Cashier.Number

myConnection.Execute (SQLStr)
myRecordset.Update

myRecordset.Close
myConnection.Close

Set myConnection = Nothing

 

[fredericofonseca]

kskinne,

When you use a "UPDATE ..." you don't need to use a recordset.

Try the following

Sub test()
Dim SQLStr As String
    Dim myConnection As adodb.connection
    Set myConnection = New adodb.connection
    myConnection.CursorLocation = adUseClient
    myConnection.Mode = adModeReadWrite
    myConnection.Open "Provider=SQLOLEDB.1;Password=sql;Persist Security Info=True;User ID=sa;Initial Catalog=carroll;Data Source=CARROLLRETAIL"

    SQLStr = "UPDATE Cashier SET Privileges = '4597654' WHERE Privileges = '4596118' AND Number = 123"
'where 123 is a NUMBER that exists on the table cashier. Be sure before hand that the record does exist on the table.

    myConnection.Execute (SQLStr)
    myConnection.Close

    Set myConnection = Nothing

End Sub

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[kskinne]

That doesn't work either. The code is in a class module in an activeX dll project, the code in the dll is triggered within a POS program we use (Microsoft RMS). I am beginning to think that maybe there are some securities set up on the DB to prevent certain cashiers from performing this? I will test with my cashier login and post back with the results - my login has access to everything.

kevin

 

[fredericofonseca]

Ahh. You should always try the code with someone that as access to do all updates. (and do it on a temp db/table!!).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[kskinne]

Thanks for the tip, my problem was permissions-related for that particular login. In the meantime, however, I have been able to come up with an easier solution, that didn't require the SQL query, by making some additional changes to access rights in the program via the front-end. Thank you for your help

Regards,
Kevin