Delphi SQL insert into statement error

Question

qryreg.SQL.Add('Insert into RegistreerTB');
qryreg.SQL.add('Name , Surname, E-mail, Password)');
qryreg.SQL.Add('Values ('+quotedstr(edtname.Text)+','+quotedstr(edtsname.Text)+','+quotedstr(edtemail.Text)+','+quotedstr(edtpassuse.Text)+')');
qryreg.ExecSQL ;
qryreg.SQL.Text := 'Select * from RegistreerTB';
qryreg.Open ;

This is the code im using atm with delphi im trying to save data to my database from editboxes. The error im getting is EOELeException "Insert into statement"

ty in advance

Answer 1

Your problem is in the first line. I made the correction below. you need an opening parenthesis.

qryreg.SQL.Add('Insert into RegistreerTB ('); 
qryreg.SQL.Add('Name , Surname, E-mail, Password)'); 
qryreg.SQL.Add('Values ('+quotedstr(edtname.Text)+','+quotedstr(edtsname.Text)+','+quotedstr(edtemail.Text)+','+quotedstr(edtpassuse.Text)+')'); 
qryreg.ExecSQL ; 
qryreg.SQL.Text := 'Select * from RegistreerTB'; 
qryreg.Open ; 

see if this works

qryreg.SQL.Add("Insert into RegistreerTB ("); 
qryreg.SQL.Add("Name , Surname, E-mail, Password)"); 
qryreg.SQL.Add("Values ('"+edtname.Text+"','"+edtsname.Text +"','"+edtemail.Text+"','"+edtpassuse.Text +"')"); 
qryreg.ExecSQL ; 
qryreg.SQL.Text := "Select * from RegistreerTB"; 
qryreg.Open ; 

Answer 2

1. May be you have to call qryreg.SQL.Clear before your first line.
2. Why not to use parameters ?

Answer 3

As John's answer points out, you need to have parentheses around the column names before VALUES. You need to make sure all the column names are valid SQL identifiers. If they aren't, as in the case for E-mail, you need to quote or escape them according to your database's syntax rules. For example, MySQL uses grave accents, Microsoft SQL uses brackets, and Oracle and Postgresql use quotation marks.

Answer 4

As oodesigner stated, a better method would be to use parameters. I don't know what text book you are looking at, but the code given isn't really best practice (it isn't worst practice either, at least it uses QuotedStr rather than '''' + edtname.Text + '''' which fails the first time you use something like O'Connell, and allows SQL injection attacks.

Using parameters and assuming SQL Server syntax as per Rob's answe, and assuming TADOQuery (based on the EOLEException) the code would be something like:

qryreg.SQL.Add('Insert into RegistreerTB');  
qryreg.SQL.Add('(Name , Surname, [E-mail], Password)');  //SQL Server syntax with square brackets

// OR qryreg.SQL.Add('(Name , Surname, "E-mail", Password)');  //Oracle/Postgres syntax with double quotes
// OR qryreg.SQL.Add('(Name , Surname, `E-mail`, Password)');  //MySQL syntax with grave accent

qryreg.SQL.Add('Values :Name, :Surname, :Email, :Password)'); 

qryreg.Parameters.ParamByName('Name').Value := edtName.Text;
qryreg.Parameters.ParamByName('Surname').Value := edtSName.Text;
qryreg.Parameters.ParamByName('Email').Value := edtEmail.Text;
qryreg.Parameters.ParamByName('Password').Value := edtPassUse.Text;

qryreg.ExecSQL;  
qryreg.SQL.Text := 'Select * from RegistreerTB';  
qryreg.Open ;