SOLVED - Please check this and tell me how to make it work

[admin12]

I created a database report tool for my client that works great, but now I need to WRITE a new record and I need a bit of help.

Here is the code for the save button (after they fill out a small form - the database info and login info has been changed to protect my client):

=================================================================

on mouseUp
--Create a global variable for the db
global dbID

-- put the Ip address of the MySQL Server
--put the name of your DB
-- then your login
--then your password

--this connects to the database
put revOpenDatabase("MySQL","www.url.com","dbname_dbname","username","password") into dbID

if dbID is not a number then
answer warning "Error "&dbID
exit to top
else
answer "connection successful"
end if

--now we save the data

set itemdelimiter to tab
local newselection
local theQuery
local theData

-- by defaut, revDataFromQuery will return tab between columns and return for record
--you can change this for instance : put revDataFromQuery("*","|",dbID,theQuery) into theData
-- this can be usefull when in text column in your MYSQL DB there are return characters (or tab), so you don't want to mix up everything
-- put revDataFromQuery("*","|",dbID,theQuery) into theData
-- replace return with " " in theData
--replace tab with " " in theData
-- replace "*" with tab in theData
-- replace "|" with return in theData

put "0" into UserActive
put "0" into UserStatus
put "3" into GroupID
put "Insert Into UserName,Password,UserEmail,UserActive,UserStatus,GroupID from UserValidation" &quote & fldUserName,fldPassword1,fldEmailAddr,UserActive,UserStatus,GroupID &quote into IdSelect
put IdSelect into IdQuery
put revDataFromQuery(,,dbID,IdQuery) into IdData

If IdData contains "revdberr" then
answer warning "There is an error "&IdData
exit to top
end if

--now we go to the next card

go to card RegPage2
end mouseUp

on mouseenter
set the text of field "lblHints" to "CLICK THIS BUTTON to save your work thus far and continue to step 2 of the registration"

end mouseenter

on mouseleave
set the text of field "lblHints" to empty
end mouseleave

=================================================================

The following is where I need the help:

put "0" into UserActive
put "0" into UserStatus
put "3" into GroupID
put "Insert Into UserName,Password,UserEmail,UserActive,UserStatus,GroupID from UserValidation" &quote & fldUserName,fldPassword1,fldEmailAddr,UserActive,UserStatus,GroupID &quote into IdSelect
put IdSelect into IdQuery
put revDataFromQuery(,,dbID,IdQuery) into IdData

What is the proper syntax to make the Insert Into work? Are the assignments to the variables correct (UserActive, UserStatus, etc)?

Mike

[dglass]

INSERT INTO UserValidation (UserName,Password,UserEmail,UserActive,UserStatus,GroupID) VALUES (<<whatever the values are>>)

Assuming 'UserValidation' is the name of your table.

[admin12]

Dglass,

For the field values (the first few variables - UserName, Password1, etc), how do I represent those? Using the &quote? If so, how would I write that?

I tried VALUES (&quote &fldUserName &quote,&quote &fldPassword &quote,&quote &fldEmailAddr) plus the other variables and it did not work - big ole error.

I also tried VALUES (&quote &field fldUserName &quote,&quote &field fldPassword &quote,&quote &field fldEmailAddr) plus the other variables and it did not work - bigt ole error.

The last three variables are not fields, they are variables that I initialized just above the INSERT statement.

I just need to know how to write this one and the rest should be fairly straight forward after that.

Thanks for the help. This is pretty key to my entire project and I am not proficient enough in Live Code to not ask for help.

Mike

[admin12]

OK, I coded the following to a button called SAVE & EXIT:

=============================


on mouseUp
--Create a global variable for the db
global dbID

-- put the Ip address of the MySQL Server
--put the name of your DB
-- then your login
--then your password

--this connects to the database
put revOpenDatabase("MySQL","www.website.com","client_database","username","password") into dbID

if dbID is not a number then
answer warning "Error "&dbID
exit to top
else
answer "connection successful"
end if

--now we save the data

set itemdelimiter to tab
local newselection
local theQuery
local theData

-- by defaut, revDataFromQuery will return tab between columns and return for record
--you can change this for instance : put revDataFromQuery("*","|",dbID,theQuery) into theData
-- this can be usefull when in text column in your MYSQL DB there are return characters (or tab), so you don't want to mix up everything
-- put revDataFromQuery("*","|",dbID,theQuery) into theData
-- replace return with " " in theData
--replace tab with " " in theData
-- replace "*" with tab in theData
-- replace "|" with return in theData

--create some necessary variables
put 0 into UserActive
put 0 into UserStatus
put 3 into GroupID
put field "fldUserName" into UserName
put field "fldPassword2" into UserPassword
put field "fldEmailAddr" into UserEmail
--answer UserName,UserPassword,UserEmail,UserActive,UserStatus,GroupID

--create a insert mysql statement to create a new UserValidation record
put "INSERT INTO UserValidation (UserName,Password,UserEmail,UserActive,UserStatus,GroupID) VALUES (" &numtochar(39) &UserName &numtochar(39) &comma &numtochar(39) &UserPasssword &numtochar(39) &comma &numtochar(39) &UserEmail &numtochar(39) &comma &UserActive &comma &UserStatus &comma &GroupID &");" into IdSelect
put IdSelect into IdQuery
answer IdQuery
put revDataFromQuery(,,dbID,IdQuery) into IdData

If IdData contains "revdberr" then
answer warning "There is an error "&IdData
exit to top
end if

--now we go to the next card

go to card RegPage2
end mouseUp

on mouseenter
set the text of field "lblHints" to "CLICK THIS BUTTON to save your work thus far and continue to step 2 of the registration"

end mouseenter

on mouseleave
set the text of field "lblHints" to empty
end mouseleave

======================================

It gives me a valid query for the answer, but I still get a revdberr anyway. Can anyone help me find the culprit? Is revdberr a general pre-assigned variable that returns a number or whatever?

This 'should' work, but it doesn't.

One thought - the UserID is the primary key of table UserValidation and auto-increments. Do I need to assign a first record for it to work (currently no data) or will the first auto-assigned UserID = 0 automatically? Do I need to include that in the INSERT INTO command?

Mike

[dglass]

a) If the key is autoincrement you can leave it out of the INSERT statement.

b) You are not running a query so you don't use revDataFromQuery. You are executing an SQL statement so you need to use revExecuteSQL.

If you are retrieving data from a DB you use revDataFromQuery, or one of its 'sisters'. If you are changing data (INSERT, UPDATE, DELETE) in the DB you use revExecuteSQL.

[admin12]

a) If the key is autoincrement you can leave it out of the INSERT statement.

b) You are not running a query so you don't use revDataFromQuery. You are executing an SQL statement so you need to use revExecuteSQL.

If you are retrieving data from a DB you use revDataFromQuery, or one of its 'sisters'. If you are changing data (INSERT, UPDATE, DELETE) in the DB you use revExecuteSQL.

Almost there!!!!!

Here is the current code:

=========================================

on mouseUp
global dbID

put "SELECT UserID FROM UserValidation ORDER BY UserId DESC LIMIT 1" into theQuery
put revDataFromQuery(,,dbID,theQuery) into theData
if theData contains "revdberr" then
answer warning "There is an error "&theData
exit to top
end if
put theData into PrevUserID
add 1 to PrevUserID
put PrevUserID into NewUserID

--save the data

-- by defaut, revDataFromQuery will return tab between columns and return for record
--you can change this for instance : put revDataFromQuery("*","|",dbID,theQuery) into theData
-- this can be usefull when in text column in your MYSQL DB there are return characters (or tab), so you don't want to mix up everything
-- put revDataFromQuery("*","|",dbID,theQuery) into theData
-- replace return with " " in theData
--replace tab with " " in theData
-- replace "*" with tab in theData
-- replace "|" with return in theData

--create some necessary variables
put NewUserID into UserID
put 0 into UserActive
put 0 into UserStatus
put 3 into GroupID
put field "fldUserName" into UserName
put field "fldPassword2" into UserPassword
put field "fldEmailAddr" into UserEmail

--create a insert mysql statement to create a new UserValidation record
--revExecuteSQL myID, "insert into mytable values(:1,:2,:1)", "valueX","valueY"
put "INSERT INTO UserValidation (UserName, Password, UserEmail, UserActive, UserStatus, GroupID) VALUES ("&numtochar(39) & the text of field "fldUserName" &numtochar(39) &comma &numtochar(39) & the text of field "fldPassword2" &numtochar(39) &comma &numtochar(39) & the text of field "fldEmailAddr" &numtochar(39) &comma &UserActive &comma &UserStatus &comma &GroupID &");" into theQuery

answer theQuery

put revExecuteSQL(,,dbID,theQuery) into theData

answer theData

If theData contains "revdberr" then
answer warning "There is an error "&theData
exit to top
end if

answer "Information saved"

--now we go to the next card

go to card RegPage2
end mouseUp

on mouseenter
set the text of field "lblHints" to "CLICK THIS BUTTON to save your work thus far and continue to step 2 of the registration"

end mouseenter

on mouseleave
set the text of field "lblHints" to empty
end mouseleave

============================================

This line gives me an error:

put revExecuteSQL(,,dbID,theQuery) into theData

How is that supposed to be formatted?

Is this line correct as far as syntax:

put "INSERT INTO UserValidation (UserName, Password, UserEmail, UserActive, UserStatus, GroupID) VALUES ("&numtochar(39) & the text of field "fldUserName" &numtochar(39) &comma &numtochar(39) & the text of field "fldPassword2" &numtochar(39) &comma &numtochar(39) & the text of field "fldEmailAddr" &numtochar(39) &comma &UserActive &comma &UserStatus &comma &GroupID &");" into theQuery

The resulting answer is a correct sql query.

Thanks Dglass.

Mike

[dglass]

revExecuteSQL databaseID,SQLStatement [,{variablesList | arrayName}]

You are providing a 'fully qualified' SQL statement so all you need is

put revExeuteSQL myID, theQuery into tResult

Check the Dictionary for more info on the variableList | arrayName format.

[admin12]

revExecuteSQL databaseID,SQLStatement [,{variablesList | arrayName}]

You are providing a 'fully qualified' SQL statement so all you need is

put revExeuteSQL myID, theQuery into tResult

Check the Dictionary for more info on the variableList | arrayName format.

So damn close.

I copy and pasted the line above and I get the error:

button "btnStep2": compilation error at line 56 (Commands: missing ',') near "into", char 28

Here is the code snippet:

=======================

--create a insert mysql statement to create a new UserValidation record
put "INSERT INTO UserValidation (UserID,UserName, Password, UserEmail, UserActive, UserStatus, GroupID) VALUES ("&UserID &comma &numtochar(39) & the text of field "fldUserName" &numtochar(39) &comma &numtochar(39) & the text of field "fldPassword2" &numtochar(39) &comma &numtochar(39) & the text of field "fldEmailAddr" &numtochar(39) &comma &UserActive &comma &UserStatus &comma &GroupID &");" into theQuery

answer theQuery

put revExecuteSQL myID, theQuery into tResult

answer tResult

If tResult contains "revdberr" then
answer warning "There is an error "&tResult
exit to top
end if

answer "Information saved"

--now we go to the next card

go to card RegPage2

========================================

What am I doing wrong? I tried adding a comma as suggested in the error and I still get errors. Funny enough, turns out a LOT of my attempts did somehow work and were written to the database, but now nothing and the error.

Thanks for your help dglass.

Mike

[dglass]

It probably isn't necessary to write your SQL with all those 'numtochar(39)' and 'comma' commands.

Just use quotes and commas....

INSERT INTO UserValidation (UserID,UserName, Password, UserEmail, UserActive, UserStatus, GroupID) VALUES (" & UserID & "," & the text of field "fldUserName" & "," & the text of field "fldPassword2" & "," & the text of field "fldEmailAddr" & "," & UserActive & "," & UserStatus & "," & GroupID & ");"

At least for me that's easier to read. You also should be doing some SQLifying of your variables; escaping them so they don't mess up your SQL.

My guess is that one of your fields has either an apostrophe or a comma in it, and it is changing the SQL.

Generally speaking, text values need to be surrounded by single quotes/apostrophes so any apostrophes in the values need to be escaped otherwise the apostrophe within the value will be seen as the closing quote of the value, with the stuff following the apostrophe then messing up the parsing.

You should really check over your SQL to be sure it is what you think it should be.

Also, this sort of thing is what the 'variableNames | arrayValues' format of the SQLExecute command is designed to handle.

[admin12]

I just cannot find the problem.

Here is the most current snippet:

--create some necessary variables
put NewUserID into UserID
put 0 into UserActive
put 0 into UserStatus
put 3 into GroupID
put field "fldUserName" into UserName
put field "fldPassword2" into UserPassword
put field "fldEmailAddr" into UserEmail

--create a insert mysql statement to create a new UserValidation record
--put "INSERT INTO UserValidation (UserID,UserName, Password, UserEmail, UserActive, UserStatus, GroupID) VALUES ("&UserID &comma &numtochar(39) & the text of field "fldUserName" &numtochar(39) &comma &numtochar(39) & the text of field "fldPassword2" &numtochar(39) &comma &numtochar(39) & the text of field "fldEmailAddr" &numtochar(39) &comma &UserActive &comma &UserStatus &comma &GroupID &");" into theQuery

put "INSERT INTO UserValidation (UserID,UserName, Password, UserEmail, UserActive, UserStatus, GroupID) VALUES (" & UserID & "," & the text of field "fldUserName" & "," & the text of field "fldPassword2" & "," & the text of field "fldEmailAddr" & "," & UserActive & "," & UserStatus & "," & GroupID & ");" into theQuery

answer theQuery

put revExecuteSQL (,,dbID,theQuery) into theData

answer theData

If theData contains "revdberr" then
answer warning "There is an error "&theData
exit to top
end if

answer "Information saved"

--now we go to the next card

go to card RegPage2


All of this happens within a mouseup (button press).

I wish we could find this and squash it. It's driving me nuts!

Also, I read the dictionary under 'variableNames | arrayValues' format of the SQLExecute command and I just don't get it.

What part of my snippet has to do with the variable names or array values. I already created the working sql query (which I tested in phpMyAdmin and it worked 100%), so what else do I need?

I tried writing the revExecuteSQL the way you did (you left out the 'c', btw), in fact, I copied and pasted it. I still received the error.

dbID is the variable for the database ID collected during the connection script. Does it have to be myID or is dbID fine?

Mike

[admin12]

The following code snippet makes LiveCode happy, but does not result in a new entry into the database:

===========================

put "INSERT INTO UserValidation (UserID,UserName, Password, UserEmail, UserActive, UserStatus, GroupID) VALUES (" & UserID & "," & the text of field "fldUserName" & "," & the text of field "fldPassword2" & "," & the text of field "fldEmailAddr" & "," & UserActive & "," & UserStatus & "," & GroupID & ");" into theQuery

answer theQuery

put revExecuteSQL, dbID, theQuery into theResult

answer theResult

If theResult contains "revdberr" then
answer warning "There is an error "&theResult
exit to top
end if

answer "Information saved"

--now we go to the next card

go to card RegPage2

========================================

I need to make this work - dglass or anyone, please help me find the error or formulate the correct useage so I can move on. I read the dictionary entry and docs up and down - no luck.

Mke

[admin12]

This makes it happy, but gives no result - as in, no record inserted. Please help me find this - I am under pressure to get this going.

This is right out of the dictionary. Still does not work.

=====================================

put NewUserID into UserID
put 0 into UserActive
put 0 into UserStatus
put 3 into GroupID

--create a insert mysql statement to create a new UserValidation record

put "INSERT INTO UserValidation (UserID,UserName, Password, UserEmail, UserActive, UserStatus, GroupID) VALUES ("&UserID & "," & the text of field "fldUserName" & "," & the text of field "fldPassword2" & "," & the text of field "fldEmailAddr" & "," & UserActive & "," & UserStatus & "," & GroupID & ");" into theQuery

--answer theQuery

revExecuteSQL dbID, theQuery, "*bMyVar"

-- answer theResult

If theResult contains "revdberr" then
answer warning "There is an error "&theResult
exit to top
end if

answer "Information saved"

--now we go to the next card

go to card RegPage2

=============================================================

Mike

[admin12]

I figured it out. YAY!

Here is the properly working code:

=========================================

--create some necessary variables
put NewUserID into UserID
put 0 into UserActive
put 0 into UserStatus
put 3 into GroupID

if UserPass1 <> UserPass2 then
answer "Passwords do not match - try again"
Go to top
else
end if

--create a insert mysql statement to create a new UserValidation record

put "INSERT INTO UserValidation (UserID,UserName, Password, UserEmail, UserActive, UserStatus, GroupID) VALUES ("&UserID & "," &numtochar(39) & the text of field "fldUserName" &numtochar(39) & "," &numtochar(39) & the text of field "fldPassword2" &numtochar(39) & "," &numtochar(39) & the text of field "fldEmailAddr" &numtochar(39) & "," & UserActive & "," & UserStatus & "," & GroupID & ")" into theQuery

answer theQuery

revExecuteSQL dbID, (theQuery)

-- answer theResult

-- If theResult contains "revdberr" then
-- answer warning "There is an error "&theResult
-- exit to top
-- end if

answer "Information saved"

--now we go to the next card

go to card RegPage2

==============================================