VB6 + MySql: Update Error on Recordset

[rennis]

After researching, i have been unsuccessful in finding the correct syntax for updating a record set within vb6 to an mysql database.

the current syntax is just saveRec.update, and each field is saved to the record set as saveRec.fields("fname") (as this is the syntax for vb6 and access.

Can anyone kindly suggest how or if this syntax different for vb6 & mysql?

The reason i ask is because i get the following error on the saveRec.update line:
[MySQL][ODBC 3.51 Driver][mysqld-5.0.27-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`tablename` ( f_name,initial,l_name,gender,add1,add2,post_code,tele_home,tele' at line 1

Any help would be greatly appreciated

 

[hvass]

Basically you are using VB6 to pass a sql command to the mysql server via the ODBC driver so the command has to be a mysql command. The MySQL is complaining you are not sending a valid MySQL command.

Post the command that VB6 produces so get your VB6 code to print the command string post it on the forum and someone will be able to help.

 

[rennis]

thanks for your reply..hopefully this is what your were looking for...

VB6 & Access (original)
saveRec.Open "SELECT TOP 1 * FROM [PatientList]", frmStartUp.myConnection, 3, 3

Which i changed to for VB6 & MySql:
saveRec.Open "SELECT * FROM PatientList LIMIT 1", frmStartUp.myConnection, 3, 3

the program crashes on saveRec.update (which Im guessing i need to alter for mysql & vb6)

 

[hvass]

 "SELECT * FROM PatientList LIMIT 1"

looks ok
but looking at the error message you posted the error was in a command that ended something like

.`tablename` ( f_name,initial,l_name,gender,add1,add2,post_code,tele_home,tele"

 

[rennis]

Once the project hits saveRec.update, it crashes to error (coded into the project) displaying that information...

Im wondering, is the syntax for updating the mysql database suppose to be different from saveRec.update??

 

[hvass]

yes so post us the sql commands that saveRec.update issues

 

[rennis]

Thats just it i don't know. Im guessing the saveRec.update generates the sql command (i dont know what the update value is), but the it jumps to error..

Thanks for your assistance by the way, its greatly appreciated

 

[hvass]

If you do you not also have access to the saveRec.Update code I guess you are stuck ....

 

[rennis]

with mysql, is the correct command/syntax to update the database saverec.update?

or is it something with an insert statement for example:
sql = "insert into tablename (fname) value (saverec.field("f_name"));"??

 

[johnwm]

To insert a new record use the Insert statement. To update an existing record use the Update statement. See

http://dev.mysql.com/doc/

for more detail

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[hvass]

rennis

I know nothing about VB so dont take this as gospel but saverec.update is probably a method - so there will be some vb code behind this method that could do anything but I guess it updates a record - in mysql terms this might be something like

update tablename
set fname='my name'
where recordid=999;

but could be much more complex - it may add the record if it does not exist, update the record in several tables etc..

I am guessing whoever wrote the VB code will have compiled it into an executable so you will never know what it does - if you cannot get the code you are stuck - so you can see what a revolution open source is.

Why not start again write your own application - or look for an open source solution that you can adapt - there are not that many applications for which there are no good open source solutions.