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.ConnectionStr ing := 'Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=clean.mdb;Persist Security Info=False';
CustomerInfo.Sql.Text := 'INSERT INTO ' + sTable + '(' + sField + ') VALUES (:s)';
CustomerInfo.RecNo:= CustomerInfo.RecordCount
ShowMessage(CustomerInfo.S QL.Text);
CustomerInfo.Parameters.Pa ramByName( '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.
procedure AddInfo(sTable, sField, sValue: String);
var
CustomerInfo: TAdoQuery;
begin
CustomerInfo := TAdoQuery.create(nil);
CustomerInfo.ConnectionStr
CustomerInfo.Sql.Text := 'INSERT INTO ' + sTable + '(' + sField + ') VALUES (:s)';
CustomerInfo.RecNo:= CustomerInfo.RecordCount
ShowMessage(CustomerInfo.S
CustomerInfo.Parameters.Pa
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.
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.
I'm upping the points to 500 if you could just do that.
Thanks.
ASKER
If I changed sValues : string to sValues : Variant would that work?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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?
Are you familiar with using DB Controls?
ASKER
Works just fine.
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.ConnectionStr
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.S
for i := 0 to pred(sFieldsSL.Count) do CustomerInfo.Parameters.Pa
CustomerInfo.ExecSQL;
finally
sFieldsSL.Free;
sValuesSL.Free;
end;
end;
call it like
AddInfo('Table1',
'Field1' + #28 + 'Field2' + #28 + 'Field3',
'Value1' + #28 + 'Value2' + #28 + 'Value3');