Board index » delphi » Help - Problems with Query _AND_ insert

Help - Problems with Query _AND_ insert

I'm having a bad time trying to figure out the Delphi
DB stuff.

Given the following snippet:

with frmJobs.JBQuery do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select max(JobJNumber) from createhs where JobJDate = :MyJobdate');
    Prepare;
    Params[0].AsInteger := MyJobdate;
    Open;
  end;   {Query}

And given that I have a TDBEdit box, how do I get the returned
value from an aggregate function (max) ?  I really would like to
put this in a variable.... Doable?

Also, problem # 2:

given the following snippet:

with frmJobs.JBQuery do
  begin

    Close;
    SQL.Clear;
    SQL.Add('insert into createhs (JobJDate, JobJNumber) values ( :createjdate,
:createjnumber)');
    Prepare;
    Params[0].AsInteger := MyJobdate;
    Params[1].AsInteger := tmpJnum;
    ExecSQL;

This does not insert to the table (simple table, two fields).  Any glaring
problems. I really gettin' kicked here...  Any help appreciated...

-Darrel

--
________________________________________________________
Darrel L. Davis    Senior P/A    ThunderWave, Inc.
darr...@access.digex.net         dda...@twave.com

 

Re:Help - Problems with Query _AND_ insert


Quote
Darrel L. Davis wrote:

> I'm having a bad time trying to figure out the Delphi
> DB stuff.

> Given the following snippet:

> with frmJobs.JBQuery do
>   begin
>     Close;
>     SQL.Clear;
>     SQL.Add('select max(JobJNumber) from createhs where JobJDate = :MyJobdate');
>     Prepare;
>     Params[0].AsInteger := MyJobdate;
>     Open;
>   end;   {Query}

> And given that I have a TDBEdit box, how do I get the returned
> value from an aggregate function (max) ?  I really would like to
> put this in a variable.... Doable?

First, set the max(JobJNumber) to some known field name such as:

SQL.Add('select MaxJobN = max(JobJNumber) from createhs where JobJDate = :MyJobdate');

Then, use FieldByName('MaxJobN').Value to retrieve the value of the field.

- Show quoted text -

Quote
> Also, problem # 2:

> given the following snippet:

> with frmJobs.JBQuery do
>   begin

>     Close;
>     SQL.Clear;
>     SQL.Add('insert into createhs (JobJDate, JobJNumber) values ( :createjdate,
> :createjnumber)');
>     Prepare;
>     Params[0].AsInteger := MyJobdate;
>     Params[1].AsInteger := tmpJnum;
>     ExecSQL;

> This does not insert to the table (simple table, two fields).  Any glaring
> problems. I really gettin' kicked here...  Any help appreciated...

> -Darrel

> --
> ________________________________________________________
> Darrel L. Davis    Senior P/A    ThunderWave, Inc.
> darr...@access.digex.net         dda...@twave.com

I'm not sure why the second snippet doesn't work.  
Have you tried ParamByName('createjdate').AsInteger and ParamByName('createjnumber').AsInteger?  I always use
these because it makes the code much easier to read, especially if the SQL is defined at design time through
the object inspector and it's not obvious which parameters [0] and [1] refer to.

Re:Help - Problems with Query _AND_ insert


"Darrel L. Davis" <darr...@access.digex.net> wrote:

Quote
>Given the following snippet:
>with frmJobs.JBQuery do
>  begin
>    Close;
>    SQL.Clear;
>    SQL.Add('select max(JobJNumber) from createhs where JobJDate = :MyJobdate');
>    Prepare;
>    Params[0].AsInteger := MyJobdate;
>    Open;
>  end;   {Query}
>And given that I have a TDBEdit box, how do I get the returned
>value from an aggregate function (max) ?  I really would like to
>put this in a variable.... Doable?

Most RDBMSs support some sort of column aliasing.  You should be able
to do 'select max(JobJNumber) MaxJNumber from ....' and reference the
column name 'MaxJNumber' in your code.  You can assign the TDBEdit's
Datafield property to 'MaxJNumber'.

Quote
>Also, problem # 2:
>given the following snippet:
>with frmJobs.JBQuery do
>  begin
>    Close;
>    SQL.Clear;
>    SQL.Add('insert into createhs (JobJDate, JobJNumber) values ( :createjdate,
>:createjnumber)');
>    Prepare;
>    Params[0].AsInteger := MyJobdate;
>    Params[1].AsInteger := tmpJnum;
>    ExecSQL;
>This does not insert to the table (simple table, two fields).  Any glaring
>problems. I really gettin' kicked here...  Any help appreciated...

It looks ok.  Are you getting any exceptions? or does it fail quietly?
How are you checking for success?  In some environments, you will need
to commit the insert before the data appears to other user
connections.

Other Threads