I cant insert to a table

[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

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

[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

[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

[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

[Simon]

Hi John,
The error does not seem to be in the INSERT.
Is you first field "userID integer primary key"?

Simon

[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

[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