Please help me with my INSERT INTO syntax. i keep getting an error and i don't know why.

I am trying to write a simple program to insert boolean and string values into a database using SQL, but i keep getting an error in my INSERT INTO syntax.
I am coding on Delphi 2010.
Here is my code:
procedure TForm2.btnCreateNewProfileClick(Sender: TObject);
VAR
  iNum :  Integer;
  sNum : String;
begin
  if (Length(edtNewName.Text) > 25) or (Length(edtNewName.Text) < 1) then
    ShowMessage('Username must be between 1 and 25 characters long!')      // Name characrter validation
    else
      if (Length(edtNewPassword.Text) > 25) or (Length(edtNewPassword.Text) < 5) then
        ShowMessage('Password must be between 5 and 25 characters long!')  // password validation
        else
          if NOT Ord(edtNewPassword.Text) = Ord(edtPassConfirm.Text) then
            ShowMessage('Passwords do not match!');


  tblUsers.Open;                    //save input to database
  tblUsers.Last;
  inum := tblUsers['profile'];
  inum := inum + 1;
  sNum := IntToStr(iNum);
  qryDatabase.SQL.Clear;
  qryDatabase.SQL.Add('INSERT INTO Users (UserName, Password, Profile, Admin) ');
  qryDatabase.SQL.Add('VALUES ('+ QuotedStr(edtNewName.Text)+','+QuotedStr(edtNewPassword.Text)+',' +QuotedStr(sNum)+ ',' +cbxAdmin.Checked+ ')');     // apparently this is wrong
  qryDatabase.ExecSQL;
  qryDatabase.Open;

end;

Open in new window

Anthony HiscockAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marco GasiFreelancerCommented:
How is your table definition, please?
Kanti PrasadCommented:
Hi

remove cap N so change  sNum := IntToStr(iNum);  to  sNum := IntToStr(inum); and see that your data types in the user table are strings. I am assuming cbxAdmin.Checked is also a string or else change the admin data type accordingly
mlmccCommented:
What error are you getting?

If that is the code then you will always insert the user even if there are problems with the name or password.

mlmcc
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

aikimarkCommented:
I assume Profile is a numeric field, so you need to removed the QuotedStr() invocation from the
QuotedStr(sNum)
expression
Sinisa VukSoftware architectCommented:
Because we don't know table definition ... we can assume only. I think that Admin field should be:
QuotedStr(cbxAdmin.Checked)
Geert GOracle dbaCommented:
would you like good performance ?
use bind variables !

what type of database are you using ?

procedure TForm2.btnCreateNewProfileClick(Sender: TObject);
VAR
  iNum :  Integer;
  sNum : String;

begin
  // Checks
  if (Length(edtNewName.Text) > 25) or (Length(edtNewName.Text) < 1) then
    ShowMessage('Username must be between 1 and 25 characters long!')      // Name characrter validation
    else
      if (Length(edtNewPassword.Text) > 25) or (Length(edtNewPassword.Text) < 5) then
        ShowMessage('Password must be between 5 and 25 characters long!')  // password validation
        else

   // Checking the ordinal value of a text doesn't mean it's the same !
   // a comparison is better
          if edtNewPassword.Text <> edtPassConfirm.Text then
            ShowMessage('Passwords do not match!');

  // really open the complete table to get the next id ????
  // use an autoincrement (or a sequence number !)
  // some database allow for a returning clause 

  //tblUsers.Open;                    //save input to database
  //tblUsers.Last;
  //inum := tblUsers['profile'];
  //inum := inum + 1;
  //sNum := IntToStr(iNum);
  qryDatabase.SQL.Clear;
  // Use parameters ... not quotedstr !
  // your query gets cached ... your way, you just fill up the cache with 1 time only querries
  qryDatabase.SQL.Add('INSERT INTO Users (UserName, Password, Profile, Admin) ');
  qryDatabase.SQL.Add('VALUES (:username, :password, :profile, :admin)');     // apparently this is wrong ... yes very wrong !
  qryDatabase.ParamByName('USERNAME').AsString := edtNewName.Text;
  qryDatabase.ParamByName('PASSWORD').AsString := edtNewPassword.Text;
  qryDatabase.ParamByName('PROFILE').AsInteger  := ??? ; // this should be autogenerated ?
  qryDatabase.ParamByName('ADMIN').AsInteger  := IfThen(cbxAdmin.Checked, 1, 0); // use 1 or 0, most databases don't know boolean
  qryDatabase.ExecSQL;
  // qryDatabase.Open; it's either executing an insert/update/delete statement or opening a select ... not both

end;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
i have no clue why the other guys are waiting for your table definition to give comments
there was already so much wrong with your code, that they could have helped you with that

some more pointers ...
1. you are hard checking for length of 25
catching the error upon execsql and showing the warning should suffice
otherwise you'll have to adapt your code every time you extend the database field

in it's simplest form :
try
  qry.ExecSQL;
except
  on E: Exception do 
    ShowMessage(E.Message);
end;

Open in new window


2: only check for minimum password length
the maximum will be caught by the database if you do as in pointer 1

3: use sequences (or autonumber fields) to generate id's
if you use oracle, the syntax is like this:
qry.sql.Text := 
  'INSERT INTO TABLE (COLUMN1, COLUMN2) VALUES (:COL1, :COL2) RETURNING ID INTO :ID';
qry.ParamByName('COL1').AsString := 'col1';
qry.ParamByName('COL2').AsString := 'col2';
qry.ReturnParams := True;
qry.ExecSQL;
id := qry.ParamByName('ID').AsInteger;

Open in new window

Sinisa VukSoftware architectCommented:
I think that nobody is waiting except for asker response ... if he saw these comments (at all).
Anthony HiscockAuthor Commented:
I see people asking for my table definition, I'm not sure what exactly that is though? I only started studying IT recently so i guess I still have much to learn.
Geert GOracle dbaCommented:
studying IT ?
that's like studying medicine ...

IT and Medicine have 1 thing in common : they have a lot different kinds of knowledge bases
programming, databases, hardware, software, networking ... just to name a few top items
off course each item, holds a load of specialties within, like Delphi in programming


we can only help you on your long path ... :)
Anthony HiscockAuthor Commented:
I was using Microsoft Access 2010. To specify my studies would have been irrelevant to my problem, i know it is a broad field, in year 1 we do many different things; photoshop, flash, c#, networking, databases etc. specialising later. My goal for now is software engineering and going into either security or gaming... Still weighing the odds. Nevertheless thank you Geert Gruwez your first two comments helped :)
Anthony HiscockAuthor Commented:
Thanks, everyone. I will be using this website a lot from now on. Hopefully i can start contributing as I learn more.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.