Board index » delphi » ADO Insert Problem

ADO Insert Problem

Hi

Im having a stange problem!!

Im using a ADO application to insert a row into a SQL-Server table

Provider is SQLOLEDB

Delphi code gos like this

  qry_SQL.Close;
  qry_SQL.SQL.Clear;
  qry_SQL.SQL.Add('INSERT INTO Box (boxnumber, code, department, status,
briefdescription,'
                 +'destructiondate, origindate, numfiles, ISource,
BoxDescription, Text1, '
                 +'Text2, Text3, Text4, Text5, Date1, Date2, Num1, Num2)');
  qry_SQL.SQL.Add('VALUES(:boxnumber, :code, :department, :status,
:briefdescription,'
                 +':destructiondate, :origindate, :numfiles, :ISource,
:BoxDescription, '
                 +':Text1, :Text2, :Text3, :Text4, :Text5, :Date1, :Date2,
:Num1, :Num2)');
//============Set Params==============
  qry_SQL.Parameters.ParamValues['boxnumber'] := '1000199B';
  qry_SQL.Parameters.ParamValues['code'] := '9565200';
  qry_SQL.Parameters.ParamValues['department'] := '01';
  qry_SQL.Parameters.ParamValues['status'] := -1;   //SQL updates status
  qry_SQL.Parameters.ParamValues['briefdescription'] := 'GENERAL';
  qry_SQL.Parameters.ParamValues['destructiondate'] :=
StrtoDate('01/01/2001');
  qry_SQL.Parameters.ParamValues['origindate'] := StrtoDate('01/01/2001');
  qry_SQL.Parameters.ParamValues['numfiles'] := Null;
  qry_SQL.Parameters.ParamValues['ISource'] := 4;
  qry_SQL.Parameters.ParamValues['BoxDescription'] := 'TRY_CONS_LTD_200_01';
  qry_SQL.Parameters.ParamValues['Text1'] := 'TEST';
  qry_SQL.Parameters.ParamValues['Text2'] := 'TEST';
  qry_SQL.Parameters.ParamValues['Text3'] := Null;
  qry_SQL.Parameters.ParamValues['Text4'] := Null;
  qry_SQL.Parameters.ParamValues['Text5'] := Null;
  qry_SQL.Parameters.ParamValues['Date1'] := Null;
  qry_SQL.Parameters.ParamValues['Date2'] := Null;
  qry_SQL.Parameters.ParamValues['Num1'] := Null;
  qry_SQL.Parameters.ParamValues['Num2'] := Null;
  qry_SQL.ExecSQL;

I get no response at all and nothing is inserted!!

Can anyone tell me why nothing is inserted?? Surely I should get an error or
something??

Regards
Chris

 

Re:ADO Insert Problem


Well, when using SQL Server for example try copying the result query into
the Query Analyser..
If it inserts into the DB, then you know it is not the query which is te
problem, but the code..

That;s something to start with

Greetz,
Arnoud

Quote
"Christopher Viljoen" <c...@skysoft.co.za> wrote in message

news:3c8f5f2c_1@dnews...
Quote
> Hi

> Im having a stange problem!!

> Im using a ADO application to insert a row into a SQL-Server table

> Provider is SQLOLEDB

> Delphi code gos like this

>   qry_SQL.Close;
>   qry_SQL.SQL.Clear;
>   qry_SQL.SQL.Add('INSERT INTO Box (boxnumber, code, department, status,
> briefdescription,'
>                  +'destructiondate, origindate, numfiles, ISource,
> BoxDescription, Text1, '
>                  +'Text2, Text3, Text4, Text5, Date1, Date2, Num1,
Num2)');
>   qry_SQL.SQL.Add('VALUES(:boxnumber, :code, :department, :status,
> :briefdescription,'
>                  +':destructiondate, :origindate, :numfiles, :ISource,
> :BoxDescription, '
>                  +':Text1, :Text2, :Text3, :Text4, :Text5, :Date1, :Date2,
> :Num1, :Num2)');
> //============Set Params==============
>   qry_SQL.Parameters.ParamValues['boxnumber'] := '1000199B';
>   qry_SQL.Parameters.ParamValues['code'] := '9565200';
>   qry_SQL.Parameters.ParamValues['department'] := '01';
>   qry_SQL.Parameters.ParamValues['status'] := -1;   //SQL updates status
>   qry_SQL.Parameters.ParamValues['briefdescription'] := 'GENERAL';
>   qry_SQL.Parameters.ParamValues['destructiondate'] :=
> StrtoDate('01/01/2001');
>   qry_SQL.Parameters.ParamValues['origindate'] := StrtoDate('01/01/2001');
>   qry_SQL.Parameters.ParamValues['numfiles'] := Null;
>   qry_SQL.Parameters.ParamValues['ISource'] := 4;
>   qry_SQL.Parameters.ParamValues['BoxDescription'] :=

'TRY_CONS_LTD_200_01';

- Show quoted text -

Quote
>   qry_SQL.Parameters.ParamValues['Text1'] := 'TEST';
>   qry_SQL.Parameters.ParamValues['Text2'] := 'TEST';
>   qry_SQL.Parameters.ParamValues['Text3'] := Null;
>   qry_SQL.Parameters.ParamValues['Text4'] := Null;
>   qry_SQL.Parameters.ParamValues['Text5'] := Null;
>   qry_SQL.Parameters.ParamValues['Date1'] := Null;
>   qry_SQL.Parameters.ParamValues['Date2'] := Null;
>   qry_SQL.Parameters.ParamValues['Num1'] := Null;
>   qry_SQL.Parameters.ParamValues['Num2'] := Null;
>   qry_SQL.ExecSQL;

> I get no response at all and nothing is inserted!!

> Can anyone tell me why nothing is inserted?? Surely I should get an error
or
> something??

> Regards
> Chris

Re:ADO Insert Problem


On Wed, 13 Mar 2002 16:25:02 +0200, "Christopher Viljoen"

Quote
<c...@skysoft.co.za> wrote:
>Im using a ADO application to insert a row into a SQL-Server table
>Provider is SQLOLEDB

What if you try this:

try
  qry_SQL.ExecSQL;

except
  on E: EOleSysError do begin
    ShowMessageFmt('$%x - %s', [E.ErrorCode, E.Message]);
  end;
end;

Do you get an exception? Does is say something like "Constraint
violation" or "Field cannot be empty" or something like that ?

Marc

------------------------------------------------------------------------
Marc Scheuner                                          Software Engineer
Quest Software Canada                       Halifax, Nova Scotia, Canada
Email: marc.scheu...@quest.com                      http://www.quest.com

Other Threads