Page 1 of 1
I cant insert to a table
Posted: Wed Jan 28, 2015 11:07 pm
by parsec
I have sqlite database with one table and 3 fields "RecNo,name,EmailList"
I try to use this code to learn but I do not get the data inserted in the table
what I am doing wrong?
thank you for the help
Code: Select all
on mouseUp
global gConID
local tColumnItems
put "name," into tColumnItems
local tRowData
repeat for each item I in tColumnItems
ask question "New value for column: " & I
if it is empty or the result is "cancel" then exit to top
put "'" & cleanSQL(it) & "'" & "," after tRowData
end repeat
ask question "New value for column: emailList" with "true"
if it is empty or the result is "cancel" then exit to top
put "'" & cleanSQL(it) & "'" after tRowData
put "INSERT INTO vendors(RecNo,name,EmailList)" & \
" VALUES(null," & tRowData & ")" into tSQL
local tResult
revExecuteSQL gConID, tSQL
put the result into tResult
handleRevDBerror tResult
if the result is not empty then
answer warning the result
exit mouseUp
end if
answer information "Number of rows added: " & return & tResult
end mouseUp
function cleanSQL pSQL
replace "'" with "''" in pSQL
return pSQL
end cleanSql
Re: I cant insert to a table
Posted: Wed Jan 28, 2015 11:58 pm
by SparkOut
I can't really study this very much nor test at all on this phone, but are you trying to insert null into an auto-increment field? Does the RecNo column really need a value to be inserted?
What does tSql contain if you answer or put it, before executing?
Are you getting any errors? What does the result contain after execution?
Re: I cant insert to a table
Posted: Thu Jan 29, 2015 12:16 am
by parsec
I have borrow the code from here SQLite-Sampler in this example the update function works. I have modified the code so I can learn but after 2 days I cant find what I did wrong ?
Thank you for any help
I am sorry I can post the link
also here is the code from that example
Code: Select all
on mouseUp
global gConID
local tColumnItems
put "name,email" into tColumnItems
local tRowData
repeat for each item I in tColumnItems
ask question "New value for column: " & I
if it is empty or the result is "cancel" then exit to top
put "'" & cleanSQL(it) & "'" & "," after tRowData
end repeat
ask question "New value for column: emailList" with "true"
if it is empty or the result is "cancel" then exit to top
put "'" & cleanSQL(it) & "'" after tRowData
local tSQL
put "INSERT INTO users(userID,name,email,emailList)" & \
" VALUES(null," & tRowData & ")" into tSQL
local tResult
revExecuteSQL gConID, tSQL
put the result into tResult
handleRevDBerror tResult
if the result is not empty then
answer warning the result
exit mouseUp
end if
answer information "Number of rows added: " & return & tResult
end mouseUp
function cleanSQL pSQL
replace "'" with "''" in pSQL
return pSQL
end cleanSQL
Re: I cant insert to a table
Posted: Thu Jan 29, 2015 1:09 am
by Simon
Hi parsec, SparkOut,
Here is what comes just before the revExecuteSQL in parsec's code
Code: Select all
INSERT INTO vendors(RecNo,name,EmailList) VALUES(null,'john','true')
Here is one from the sqlite lesson
Code: Select all
put "INSERT into contact_details VALUES ('Joe Bloggs','joe.bloggs@email.com');" into tSQL
That says you can drop your field names.
And then from the mySql lesson
Code: Select all
put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3)" into tSQL
revExecuteSQL gConnectionID, tSQL, "tFirstName", "tLastName", "tBirthDate"
Which is most like parsec's code... kinda, where the Values are place after the revExecuteSQL
Take you pick
Simon
Re: I cant insert to a table
Posted: Thu Jan 29, 2015 12:43 pm
by parsec
Hello Simon
Thank you for looking at this but I am a bit confused as the examples that you post are from another lesson I have used this as my lesson livecodeshare.runrev.com/stack/682/SQLite-Sampler
still cant figure what I am doing wrong
Thank you for the help
John
Re: I cant insert to a table
Posted: Thu Jan 29, 2015 7:24 pm
by Simon
Hi John,
The error does not seem to be in the INSERT.
Is you first field "userID integer primary key"?
Simon
Re: I cant insert to a table
Posted: Sat Jan 31, 2015 2:48 am
by parsec
I have that field as the primary key that is not an issue
this the error that I get
"button "Enter Data": execution error at line 23 (Handler: can't find handler) near "handleRevDBerror", char 1"
thank you for the help
I might start the lesson from the beginning
Re: I cant insert to a table
Posted: Sat Jan 31, 2015 3:17 am
by Simon
Should be in your stack script
Code: Select all
on handleRevDBerror p
switch
case item 1 of p is "revdberr"
return "revDB Error: " & p
break
case "syntax error" is in p
return "Database Error: " & p
break
end switch
end handleRevDBerror
Simon