Skip to content

SQL Unknown column error

Stack Overflow

Question

I'm trying to insert some information to MySQL with Pascal, but when I run the program I get the error

unknown column 'mohsen' in field list

This is my code

procedure TForm1.Button1Click(Sender: TObject);
var
  aSQLText: string;
  aSQLCommand: string;
  namee:string;
  family:string;
begin
  namee:='mohsen';
  family:='dolatshah';
  aSQLText:= 'INSERT INTO b_tbl(Name,Family) VALUES (%s,%s)';
  aSQLCommand := Format(aSQLText, [namee, family]);
  SQLConnector1.ExecuteDirect(aSQLCommand);
  SQLTransaction1.Commit;
end;

How can I solve this problem?

Answer

It's because your

VALUES (%s,%s)

isn't surrounding the namee and family variable contents by quotes. Therefore, your back-end Sql engine thinks your mohsen is a column name, not a value.

Instead, use, e.g.

VALUES (''%s'',''%s'')

as in

  Namee := 'mohsen';
  Family := 'dolatshah';
  aSQLText:= 'INSERT INTO b_tbl(Name,Family) VALUES (''%s'',''%s'')';
  aSQLCommand := Format(aSQLText,[namee,family]);

In the original version of my answer, I explained how to fix your problem by "doubling up" single quotes in the Sql you were trying to build, because it seemed to me that you were having difficulty seeing (literally) what was wrong with what you were doing.

An alternative (and better) way to avoid your problem (and the one I always use in real life) is to use the QuotedStr() function. The same code would then become

aSQLText := 'INSERT INTO b_tbl (Name, Family) VALUES (%s, %s)'; 
aSQLCommand := Format(aSQLText, [QuotedStr(namee), QuotedStr(family)]);

According to the Online Help:

Use QuotedStr to convert the string S to a quoted string. A single quote character (') is inserted at the beginning and end of S, and each single quote character in the string is >repeated.

What it means by "repeated" is what I've referred to as "doubling up". Why that's important, and the main reason I use QuotedStr is to avoid the Sql db-engine throwing an error when the value you want to send contains a single quote character as in O'Reilly.

Try adding a row containing that name to your table using MySql Workbench and you'll see what I mean.

So, not only does using QuotedStr make constructing SQL statements as strings in Delphi code less error-prone, but it also avoid problems at the back-end, too.