Link to home
Start Free TrialLog in
Avatar of Grant Fullen
Grant Fullen

asked on

Making My Own Procedure

I'm making a procedure that inserts records to a .mdb access database table.  It looks something like:

procedure AddInfo(sTable, sField, sValue: String);
var
  CustomerInfo: TAdoQuery;
begin
  CustomerInfo := TAdoQuery.create(nil);
  CustomerInfo.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=clean.mdb;Persist Security Info=False';
  CustomerInfo.Sql.Text := 'INSERT INTO ' + sTable + '(' + sField + ') VALUES (:s)';
  CustomerInfo.RecNo:= CustomerInfo.RecordCount
  ShowMessage(CustomerInfo.SQL.Text);
  CustomerInfo.Parameters.ParamByName('s').Value:= sValue;
  CustomerInfo.ExecSQL;
end;

This works fine but it only adds one value to one field per record.  I need to be able to have more than one parameter for the field and value.  I can't just say sValue1, sValue2, etc. : string because I won't know how many values I'm adding.
Avatar of TheRealLoki
TheRealLoki
Flag of New Zealand image

const
  expectedFieldDelimiter = #28; // FS 28 = Field Separator, so is a natural choice


procedure AddInfo(sTable: string; sFields, sValues: String);
var
  sFieldsSL, sValuesSL: TStringList;
  CustomerInfo: TAdoQuery;
  i: integer;
  sqltext: string;
begin
  sFieldsSL := TStringList.Create;
  sValuesSL := TStringList.Create;
  try
    sFieldsSL.Text := StringReplace(sFields, expectedFieldDelimiter, #13#10, [rfReplaceAll]);
    sValuesSL.Text := StringReplace(sValues, expectedFieldDelimiter, #13#10, [rfReplaceAll]);

    CustomerInfo := TAdoQuery.create(nil);
    CustomerInfo.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=clean.mdb;Persist Security Info=False';
    sqltext := 'INSERT INTO ' + sTable + '(';

    for i := 0 to pred(sFieldsSL.Count) do sqltext := sqltext + sFieldsSL[i] + ',';
    delete(sqltext, length(sqltext), 1); // remove final ','

    sqltext := sqltext + '') VALUES (';

    for i := 0 to pred(sFieldsSL.Count) do sqltext := sqltext + ':' + sFieldsSL[i] + ',';
    delete(sqltext, length(sqltext), 1); // remove final ','

    CustomerInfo.Sql.Text := sqltext;

    CustomerInfo.RecNo:= CustomerInfo.RecordCount
    ShowMessage(CustomerInfo.SQL.Text);

    for i := 0 to pred(sFieldsSL.Count) do CustomerInfo.Parameters.ParamByName(sFieldsSL[i]).Value := sValuesSL[i];

    CustomerInfo.ExecSQL;

  finally
    sFieldsSL.Free;
    sValuesSL.Free;
  end;
end;  

call it like
  AddInfo('Table1',
          'Field1' + #28 + 'Field2' + #28 + 'Field3',
          'Value1' + #28 + 'Value2' + #28 + 'Value3');

Avatar of Grant Fullen
Grant Fullen

ASKER

This is perfect.  Only other thing I would like is to be able to choose whether the value you're inserting is boolean, string, date, etc.

I'm upping the points to 500 if you could just do that.

Thanks.
If I changed sValues : string to sValues : Variant would that work?
ASKER CERTIFIED SOLUTION
Avatar of TheRealLoki
TheRealLoki
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
can you show me where you are getting the values from, because if it's from another table/query, there is a much better way of doing this, which will also work for NULLs btw
The values are coming from DBEdits.  You type in the information in the edits and then hit the "Insert Info" button, which is where I'm putting this code.  The new record should then be put in.
If they are DBEdits, then shouldn't this all be happening automatically?
Are you familiar with using DB Controls?
Works just fine.