Database Update Problem

[shire]

Hello,

I'm using ODBC Connection and I'm having a problem updating record in the database, here's the code:

Code: Select all

Table$ = "name"
cn$ = GetGadgetText(173)
father_fname$ = (GetGadgetText(174)
result = DatabaseUpdate(0, "UPDATE "+Table$+" SET fname='"+father_fname$+"' WHERE MT1_F17 = '"+cn$+"';") 

It also has the result = 1, but it does not update the record. What could have been wrong?
Any idea?
Your answers will be very much appreciated.
Thank you:)

[jamba]

what's your table structure?

how are you connecting to db?

does databaseError() return anything?

[shire]

Hello jamba,

My table structure looks like this:
+-------------+-----------------+--------------+
| table_name | column_name | data_type |
+-------------+-----------------+--------------+
| name | F1 | varchar(10) |
| name | F2 | varchar(30) |

and I use this code to connect to the database:

Code: Select all

UseODBCDatabase()
   DriverODBC.s = "MySQL" 
   root$ ="root"
   pwd$ = "password"
OpenDatabase(0, DriverODBC, root$, pwd$)

I only have this update problem when using variable which is assigned with the value of a text coming from a gadget like the code I posted.
Inserting data works fine, and databaseError() returns nothing.

[Fangbeast]

Code: Select all

Table$ = "name"
cn$ = GetGadgetText(173)
spouse_fname$ = (GetGadgetText(174)
result = DatabaseUpdate(0, "UPDATE "+Table$+" SET fname='"+father_fname$+"' WHERE MT1_F17 = '"+cn$+"';")

Not sure if your typos are accidental or not but...

1. spouse_fname$ = (GetGadgetText(174) is an error and pb should have warned you that you have a bracket in front of GetGadgetText.

2. You get a value into spouse_fname$ in your above example but in your database statement you are passing father_fname$,

[shire]

@fangbeast:
oh, sorry! typo error..
that should be,

Code: Select all

Table$ = "name"
cn$ = GetGadgetText(173)
father_fname$ = GetGadgetText(174)
result = DatabaseUpdate(0, "UPDATE "+Table$+" SET fname='"+father_fname$+"' WHERE MT1_F17 = '"+cn$+"';")

btw, thank you for the reply:)

[citystate]

Code: Select all

+-------------+-----------------+--------------+
| table_name  | column_name     | data_type    |
+-------------+-----------------+--------------+
| name        | F1              | varchar(10)  |
| name        | F2              | varchar(30)  |

according to the structure you gave, the only fields are F1 and F2 - where do fname and MT1_F17 come into it?

[shire]

Hello enthusiast,

Sorry for that,
it was suppose to be:
fname = F1
MT1_F17 = F2

Here's the exact structure

Code: Select all

+-------------+-----------------+--------------+
| table_name  | column_name | data_type   |
+-------------+-----------------+--------------+
| name        | MT1_F17          | varchar(10) |
| name        | fname             | varchar(30) |

[citystate]

perhaps add this line

Code: Select all

result = DatabaseUpdate(0, "COMMIT;")

[shire]

Thank you citystate:) I'll try it..

[shire]

Thank you for all the reply:) it's finally working..