Insert statement with Ado 3

[wiredX]

Hello All,

Am new to delphi and I do hope u can all help with my problem.

I need to insert a record into a table based on a certain where clause. I have written the following code:

with dm.Dset do
begin
commandText := 'Insert into tblDeductions' +
'(deductid' +
',empid' +
',DeductType' +
',deductAmt)' +

'Values( ' + txtdeductID.Text + '' +
', ' + '''' + frmPayroll.txtEmpID.Text + '''' +
', ' + '''' + txtDescription.Text + '''' +
', ' + txtAllowanceAmt.Text + '' +

' where empid = ' + frmPayroll.txtEmpID.Text + ')' ;


connection.Execute(dm.Dset.CommandText);
end;

It comes with the ff error :Syntax error(missing operator) in query expression '400 where empid =001". Process stopped.

2. There are other fields to inserted but the error is only showing 400 and 001 from the other editboxes.

3. Where can I learn about paramterized queries. I tried reading it?

Thanks all.

wiredX

 

[Griffyn]

The first thing I'd fix would be what seems to be unbalanced quotes in your VALUE section. Maybe that's causing the error to occur. Put a breakpoint after you set your CommandText and have a look at it's value if you're having trouble getting it right.

You can't use a WHERE clause in an INSERT command. If you think about it, your empid field is unassigned at the time it's being asked to do the comparison. I can't work out under what critera you want to INSERT the record.

 

[kcostain]

Regarding Parameterized commands.. I went throught hat problem and talked about it in this post:

http://www.tek-tips.com/viewthread.cfm?SQID=793947&SPID=102&newpid=102&page=1

Hope it helps,
Kevin Costain

 

[petertickler]

Here is an example of a parameterised Insert query.

with dmRegistrations.quGeneral2 do begin
if active then close;
SQL.clear;
SQL.add('insert into contactName (Address_ID, LastName, CreationDate) ');
SQL.add(' values Address_ID, :LastName, :CreationDate) ');
ParamByName('Address_ID').asInteger := Address_ID;
if dmRegistrations.quRegistrations.FieldByName('regNo').asInteger > 9000000 then
ParamByName('LastName').asString := 'Purchaser'
else
ParamByName('LastName').asString := 'Producer';
ParamByName('CreationDate').asDateTime := Now();
execSQL;
end;

If you follow this approach, you shouldn't have any problems. Note that, as Griffyn said, you can't have a 'WHERE' in an 'INSERT' statement.

 

[AP81]

Try changing it to:

'Values( ' + txtdeductID.Text + '' +
', ' + '''' + frmPayroll.txtEmpID.Text + '''' +
', ' + '''' + txtDescription.Text + '''' +
', ' + txtAllowanceAmt.Text + '' + ') ' +

' where empid = ' + frmPayroll.txtEmpID.Text;






------------------------------------
There's no place like 127.0.0.1
------------------------------------

 

[wiredX]

Thank you all for your help. I have been able to get the code to run. I had to look at the code again and realised that:

1. At the point I was using the where clause,ie Where empid = ' + frmPayroll.txtEmpID.Text + ' , there is no value for the empid in the db...so was no need for the where expression.

petertickler Thanks for your example. Do I have to declare the paramvalues before I use them? If yes, Where pls.

On line six, you assigned the field name to the parameter value or am missing it? ParamByName('Address_ID').asInteger := Address_ID

Pls. do you know any resources on the web where I can read some stuffs on parameterised query? The Borland Online help on that topic has not been of much help!

Thanks again all.

 

[indrahig]

Hi, I'm trying to give you suggestion from all knowledge I know. Here is a simple example to insert into a table using parameterised ADOQuery. You must use TADOQuery component. Let's assume the component named quAct
with quAct do
begin
Add(' Insert into tblDeductions ( '+
' deductid, '+
' empid, '+
' DeductType, '+
' deductAmt) '+
'Valuesdeductid, '+
' :empid, '+
' eductType, '+
' :deductAmt) ');
with Parameters do
begin
ParamByName('deductid').Value:= txtdeductID.Text;
ParamByName('empid').Value:= frmPayroll.txtEmpID.Text;
ParamByName('DeductType').Value:= txtDescription.Text;
ParamByName('deductAmt').Value:= txtAllowanceAmt.Text;
end;
ExecSQL;
end;

Petertickler example is the same code if using BDE Database Component. Hope it's help.

 

[wiredX]

indrahig
Thanks so much. I am getting it now. Your codes explains almost everything.