No error message but cant insert into database

[binary]

Please what could be wrong with this script? is not inserting fields from my form into the table even when everything seems alright.

on mouseUp
-- use a global variable to hold the connection ID so other scripts can use it
global gConnectionID

-- set up the connection parameters - edit these to suit your database
put "localhost" into tDatabaseAddress
put "elibrary" into tDatabaseName
put "root" into tDatabaseUser
put "" into tDatabasePassword

-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
--connect to table
put "user_registration_form" into tTableName

put "number" into tuser_number
put "name" into tname
put "cmbsex" into tsex
put "department" into tdepartment
put "school" into tschool
put "cmblevel" into tlevel
put "address" into taddress
put "mobile" into tuser_mobile
put "email" into temail
put "dateregistered" into tcreate_date
put "cmbuserstatus" into tuser_status
put "holder" into tuser_image


-- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
put "INSERT INTO" && tTableName && "( user_number, name, sex, department, school, level, user_mobile, email, create_date, user_status, user_image )" && "VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,) " into tSQL

-- send the SQL to the database, filling in the placeholders with data from variables
revExecuteSQL gConnectionID, tSQL, tuser_number, tname, tsex, tdepartment, tschool, tlevel, tuser_mobile, temail, tcreate_date, tuser_status, tuser_image
-- check if it worked and display an error message if it didn't


-- check if it worked and display an error message if it didn't
-- & set the connection ID global
if tResult > 0 then
put tResult into gConnectionID
answer info "Connected to the database." & cr & "Connection ID = " & gConnectionID
else
put empty into gConnectionID
answer error "Unable to connect to the database:" & cr & tResult
else
put empty into gConnectionID
answer error "Unable to connect to the database:" & cr & tResult
end if
end mouseUp

[Klaus]

Hi binary,

1. welcome to the forum!

2. Please do not use all caps for the subject or wherever, this is the internet equivalent to
shouting in the real world, thank you!
I changed the subject accordingly.

3. You are accessing the database (insert...) BEFORE you have checked if the connection was established correctly!?
And you are supplying a wrong variable as the connection id, which cannot work.

Try this:

Code: Select all

...
-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

## You need to check here and now:
if tResult > 0 then

   ## The variable is now filled with the correct value:
   put tResult into gConnectionID
   answer info "Connected to the database." & cr & "Connection ID = " & gConnectionID
 else 
   put empty into gConnectionID
   answer error "Unable to connect to the database:" & cr & tResult

  ## Exit the handler in this case!
   EXIT mouseup
end if

--
## Now database access should work as exspected:
put "INSERT INTO" && tTableName && "( user_number, name, sex, department, school, level, user_mobile, email, create_date, user_status, user_image )" && "VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,) " into tSQL

revExecuteSQL gConnectionID, tSQL, tuser_number, tname, tsex, tdepartment, tschool, tlevel, tuser_mobile, temail, tcreate_date, tuser_status, tuser_image
## check if it worked and display an error message if it didn't
## Yes but the new RESULT
if the result <> emtpy then
## No success
...

Best

Klaus

[bangkok]

At least 2 problems with your code :

-you mix tResult and gConnectionID
-you use in a wrong way the "variables" in your query (you forgot the ":")

-and you probably use in a wrong way quotes

So to summarize, make it more easy, at least at the beginning :

Code: Select all

 put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

if tResult is not a number then
answer warning "Error "&tResult
exit to top
end if

 put "INSERT INTO user_registration_form (user_number, name, sex) VALUES ('1', 'myName','m')" into tSQL

 revExecuteSQL tResult, tSQL

answer the result

revCloseDatabase tResult

[Klaus]

-you use in a wrong way the "variables" in your query (you forgot the ":")

ah, yes, missed that one

[binary]

@bangkok the idea worked but is not getting user input from the field rather is inserting the field name
put "tusernumber" into userNumber
put "tname" into userName
put "tsexstatus" into usex
put "tdepartment" into department
put "tschool" into uschool
put "tname" into dlevel
put "tuaddress" into uaddress
put "tmobile" into usermobile
put "temail" into mail
put "tdateregistered" into createdate
put "tname" into userstatus
put "tholder" into userimage
put "INSERT INTO user_registration_form (usernumber, name, sex, department, school, level, address, user_mobile, email, create_date, user_status, user_image) VALUES ('userNumber', 'userName', 'usex', 'department', 'uschool', 'dlevel', 'uaddress', 'usermobile', 'mail', 'createdate', 'userstatus', 'userimage')" into tSQL
tname is my field name and is inerting UserName to database


and these does not get values
VALUES ('userNumber', 'userName', 'usex', 'department', 'uschool', 'dlevel', 'uaddress', 'usermobile', 'mail', 'createdate', 'userstatus', 'userimage')"

from these
put "tusernumber" into userNumber
put "tname" into userName
put "tsexstatus" into usex
put "tdepartment" into department
put "tschool" into uschool
put "tname" into dlevel
put "tuaddress" into uaddress
put "tmobile" into usermobile
put "temail" into mail
put "tdateregistered" into createdate
put "tname" into userstatus
put "tholder" into userimage

[binary]

I got it now...inserted successfully. Thank you all

[Klaus]

Hi binary,

you're welcome!

Could you please tell what the problem was?
I am not seeing it yet


Best

Klaus

[dave.kilroy]

Hi binary - I think you are the guy asking about a database insert issue over at the LinkedIn group - pleased to see you got your code working (I told you people in this forum were smart) - although I notice you got it working on your own. That is often the case, just by explaining a problem to someone else and answering their questions can be enough for an 'aha' moment

[binary]

@dave.kilroy you are right cos' their explanation helped me to achieved that
Look at d code that worked for me.
on mouseUp
-- use a global variable to hold the connection ID so other scripts can use it
global gConnectionID

-- set up the connection parameters - edit these to suit your database
put "localhost" into tDatabaseAddress
put "elibrary" into tDatabaseName
put "root" into tDatabaseUser
put "" into tDatabasePassword

-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

put "usernumber, name, sex, department, school, level, address, user_mobile, email, create_date, user_status, user_image" into tFields
put the field "tusernumber" into tuserNumber
put the field "tname" into tuserName
put the button "tsexstatus" into tusex
put the field "tdepartment" into tdepartment
put the field "tschool" into tuschool
put the button "tlevel" into tlevel
put the field "tuaddress" into tuaddress
put the field "tmobile" into tusermobile
put the field "temail" into tmail
put the field "tdateregistered" into tcreatedate
put the button "tcmbuserstatus" into tuserstatus
put the field "tholder" into tuserimage
put "INSERT INTO user_registration_form ("& tFields &") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12)" into tSQL

revExecuteSQL tResult, tSQL, "tuserNumber", "tuserName","tusex", "tdepartment", "tuschool", "tlevel", "tuaddress", "tusermobile", "tmail", "tcreatedate", "tuserstatus", "tuserimage"

-- 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
close card
end mouseUp

[snm]

"put THE field ..." Is wrong - should be just "put field ..."

Marek

[binary]

But it works for me
put field can also work too

[Klaus]

Hi binary,

But it works for me

yep, but it is just wrong syntax!

One day in the future the engine will be less forgiving than toaday
(it is with every new version!), you are in trouble and we can happily
say that you have been warned!


Best

Klaus

[dave.kilroy]

Hi binary

My understanding (someone please correct me) is that LiveCode's built-in functions (things like 'the seconds' which returns the number of seconds since midnight on 01-01-1970) can be called in two ways: the first is the 'conventional' way as in: "put seconds() into tVariable" whilst the more 'English-like' way is: "put the seconds into tVariable".

In other words the word "the" has a special use in LiveCode and should be used carefully or else in some situations LiveCode may be fooled into thinking you intend to call a built-in function when you don't - which could result in errors, hangs, screens-of-death and possibly Armageddon...