can anyone see an error in this rs.update code?

[thejoker]

'* Open a new connection
Set conn2 = New ADODB.Connection
Set rs2 = New ADODB.Recordset

conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\atm.mdb;" & _
"Persist Security Info=False"

'* update balance information for user
rs2.Open "UPDATE Users SET Balance = " & balanceleft & "WHERE userID = " & txtuserID.Text, _
conn2, adOpenForwardOnly, adLockReadOnly, adCmdText

rs2.Update

'* close rs connection
rs2.Close
Set rs2 = Nothing




I'm having problems getting the above code to update the database, any ideas? thanks

[RhinoBull]

First (and without going through your syntax) use connection object to execute update sql statement:

strSQL = "Update ..."
conn2.Execute strSQL

You may need to Resynq your recordset if it's already open:

RS2.Resync adAffectAllChapters, adResyncAllValues

[Kezmondo]

What's the error?

Is Balance a string or a number?

For a start, I think you've missed a space character before the WHERE statement which will make your SQL invalid.
Should be: ... balanceleft & " WHERE userID = " & ...

[thejoker]

balanceleft is a number, the error says operation not allowed while connection is closed..It now updates the database tho lol

[ashish sharma]

Try this one .it will solve ur problem

Set cn = New ADODB.Connection

if cn.state=adstateopen then cn.close
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\atm.mdb;" & _
"Persist Security Info=False"

'* update balance information for user
cn.execute "UPDATE Users SET Balance = " & balanceleft & "WHERE userID = " & txtuserID.Text


code by ashish sharma

[RhinoBull]

Try this one .it will solve ur problem

Set cn = New ADODB.Connection

if cn.state=adstateopen then cn.close
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\atm.mdb;" & _
"Persist Security Info=False"

'* update balance information for user
cn.execute "UPDATE Users SET Balance = " & balanceleft & "WHERE userID = " & txtuserID.Text


code by ashish sharma

Do you Ashish ever read replies before posting your own ???

Here is what I suggested more than an hour before you did

strSQL = "Update ..."
conn2.Execute strSQL

[randem]

thejoker,

90% of your SQL Statement problems will go away if you use code similar to what RhinoBull suggest and after doing a statement like

strSQL = "Update ..."

You do a

Debug.Print strSQL

You generally will see obvious typos, Bad SQL syntax and problems of that sort.

[thejoker]

thanks a lot for the help guys