MY SQL Insert

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
Quinton B.
Posts: 108
Joined: Mon Mar 20, 2017 5:13 am

MY SQL Insert

Post by Quinton B. » Sun May 12, 2019 12:37 pm

Hello, i've tried different variations of inserting data into my sql database without luck, whats wrong with it?

Code: Select all

put "INSERT INTO PAV2_ACCOUNTS (" & tALLFIELDS & ") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20)" into lSQL
      -------------------------------
      --THE BELLOW LINE COULD FUCK UP
      -------------------------------
      revExecuteSQL gConnectionID, lSQL, "tImageCheck", "tUserNameCheck", "tPasswordCheck", "tDoDEmailCheck", "tDSNCheck", "tPersonellNumberCheck", "tAddressCheck", "tSecurityQuestion1", "tSecurityAnswer1", "tSecurityQuestion2", "tSecurityAnswer2", "tSecurityQuestion3", "tSecurityAnswer3", "tBranchCheck", "tBaseCheck", "tOperationsGroupCheck", "tSquadronCheck", "tFlightCheck", "tRankCheck", "tRandomAlphaNumericalGenerator"
    

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: MY SQL Insert

Post by Klaus » Sun May 12, 2019 12:47 pm

Hi Quinton,

please show us the content of tALLFIELDS.


Best

Klaus

Quinton B.
Posts: 108
Joined: Mon Mar 20, 2017 5:13 am

Re: MY SQL Insert

Post by Quinton B. » Sun May 12, 2019 12:59 pm

Code: Select all

put tImageCheck, tUserNameCheck, tPasswordCheck, tDoDEmailCheck, tDSNCheck, tPersonellNumberCheck, tAddressCheck, tSecurityQuestion1, tSecurityAnswer1, tSecurityQuestion2, tSecurityAnswer2, tSecurityQuestion3, tSecurityAnswer3, tBranchCheck, tBaseCheck, tOperationsGroupCheck, tSquadronCheck, tFlightCheck, tRankCheck, tRandomAlphaNumericalGenerator into tALLFIELDS
   

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: MY SQL Insert

Post by Klaus » Sun May 12, 2019 1:06 pm

Does it works with QUOTES?

Code: Select all

...
put "tImageCheck, tUserNameCheck, tPasswordCheck, tDoDEmailCheck, tDSNCheck, tPersonellNumberCheck, tAddressCheck, tSecurityQuestion1, tSecurityAnswer1, tSecurityQuestion2, tSecurityAnswer2, tSecurityQuestion3, tSecurityAnswer3, tBranchCheck, tBaseCheck, tOperationsGroupCheck, tSquadronCheck, tFlightCheck, tRankCheck, tRandomAlphaNumericalGenerator" into tALLFIELDS
...

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: MY SQL Insert

Post by Klaus » Sun May 12, 2019 1:14 pm

Are these names of variables or the name of the database fields?

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: MY SQL Insert

Post by bangkok » Mon May 13, 2019 12:58 am

If you look at past posts, it's obvious : this syntax (variable list and array) create many problems for newbies.

It's important to go step by step.

Start with the SIMPLE SYNTAX, aka "i execute a query that i can read"

Therefore :

Code: Select all

put "INSERT INTO PAV2_ACCOUNTS (column1,column2,column3) VALUES ('"&tImageCheck&"','"&tUserNameCheck&"','"&tPasswordCheck&"')" into lSQL

answer lSQL
revExecuteSQL gConnectionID, lSQL

put the Result into tResult
answer tResult
Voilà :
-it's quare
-you have control
-you can "read" the full SQL query, and therefore you can spot any error or syntax problem
:)

Quinton B.
Posts: 108
Joined: Mon Mar 20, 2017 5:13 am

Re: MY SQL Insert

Post by Quinton B. » Mon May 13, 2019 12:55 pm

I'm still having problems with it:
The x are to not display the actual data im putting into the database, this is just to allow me to validate many other things on the app.

Code: Select all

on mouseup
   put "123" into tImageCheck
   put "xxx" into tUserNameCheck
   put "xxx!!" into tPasswordCheck
   put "xxxx.mil" into tDoDEmailCheck
   put "xx" into tDSNCheck
   put "xxx" into tPersonellNumberCheck
   put "What was your favorite sport in high school?" into tSecurityQuestion1
   put "xxx" into tSecurityAnswer1
   put "What is your favorite movie?" into tSecurityQuestion2
   put "xxxx" into tSecurityAnswer2
   put "What was the name of the company where you had your first job?" into tSecurityQuestion3
   put "xxx" into tSecurityAnswer3
   put "xxx" into tBranchCheck
   put "xxx" into tBaseCheck
   put "xxxx" into tOperationsGroupCheck
   put "xxxx" into tSquadronCheck
   put "xxxxx" into tFlightCheck
   put "xxx" into tRankCheck
   put "tImageCheck, tUserNameCheck, tPasswordCheck, tDoDEmailCheck, tDSNCheck, tPersonellNumberCheck, tAddressCheck, tSecurityQuestion1, tSecurityAnswer1, tSecurityQuestion2, tSecurityAnswer2, tSecurityQuestion3, tSecurityAnswer3, tBranchCheck, tBaseCheck, tOperationsGroupCheck, tSquadronCheck, tFlightCheck, tRankCheck" into tALLFIELDS
   put "xxxx" into tBA
   put "xxxx" into tDBN
   put "xxxx" into tDBU
   put "xxx" into tDBP
   put revOpenDatabase("mysql", tBA, tDBN, tDBU, tDBP) into lLogin
   put lLogin into gConnectionID
   if lLogin is a number then
      put "INSERT INTO PAV2_ACCOUNTS (" & tALLFIELDS & ") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23)" into lSQL
      ---------------------------
      --THE BELLOW LINE COULD FUCK UP
      ---------------------------
      revExecuteSQL gConnectionID, lSQL, "tImageCheck", "tUserNameCheck", "tPasswordCheck", "tDoDEmailCheck", "tDSNCheck", "tPersonellNumberCheck", "tAddressCheck", "tSecurityQuestion1", "tSecurityAnswer1", "tSecurityQuestion2", "tSecurityAnswer2", "tSecurityQuestion3", "tSecurityAnswer3", "tBranchCheck", "tBaseCheck", "tOperationsGroupCheck", "tSquadronCheck", "tFlightCheck", "tRankCheck"
      if item 1 of tData = "revdberr" then
         answer error "There was a problem querying the database:" & cr & tData
      else
         answer "Data Added"
         revCloseDatabase gConnectionID
      end if
   end if
end mouseup

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: MY SQL Insert

Post by Klaus » Mon May 13, 2019 1:30 pm

Hi Quinton,

as I wrote:
Are these names of variables or the name of the database fields?
Now I see these are the names of your variables.
Question is, are these ALSO the names of your database fields?
I doubt, however you treat them like they are. 8)


Best

Klaus

Quinton B.
Posts: 108
Joined: Mon Mar 20, 2017 5:13 am

Re: MY SQL Insert

Post by Quinton B. » Mon May 13, 2019 5:09 pm

All the variables have the letter t before them, while all others are the actual database columns.

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: MY SQL Insert

Post by Klaus » Mon May 13, 2019 5:18 pm

Sure?

Code: Select all

...
put "123" into tImageCheck
...
put "tImageCheck, tUserNameCheck,..." into tALLFIELDS
...
8)

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”