Sending Data from Datagrid to mySQL Database

[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.

[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
...

[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

[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.

[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.

[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?

[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.

[bsouthuk]

Thanks for your help Trevor - have finally sorted it!

[trevordevore]

You're welcome.