TADOCommand and TADOQuery do not accept INSERT statement as valid 3

[ozzydaws]

Hi everyone, when I try and run this code, Delphi tells me I the INSERT statement has a syntax error when, as far as I can see, it doesn't.

  ADOCommand1.ConnectionString  := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+frm_LogIn.SpreadSheetLocation+'btbse_ScienceBookings.mdb;Persist Security Info=False';
  ADOCommand1.CommandText := 'INSERT INTO tbl_Bookings (User, Period) VALUES (15, "p1")';
  ADOCommand1.CommandType := cmdText;

The problem does not happen if I use:

'INSERT INTO tbl_Bookings (15, "p1")

However if I then add an autonumber field to my table in MS Access, the application throws an error saying the number of values is not the same as the number of fields.

Can anyone shed any light on this infuriating problem? Is my syntax correct?

 

[DjangMan]

Check that 'User' or 'Period' are not reserved words. You might need to surround the field names with square brackets.

 

[djjd47130]

ADOCommand1.CommandText := 'INSERT INTO [tbl_Bookings] ([User], [Period]) VALUES (15, "p1")';

I'm not familiar with using anything other than MS SQL, but I always use single quotes, however in Delphi, requires a little more, like this:

ADOCommand1.CommandText := 'INSERT INTO [tbl_Bookings] ([User], [Period]) VALUES (15, ''p1'')';

Or, you can use the ADOQuery's live updates, so you can do this...

var 
  vUser: Integer;
  vPeriod: String;

...

vUser:= 15;
vPeriod:= 'p1';

...

ADOQuery1.SQL.Text:= 'select * from [tbl_Bookings] where [User] = '+ IntToStr(vUser);
ADOQuery1.Open;
  if ADOQuery1.IsEmpty then ADOQuery1.Append else ADOQuery1.Edit;
    ADOQuery1['User']:= vUser;
    ADOQuery1['Period']:= vPeriod;
  ADOQuery1.Post;
ADOQuery1.Close;

When making the SQL statement, I'm adding a where condition for two reasons: First of all, so it doesn't return the entire table when I open the query, and secondly so that if such a record already exists (with the same user ID), it will edit the existing record rather than creating a new one (prevent redundancy). If no such record exists, it prepares the query to append a new record (insert), otherwise if a record does exist, it prepares the query to edit that record (update). Then you can either Post or Cancel the edits made. This of course can be tweaked into many different scenarios. I rarely ever need to execute SQL commands like Insert, Update, etc.

JD Solutions

 

[ozzydaws]

Brilliant, thank you for your replies. As always, hours of agonising was solved with a few characters. The working code is below:

ADOCommand1.CommandText := 'INSERT INTO [tbl_Bookings] ([User], [Period]) VALUES (15, ''p1'')';
  ADOCommand1.CommandType := cmdText;
  ADOCommand1.Execute;

User and Period are not reserved as far as I can tell, either.

 

[DjangMan]

Excellent news. Glad it's fixed.

FYI: Period is not a reserved word but User is. That's why the square brackets solved the issue.

http://support.microsoft.com/kb/286335

 

[djjd47130]

The method I posted above can also be used to obtain those values as well. All my interaction with any SQL database is through TADOQuery and it's become extremely easy for me. The same dataset can be used in multiple ways, therefore you don't need to use the Insert/Update/Delete commands.

For example, I'm creating a DLL with a number of simple functions, like the one below. This function retrieves the First Name of a specified user in the database specified in the connection string.

function GetUserFirstName(ConnectionString: PChar; UserName: PChar): PChar; stdcall;
var
  Q: TADOQuery;
  cUserName: String;
begin
  Result:= '';
  cUserName:= UserName;
  Q:= TADOQuery.Create(nil);
  try
    Q.ConnectionString:= ConnectionString;
    try
      Q.SQL.Text:= 'select * from Users where Login = '''+ cUserName +'''';
      Q.Open;
        if not Q.IsEmpty then Result:= PChar(Q.FieldByName('FName').AsString);
      Q.Close;
    except
      on e: exception do Result:= '';
    end;
  finally
    if assigned(Q) then begin
      if Q.Active then Q.Close;
      Q.Free;
    end;
  end;
end;

You can also loop through the records and add them to a list, like below. Here, I create a list (TStringList) with objects (TValue) holding the ID and UserName of each user.

type 
  TValue = class(TObject)
    ID: Integer;
    Value: String;
  end;

  function GetUsers(ConnectionString: PChar): TStringList;
  var
    Q: TADOQuery;
    V: TValue;
  begin
    Q:= TADOQuery.Create(nil);
    Result:= TStringList.Create;
    try
      Q.ConnectionString:= ConnectionString;
      Q.SQL.Text:= 'Select * from [Users]';
      Q.Open;
        if not Q.IsEmpty then begin
          Q.First;
          While Not Q.EOF do begin
            V:= TValue.Create;
              V.ID:= Q.FieldByName('ID').AsInteger;
              V.Caption:= Q.FieldByName('UserName').AsString;
            Result.AddObject(V.Caption, V);
            Q.Next;
          end;
        end;
      Q.Close;
    finally
      if assigned(Q) then begin
        if Q.Active then Q.Close;
        Q.Free;
      end;
    end;
  end;

On the other hand, if I wanted to do the reverse of this and save the values of the list to the database, it would be something like this:

type
  TValue = class(TObject)
    ID: Integer;
    Value: String;
  end;

  function SaveUsers(ConnectionString: PChar; UserList: TStringList): Bool;
  var
    Q: TADOQuery;
    V: TValue;
    X: Integer;
  begin
    Result:= False;
    Q:= TADOQuery.Create(nil);
    try
      for X:= 0 to UserList.Count - 1 do begin
        V:= TValue(UserList.Objects[X]);
        Q.ConnectionString:= ConnectionString;
        Q.SQL.Text:= 'Select * from Users where ID = 'IntToStr(V.ID);
        Q.Open;
          Q.Edit;
            Q['UserName']:= V.Caption;
          Q.Post;
        Q.Close;
      end;
      Result:= True;
    finally
      if assigned(Q) then begin
        if Q.Active then Q.Close;
        Q.Free;
      end;
    end;
  end;

Some extra error catching would be required on that function, but it should work (although not tested at all).

I'm assigning the connection string to Q each time, to make sure that the password isn't cleared (as it happens in Windows Vista and 7).


JD Solutions

 

[DjangMan]

Thanks for that post! I like to see functions that make my life easier.

Here's an interfaced object that came from whosrdaddy (I'm pretty sure it was whosrdaddy but I can't for the life of me search and find the original post). I've added a few functions to the original code such as AsStringT (to trim the value), AsStringQ to return a quoted string, HasValue and IsMissing. Three lines of code in a function and I have a value from the database and the query is freed from memory.

unit unit_QryInterfaceD2009;

{
USAGE:
procedure DoSomeQuery;
var
    Qry : IIADOQuery;
begin
    Qry := TIADOQuery.Create(MyDBConnection); // MyDBConnection is your TADOConnection
    // delete/insert/update statements
    Qry.UpdateQuery('DELETE FROM DaTable');
    // parameters
    Qry.ParseQuery('SELECT name FROM DaTable WHERE id=:id';
    Qry.Parameter('id', 10);
    Qry.SelectParsedQuery;
    ShowMessage(Qry.AsString('name'));
end;


}

interface

uses
  Windows,
  SysUtils,
  Classes,
  Controls,
  ADODB,
  DB;

const ADOConnStrTemplate = 'Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=%s;Data Source=%s;User ID=%s;Password=%s';
      MySQL5StrTemplate = 'Provider=MSDASQL.1;Persist Security Info=True;User ID=%0:s;Password=%1:s;Extended Properties="DRIVER={MySQL ODBC 5.1 Driver};SERVER=%2:s;PORT=3306;DATABASE=%3:s;USER=%0:s;PASSWORD=%1:s;OPTION=1048579"';
      MySQL3StrTemplate = 'Provider=MSDASQL.1;Persist Security Info=True;User ID=%0:s;Password=%1:s;Extended Properties="DRIVER={MySQL ODBC 3.51 Driver};SERVER=%2:s;PORT=3306;DATABASE=%3:s;USER=%0:s;PASSWORD=%1:s;OPTION=1048579"';

type
  TDBtype = (dbMsSQL, dbMySQL3, dbMySQL5);

const
  TDBTypeStrings : array[dbMsSQL..dbMySQL5] of string[10] = ('MsSQL', 'MySQL3', 'MySQL5');

type
  TDateTimeType = (dtDate, dtTime, dtDateTime);

  IIADOQuery = interface
  ['{344B711B-FBB2-473B-A864-F5D0ABB7A8F8}']
    procedure ParseQuery(SQL : String);
    procedure SelectParsedQuery;
    procedure UpdateParsedQuery;
    procedure SelectQuery(SQL : String);
    procedure UpdateQuery(SQL : string);
    procedure SelectQueryEx(SQL : String; var Flag : Boolean);
    procedure UpdateQueryEx(SQL : string; var Flag : Boolean);
    procedure UpdateParsedQueryEx(var Flag : Boolean);
    procedure SelectParsedQueryEx(var Flag : Boolean);
    procedure Parameter(ParameterName : String; Value : String); overload;
    procedure Parameter(ParameterName : String; Value : Integer); overload;
    procedure Parameter(ParameterName : String; Value : Boolean); overload;
    procedure Parameter(ParameterName : String; Value : Real); overload;
    procedure Parameter(ParameterName : String; Value : String; CurrencySymbol: String); overload;
    procedure Parameter(ParameterName : String; Value : String; DateTimeType : TDateTimeType); overload;
    function AsString(FieldName : String) : String;
    function AsStringT(FieldName : String) : String; overload;
    function AsStringT(FieldNumber : integer) : String; overload;
    function AsStringQ(FieldName : String) : String;
    function AsInteger(FieldName : String) : Integer;
    function AsFloat(FieldName : String) : Double;
    function AsBoolean(FieldName : String) : Boolean;
    function AsDateTime(Fieldname : String) : TDateTime;
    function IsNull(Fieldname : String): boolean; overload;
    function IsNull: boolean; overload;
    function HasValue(Fieldname : String): boolean;
    function EOF : Boolean;
    function RecordCount : Integer;
    procedure Next;
    procedure Previous;
    procedure Edit;
    procedure Post;
    function Qry : TADOQuery;
  end;

  TIADOQuery = class(TInterfacedObject, IIADOQuery)
  private
  public
    ADOQuery : TADOQuery;
    procedure ParseQuery(SQL : String);
    procedure SelectParsedQuery;
    procedure UpdateParsedQuery;
    procedure SelectQuery(SQL : String);
    procedure UpdateQuery(SQL : string);
    procedure SelectQueryEx(SQL : String; var Flag : Boolean);
    procedure UpdateQueryEx(SQL : string; var Flag : Boolean);
    procedure UpdateParsedQueryEx(var Flag : Boolean);
    procedure SelectParsedQueryEx(var Flag : Boolean);
    procedure Parameter(ParameterName : String; Value : String); overload;
    procedure Parameter(ParameterName : String; Value : Integer); overload;
    procedure Parameter(ParameterName : String; Value : Boolean); overload;
    procedure Parameter(ParameterName : String; Value : Real); overload;
    procedure Parameter(ParameterName : String; Value : String; CurrencySymbol: String); overload;
    procedure Parameter(ParameterName : String; Value : String; DateTimeType : TDateTimeType); overload;
    procedure Parameter(ParameterName: String; Value: TDate); overload;
    function AsString(FieldName : String) : String;
    function AsStringT(FieldName : String) : String; overload;
    function AsStringT(FieldNumber : integer) : String; overload;
    function AsStringQ(FieldName : String) : String;
    function AsInteger(FieldName : String) : Integer;
    function AsFloat(FieldName : String) : Double;
    function AsBoolean(FieldName : String) : Boolean;
    function AsDateTime(Fieldname : String) : TDateTime;
    function IsNull(Fieldname : String): boolean; overload;
    function IsNull: boolean; overload;
    function HasValue(Fieldname : String): boolean;
    function EOF : Boolean;
    function RecordCount : Integer;
    procedure Next;
    procedure Previous;
    procedure Edit;
    procedure Post;
    function Qry : TADOQuery;
    constructor Create(ADOConnection : TADOConnection); overload;
    constructor Create(ADOConnection : TADOConnection; ctDynamic : Boolean); overload;
    destructor Destroy; override;
  end;

function CreateADOConnection(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : TADOConnection;
function CreateConnectionString(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : String;
function StringToDBType(db : string) : TDBtype;

implementation

// general purpose routines
function StringToDBType(db : string) : TDBtype;

var dbtype : TDBtype;

begin
 for dbtype := Low(TDBTypeStrings) to High(TDBTypeStrings) do
  begin
   if AnsiSameText(TDBTypeStrings[dbtype], db) then
    begin
     Result := dbtype;
     Exit;
    end;
  end;
 raise Exception.CreateFmt('Not database type found for "%s"', [db]);
end;

function CreateConnectionString(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : String;

begin
 case DBType of
  dbMsSQL: Result := Format(ADOConnStrTemplate,[Catalog, SQLServer, SQLUsername, SQLPassword]);
  dbMySQL3: Result := Format(MySQL3StrTemplate,[SQLUsername, SQLPassword, SQLServer, Catalog]);
  dbMySQL5: Result := Format(MySQL5StrTemplate,[SQLUsername, SQLPassword, SQLServer, Catalog]);
 end;
end;

function CreateADOConnection(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : TADOConnection;
begin
 Result := TADOConnection.Create(nil);
 Result.ConnectionString := CreateConnectionString(DBType, SQLServer, SQLUsername, SQLPassword, Catalog);
end;


{ TInterfacedADO }

constructor TIADOQuery.Create(ADOConnection : TADOConnection);
begin
    if not Assigned(ADOQuery) then ADOQuery := TADOQuery.Create(nil);
    ADOQuery.Connection := ADOConnection;
// ADOQuery.CursorLocation := clUseServer;
// ADOQuery.CursorType := ctDynamic;
    ADOQuery.ParamCheck := False;
end;

constructor TIADOQuery.Create(ADOConnection: TADOConnection; ctDynamic: Boolean);
begin
    Create(ADOConnection);
end;

destructor TIADOQuery.Destroy;
begin
    FreeAndNil(ADOQuery);
    inherited;
end;

procedure TIADOQuery.ParseQuery(SQL: String);
begin
    ADOQuery.ParamCheck := True;
    ADOQuery.SQL.Text := SQL;
    ADOQuery.Prepared := True;
end;

function TIADOQuery.Qry: TADOQuery;
begin
    Result := ADOQuery;
end;

function TIADOQuery.RecordCount: Integer;
begin
    Result := ADOQuery.RecordCount;
end;

procedure TIADOQuery.SelectParsedQuery;
begin
    ADOQuery.Active := False;
    ADOQuery.Active := True;
end;

procedure TIADOQuery.SelectQuery(SQL: String);
begin
    ADOQuery.Active := False;
    ADOQuery.ParamCheck := False;
    ADOQuery.SQL.Text := SQL;
    ADOQuery.Active := True;
end;

procedure TIADOQuery.SelectQueryEx(SQL: String; var Flag: Boolean);
begin
    try
    SelectQuery(SQL);
    except
        Flag := False;
        raise;
    end;
end;

procedure TIADOQuery.UpdateParsedQuery;
begin
    ADOQuery.Active := False;
    ADOQuery.ExecSQL;
end;

procedure TIADOQuery.SelectParsedQueryEx(var Flag: Boolean);
begin
    try
        SelectParsedQuery;
    except
        Flag := False;
        raise;
    end;
end;

procedure TIADOQuery.UpdateParsedQueryEx(var Flag : Boolean);
begin
    try
        UpdateParsedQuery;
    except
        Flag := False;
        raise;
    end;
end;

procedure TIADOQuery.UpdateQuery(SQL: string);
begin
    ADOQuery.Active := False;
    ADOQuery.ParamCheck := False;
    ADOQuery.SQL.Text := SQL;
    ADOQuery.ExecSQL;
end;

procedure TIADOQuery.UpdateQueryEx(SQL: string; var Flag: Boolean);
begin
 try
  UpdateQuery(SQL);
 except
  Flag := False;
  raise;
 end;
end;

procedure TIADOQuery.Parameter(ParameterName, Value: String);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftString;
end;

procedure TIADOQuery.Parameter(ParameterName: String; Value: Integer);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftInteger;
end;

procedure TIADOQuery.Parameter(ParameterName: String; Value: Boolean);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftBoolean;
end;

procedure TIADOQuery.Parameter(ParameterName: String; Value: Real);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftFloat;
end;

procedure TIADOQuery.Parameter(ParameterName: String; Value: TDate);
begin
 ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftDate;
end;


procedure TIADOQuery.Parameter(ParameterName, Value, CurrencySymbol: String);

var ConvertedValue : Real;
    Ps : Integer;

begin
 Ps := Pos(CurrencySymbol, Value);
 if Ps > 0 then
  Value := Copy(Value, 1, Ps - 1);
 ConvertedValue := StrToFloat(Value);
 ADOQuery.Parameters.ParamByName(ParameterName).Value := ConvertedValue;
 ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftFloat;
end;

procedure TIADOQuery.Parameter(ParameterName, Value: String; DateTimeType: TDateTimeType);
begin
 case DateTimeType of
  dtDate :
 end;
end;

function TIADOQuery.AsBoolean(FieldName: String): Boolean;
begin
    Result := ADOQuery.FieldByName(FieldName).AsBoolean;
end;

function TIADOQuery.AsDateTime(Fieldname: String): TDateTime;
begin
 Result := ADOQuery.FieldByName(FieldName).AsDateTime;
end;

function TIADOQuery.AsFloat(FieldName: String): Double;
begin
 Result := ADOQuery.FieldByName(FieldName).AsFloat;
end;

function TIADOQuery.AsInteger(FieldName: String): Integer;
begin
 Result := ADOQuery.FieldByName(FieldName).AsInteger;
end;

function TIADOQuery.AsString(FieldName: String): String;
begin
    Result := ADOQuery.FieldByName(FieldName).AsString;
end;

function TIADOQuery.AsStringQ(FieldName: String): String;
begin
    result := QuotedStr(AsStringT(FieldName));
end;

function TIADOQuery.AsStringT(FieldNumber: integer): String;
begin
    Result := trim(ADOQuery.Fields[FieldNumber].AsString);
end;

function TIADOQuery.AsStringT(FieldName: String): String;
begin
    result := trim(AsString(FieldName));
end;

procedure TIADOQuery.Edit;
begin
 Qry.Edit;
end;

function TIADOQuery.EOF: Boolean;
begin
 Result := Qry.Eof;
end;

function TIADOQuery.HasValue(Fieldname: String): boolean;
begin
    Result := not(IsNull(Fieldname));
end;

function TIADOQuery.IsNull: boolean;
begin
    Result := ADOQuery.Fields[0].IsNull;
end;

function TIADOQuery.IsNull(Fieldname: String): boolean;
begin
    Result := ADOQuery.FieldByName(FieldName).IsNull;
end;

procedure TIADOQuery.Next;
begin
 Qry.Next;
end;

procedure TIADOQuery.Post;
begin
 Qry.Post;
end;

procedure TIADOQuery.Previous;
begin
 Qry.Prior;
end;

end.

function TfrmCSRMain.GetAgreementNMAGRE(aAgreementNo: string): integer;
var
    lQry: IIADOQuery;
begin
    Result := 0;
    lQry := TIADOQuery.Create(ADOAccpacConn);
    lQry.SelectQuery('SELECT NMDOCID FROM VMDH WHERE TXDOCID='+QuotedStr(aAgreementNo)+' AND WDDOCTYPE='+IntToStr(VM_DHDocType_SiteAgr) );
    if lQry.HasValue('NMDOCID') then Result := lQry.AsInteger('NMDOCID');

end;

 

[djjd47130]

Wowzers, now that's a hell of a class. Thanks for sharing. Hope it works in D7, that's what I always use... I've moved away from Embarcadero Delphi 2010, they ruined it. Although there are many nice things, like the DB grids are fixed.. I just don't like the environment, it sucks up my memory by 10 times D7.

Anyway, I also posted an FAQ here:

http://tek-tips.com/faqs.cfm?fid=7420

It's a component I wrote to wrap a table and easily interact with it without using any SQL statements (on the outside). I simply assign a connection and wham bam thank you maam! That is the early stages of that component, I've done quite a bit since then, but it's been a while I'd have to go find it. I've been working with ASP.NET lately, haven't touched Delphi in about a month Anyway That's paying off with all I'm learning with web development. Knew nothing of it before this past November, now I'm building an AJAX messaging system

http://sports.jdsoftwareinc.com

JD Solutions

 

[whosrdaddy]

Djangman,

that was my class

one remark for both:

<security rant>
Please, please, please, use Parameters!!!!

I don't want to see code like that:

Q.SQL.Text:= 'select * from Users where Login = '''+ cUserName +'''';

lQry.SelectQuery('SELECT NMDOCID FROM VMDH WHERE TXDOCID='+QuotedStr(aAgreementNo)+' AND WDDOCTYPE='+IntToStr(VM_DHDocType_SiteAgr) );

please google SQL injection and again don't do this ever again...

</security rant>

Cheers,
Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[DjangMan]

Fortunately for me my application isn't web based and my front end manages validation before I get to the SQL. Otherwise I would use parameters. I'll probably convert the code to parameters at a later date, however. Thanks for the reminder...and the class!

 

[djjd47130]

By the way,

Last I checked, tags cannot have spaces in the tag names like <security rant> and </security rant> - You may consider using underscores (_) instead, like <security_rant> and </security_rant>. Did you mean to specify an attribute? In that case, it could look more like <rant type="security">(Some Rant Text)</rant>




JD Solutions