record changes not being saved

[ideasworking]

Hello,

I'm working in vb6 connecting to a MySQL database. I can see the record data on my form, however changes to the data are not saved to the database.

What do I need to do to update the record?

Till this point I have worked in Access and the record changs are automatically saved...

TIA
Lou

 

[vbkris]

use the update statement....

sql="update table set column=value where column=condition"

Known is handfull, Unknown is worldfull

 

[ideasworking]

Is that the best way? I would have to do that for every text box, combo box, etc on the form.

I find it strange that changes I make in a datagrid are saved without any additional code from me. Why is that?

Thanks
Lou

 

[vbkris]

"update table set column=value,column1=value1,column2=value2.... where column=condition"

all can come in one sql stmt...

Known is handfull, Unknown is worldfull

 

[ideasworking]

Why isn't this working?

Dim rstSite As ADODB.Recordset
Set rstSite = New ADODB.Recordset
rstSite.CursorType = adOpenKeyset
rstSite.LockType = adLockOptimistic
rstSite.Update "SET " & _
"rptField1 = ReportFields(0).text, " & _
"rptField2 = ReportFields(1).text, " & _
"rptField3 = ReportFields(2).text, " & _
"rptField4 = ReportFields(3).text, " & _
"rptField5 = ReportFields(4).text, " & _
"rptField6 = ReportFields(5).text, " & _
"rptField7 = ReportFields(6).text, " & _
"rptField8 = ReportFields(7).text, " & _
"rptField9 = ReportFields(8).text, " & _
"rptField10 = ReportFields(9).text " & _
"Where Site = siteinfo(0), " & _
"Provider=MSDASQL; DRIVER={MySQL ODBC 3.51 Driver}; " & _
"Server=MY_SERVER; " & _
"UID=XXX; PWD=XXX; " & _
"database=MY_DB; Option=16387; , , , adCmdunknown"

 

[vbkris]

sorry i havent used the update method of a record set.

i write the entire query:
sql="update table set...."
connection.execute sql

this is how i do it. it is ver easy to debug...


however i did notice one error:
"rptField1 = ReportFields(0).text, " & _
must be:
"rptField1 = " & ReportFields(0).text & ", " & _



Known is handfull, Unknown is worldfull

 

[ideasworking]

For all who may want to use the Update method...

What I have found is the select statement must name all the fields you want to update.

For example you cannot use "SELECT * FROM table" you have to use "SELECT Field1, Field2, etc FROM table"

Once I did that all my code functioned as desired.