Simon Erasmus Simon Erasmus - 3 months ago 17
SQL Question

SQL table field named "Language" giving SQL error - delphi

I am building a application for school that displays and adds entries to tables in a neat and user friendly way. Whilst programming the "adding of new records" part, I came across this error

Syntax error in INSERT INTO statement


I tried fixing it by manually going through the list of fields I am entering and found that only one gave an error even though this field is exactly the same to all the other fields. If I purposefully spell the field wrong I get another error. My Code looks like this:

with dmInfo do
begin
qryInfo.SQL.Clear;
qryInfo.SQL.Add('INSERT INTO tblGymnast');
qryInfo.SQL.Add('(GymnastID, Surname, Name, NickName, FamilyID, PosCode, CellNo, SAGFID, Photo, StartDate, BirthDate, Gender, Race, Category, IDNum, UpgrdTo, SchoolID, LevelID, TransID, DokterID, MedNum, MedID, Language)');

qryInfo.SQL.Add(' VALUES(:GymnastID, :Surname, :Name, :NickName, :FamilyID, :PosCode, :CellNo, :SAGFID, :Photo, :StartDate, :BirthDate, :Gender, :Race, :Category, :IDNum, :UpgrdTo, :SchoolID, :LevelID, :TransID, :DokterID, :MedNum, :MedID, :Language)');

qryInfo.Parameters.ParamByName('GymnastID').Value:= 'a';
qryInfo.Parameters.ParamByName('Surname').Value:= 'a';
qryInfo.Parameters.ParamByName('Name').Value:= 'a';
qryInfo.Parameters.ParamByName('NickName').Value:= 'a';
qryInfo.Parameters.ParamByName('FamilyID').Value:= 'a';
qryInfo.Parameters.ParamByName('PosCode').Value:= 'a';
qryInfo.Parameters.ParamByName('CellNo').Value:= 'a';
qryInfo.Parameters.ParamByName('SAGFID').Value:= 'a';
qryInfo.Parameters.ParamByName('Photo').Value:= 'a';
qryInfo.Parameters.ParamByName('StartDate').Value:= 'a';
qryInfo.Parameters.ParamByName('BirthDate').Value:= 'a';
qryInfo.Parameters.ParamByName('Gender').Value:= 'a';
qryInfo.Parameters.ParamByName('Language').Value:= 'a'; //This gives the error
qryInfo.Parameters.ParamByName('Race').Value:= 'a';
qryInfo.Parameters.ParamByName('Category').Value:= 'a';
qryInfo.Parameters.ParamByName('IDNum').Value:= 'a';
qryInfo.Parameters.ParamByName('UpgrdTo').Value:= 'a';
qryInfo.Parameters.ParamByName('SchoolID').Value:= 'a';
qryInfo.Parameters.ParamByName('LevelID').Value:= 'a';
qryInfo.Parameters.ParamByName('TransID').Value:= 'a';
qryInfo.Parameters.ParamByName('DokterID').Value:= 'a';
qryInfo.Parameters.ParamByName('MedNum').Value:= 'a';
qryInfo.Parameters.ParamByName('MedID').Value:= 'a';

qryInfo.ExecSQL;
end;


Please help as I have been struggling with this for the whole day and haven't gotten anywhere.

Regards

Answer

[] or "" signifys to the RDBMs that the word is a column name not a reserved keyword. Language is a reserved keyword in SQL.

for good measure I also changed the parameter name to Lang because you identified the parameter assignment as teh issue location.

with dmInfo do
begin
  qryInfo.SQL.Clear;
  qryInfo.SQL.Add('INSERT INTO tblGymnast');
  qryInfo.SQL.Add('(GymnastID, Surname, Name, NickName, FamilyID, PosCode, CellNo, SAGFID, Photo, StartDate, BirthDate, Gender, Race, Category, IDNum, UpgrdTo, SchoolID, LevelID, TransID, DokterID, MedNum, MedID, [Language])');

  qryInfo.SQL.Add(' VALUES(:GymnastID, :Surname, :Name, :NickName, :FamilyID, :PosCode, :CellNo, :SAGFID, :Photo, :StartDate, :BirthDate, :Gender, :Race, :Category, :IDNum, :UpgrdTo, :SchoolID, :LevelID, :TransID, :DokterID, :MedNum, :MedID, :Lang)');

      qryInfo.Parameters.ParamByName('GymnastID').Value:= 'a';
      qryInfo.Parameters.ParamByName('Surname').Value:= 'a';
      qryInfo.Parameters.ParamByName('Name').Value:= 'a';
      qryInfo.Parameters.ParamByName('NickName').Value:= 'a';
      qryInfo.Parameters.ParamByName('FamilyID').Value:= 'a';
      qryInfo.Parameters.ParamByName('PosCode').Value:= 'a';
      qryInfo.Parameters.ParamByName('CellNo').Value:= 'a';
      qryInfo.Parameters.ParamByName('SAGFID').Value:= 'a';
      qryInfo.Parameters.ParamByName('Photo').Value:= 'a';
      qryInfo.Parameters.ParamByName('StartDate').Value:= 'a';
      qryInfo.Parameters.ParamByName('BirthDate').Value:= 'a';
      qryInfo.Parameters.ParamByName('Gender').Value:= 'a';
      qryInfo.Parameters.ParamByName('Lang').Value:= 'a'; //This gives the error
      qryInfo.Parameters.ParamByName('Race').Value:= 'a';
      qryInfo.Parameters.ParamByName('Category').Value:= 'a';
      qryInfo.Parameters.ParamByName('IDNum').Value:= 'a';
      qryInfo.Parameters.ParamByName('UpgrdTo').Value:= 'a';
      qryInfo.Parameters.ParamByName('SchoolID').Value:= 'a';
      qryInfo.Parameters.ParamByName('LevelID').Value:= 'a';
      qryInfo.Parameters.ParamByName('TransID').Value:= 'a';
      qryInfo.Parameters.ParamByName('DokterID').Value:= 'a';
      qryInfo.Parameters.ParamByName('MedNum').Value:= 'a';
      qryInfo.Parameters.ParamByName('MedID').Value:= 'a';

  qryInfo.ExecSQL;
end;
Comments