Insert DataGrid Data to MYSQL Database

[tanjc]

Hi,
I hope some expert could help me with the problem below. Thanks a lot if you could help.

I am new to Livecode DataGrid and I am exploring how to insert data in the DataGrid to MYSQl database.
I encountered the following error message:

You have an error in your SQL syntax check the manual that correspond to your MYSQL server version
for the right syntax to use near 'desc) values ('2') at line 1

Following is my script:

Global gConnectionID

on MouseUP
databaseConnect

## Get Data Grid Array
put the dgData of group "dg1" into theDataA

## Get indexes in proper order
put the dgIndexes of group "dg1" into theIndexes


-- edit these variables to match your database & table
put "test" into tTableName
put "ln, desc" into tFields

-- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
repeat for each item theIndexes in theIndexes
put theDataA[theIndexes]["ln"] into tln
put theDataA[theIndexes]["desc"] into tdesc
end repeat

put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2)" into tSQL
revExecuteSQL gConnectionID, tSQL, "tln", "tdesc"

-- check the result and display the data or an error message
if the result is a number then
answer info "New record added."
else
answer error "There was a problem adding the record to the database:" & cr & the result
end if
end mouseUp

[bangkok]

When confronted with a MySQL error... always look at the query that is sent by LiveCode to the MySQL server. Any error will "jump" at your eyes, making debug work easier and faster.

Therefore :

Code: Select all

answer error "There was a problem adding the record to the database:" & cr & tSQL
put tSQL

Copy and paste the query here.

[tanjc]

Hi Bangkok,

After amending to "answer error "There was a problem adding the record to the database:" & cr & tSQL

I encountered this error message:
There was a problem adding the record to the database:

Insert into test (ln, desc) values (:1, :2)

I am not sure what caused the probelm.

Please help. Thanks.

[tanjc]

Hi Bangkok,

I realised that I have a problem with the repeat loop. I changes the script and still encounter the same problem.
I don't know where i go wrong, here's the new script:

Global gConnectionID

on mouseUp
databaseConnect

put the dgData of group "DataGrid1" into tData
put the dgIndexes of group "DataGrid1" into tIndexes

put "test" into tTableName
put "ln, desc" into tFields

repeat with x = 1 to the number of items of tIndexes
put item x of tIndexes into tNumber
put line tNumber of tData into tContent
put "INSERT into " & tTableName & " (" & tFields & ") VALUES ('" &tContent& "')" into tSQL
revExecuteSQL gConnectionID, tSQL
end repeat

-- check the result and display the data or an error message
if the result is a number then
answer info "New record added."
else
answer error "There was a problem adding the record to the database:" & cr & tSQL
end if

end mouseUp

[AxWald]

Hi,

is it possible that you have a type mismatch problem? String/ varchar types with '' , numbers without!

I'd fire up HeidiSQL or phpMyAdmin, create an SQL string that works, and use this as a "blueprint" for the LC code - such can save much trouble ;-)

Have fun!

[phaworth]

If I'm not mistaken, dgData is an array so it doesn't have lines, it has keys corresponding to line numbers with subkeys corresponding to column names.

Also, you need commas between the values in tContents, and you need to enclose each value with single quotes unless they are numbers

Pete