Page 1 of 1
Sending Data from Datagrid to mySQL Database
Posted: Sat Sep 04, 2010 7:04 pm
by bsouthuk
Hi all
I have managed to obtain the following script which enables you to add a record to my Database:
Code: Select all
on mouseUp
-- check the global connection ID to make sure we have a database connection
global gConnectionID
if gConnectionID is not a number then
answer error "Please connect to the database first."
exit to top
end if
-- edit these variables to match your database & table
-- this assumes a table called Table1 with 3 fields
put "Table1" into tTableName
put "firstName, lastName, birthDate" into tFields
put "Mary" into tFirstName
put "Smith" into tLastName
put the short date into tBirthDate -- this is nonsensical but gives some variation to the data
-- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3)" into tSQL
-- send the SQL to the database, filling in the placeholders with data from variables
revExecuteSQL gConnectionID, tSQL, "tFirstName", "tLastName", "tBirthDate"
-- 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
However, I want to be able to add lots of record from a datagrid to my database. I cannot seem to find a lesson/tutorial on this one. I am sure it may be similar to the above script but cannot seem to work it out.
I am now banging my head against the walls!
Your help would be most appreciated.
Re: Sending Data from Datagrid to mySQL Database
Posted: Tue Sep 07, 2010 9:09 pm
by trevordevore
There isn't anything special that needs to be done to add data from a data grid to a database. It is just a matter of looping through the data grid data and inserting each record. Take a look at the lesson on exporting data from a data grid. It shows how to loop through a data grid array:
http://lessons.runrev.com/spaces/lesson ... Data-Grid-
As you loop through each entry in the data grid array you can use a slight variation of the INSERT code you already have. The only difference is that you need to get the data from the array rather than using hard coded values:
Code: Select all
...
put theDataA[theIndex]["first_name"] into tFirstName
put theDataA[theIndex]["last_name"] into tLastName
...
Re: Sending Data from Datagrid to mySQL Database
Posted: Wed Sep 08, 2010 12:49 am
by bsouthuk
Thanks for your response Trevor.
I'm actually pretty confused and cannot seem to work this out - I have tried the following but something here is wrong, can you spot it?
Code: Select all
-- check the global connection ID to make sure we have a database connection
global gConnectionID
if gConnectionID is not a number then
answer error "Please connect to the database first."
exit to top
end if
-- edit these variables to match your database & table
-- this assumes a table called Table1 with 3 fields
put "Customer" into tTableName
put "Company, FirstName, LastName, Switchboard" 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 theIndex in theIndexes
put theDataA[theIndex]["Company"] into tCompany
put theDataA[theIndex]["FirstName"] into tFirstName
put theDataA[theIndex]["LastName"] into tLastName
put theDataA[theIndex]["SwitchBoard"] into tSwitchboard
end repeat
-- send the SQL to the database, filling in the placeholders with data from variables
revExecuteSQL gConnectionID, theIndexes , "tCompany", "tFirstName", "tLastName", "tSwitchboard"
-- 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
Re: Sending Data from Datagrid to mySQL Database
Posted: Wed Sep 08, 2010 12:15 pm
by Klaus
Hi Daniel,
## repeat for each item theIndex in theIndexes
"theIndexes" is not defined anywhere in your script!
Best
Klaus
P.S.
Please, please, please stop using useless comment like "...something here is wrong..."
and post the errors you get after trying your script(s), thanks.

Re: Sending Data from Datagrid to mySQL Database
Posted: Wed Sep 08, 2010 3:13 pm
by trevordevore
Daniel - Klaus is right. In addition you don't put any data into theDataA either. It looks like you didn't copy and paste the code that fills in theDataA and theIndexes from the lesson.
Code: Select all
## Get Data Grid Array
put the dgData of group "DataGrid 1" into theDataA
## Get indexes in proper order
put the dgIndexes of group "DataGrid 1" into theIndexes
It is always a good idea to use the debugger to verify that your variables contain the data you think they should. Just put a breakpoint in before the repeat loop and you can instantly tell that theDataA and theIndexes are blank. Now you know where to start troubleshooting.
Re: Sending Data from Datagrid to mySQL Database
Posted: Wed Sep 08, 2010 9:38 pm
by bsouthuk
Hi guys
Its starting to make a bit more sense to me now - but I keeo getting an error message saying the Query is empty.
I now have the following code:
Code: Select all
-- check the global connection ID to make sure we have a database connection
## Connect to the database
on MouseUP
put "mysql" into theDBType
put "www.qglogin.co.uk" into theDBHost
put "qgloginc_bsouthuk" into theDBName
put "qgloginc_David" into theDBUser
put "manutd07" into theDBPassword
put revOpenDatabase( theDBType, theDBHost, theDBName, theDBUser, theDBPassword ) into theConnectionID
answer the result
## Get Data Grid Array
put the dgData of group "Text" into theDataA
## Get indexes in proper order
put the dgIndexes of group "Text" into theIndexes
-- edit these variables to match your database & table
-- this assumes a table called Table1 with 3 fields
put "Customer" into tTableName
//put "Company, FirstName, LastName, Switchboard" 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 theIndex in theIndexes
put theDataA[theIndex]["Company"] into tCompany
put theDataA[theIndex]["FirstName"] into tFirstName
put theDataA[theIndex]["LastName"] into tLastName
put theDataA[theIndex]["SwitchBoard"] into tSwitchboard
end repeat
-- send the SQL to the database, filling in the placeholders with data from variables
revExecuteSQL theConnectionID, theDataA[theIndex], "tCompany", "tFirstName", "tLastName", "tSwitchboard"
-- 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
What idiotic thing have I done or should I say not done this time?
Re: Sending Data from Datagrid to mySQL Database
Posted: Thu Sep 09, 2010 4:36 pm
by trevordevore
You never generate a query in your example. Rather you are passing an array reference (theDataA[theIndex]) in place of the query. In your original example you generated and stored the sql statement in tSQL.
Re: Sending Data from Datagrid to mySQL Database
Posted: Thu Sep 09, 2010 4:39 pm
by bsouthuk
Thanks for your help Trevor - have finally sorted it!
Re: Sending Data from Datagrid to mySQL Database
Posted: Thu Sep 09, 2010 4:50 pm
by trevordevore
You're welcome.