How to insert current date to the table?

[sanjna000]

Hi,

I 've got a problem with adding current date to the table. I tried to add the current date along with other 2 fields exist in my table. They are ID (Number)and Desc(Alpha).

The code i used is as follows:

Desc := Edit2.Text;
CDate := DateToStr( Date );
qry.Close;
qry.SQL.Clear;
qry.SQL.Add( 'INSERT INTO Table1 (ID, Cdate, Desc)' );
qry.SQL.Add( ' VALUES (' + Edit1.Text + ',' + QuotedStr(CDate)+ QuotedStr(Desc) + ')' );
qry.ExecSQL;

This code gives an error. Can any one tell me what i have done wrong?

Many Thanks for u r help in advance,
Sanjna...

 

[towerbase]

Assuming that you are using the BDE you need to present the date as a string in 'mm/dd/yyyy' format.

Desc := Edit2.Text;
CDate := FormdatDateTime( 'mm/dd/yyyy', Date );
qry.Close;
qry.SQL.Clear;
qry.SQL.Add( 'INSERT INTO Table1 (ID, Cdate, Desc)' );
qry.SQL.Add( ' VALUES (' + Edit1.Text + ',' + QuotedStr(CDate)+ QuotedStr(Desc) + ')' );
qry.ExecSQL;

Andrew

 

[hilfy]

If the SQL is going to always be the same with just different values for the ID, Cdate, and Desc, you could also use parameters in your query. You would set the SQL at design time and then just update the parameters every time you needed to add new values. For example:

SQL:
INSERT INTO Table1 ( ID, Cdate, Desc )
VALUES( :ID, :Cdate, :Desc )

Show the TQuery (or whatever query component you're using...)in the Object Inspector, go to the Params property and click on the elipse button. For each parameter set the DataType (looks like ftString and ftDate from your code) and the ParamType (ptInput).

In your code:

qry.ParamByName('ID') := Edit1.Text;
qry.ParamByName('Cdate') := Date;
qry.ParamByName('Desc') := Edit2.Text;
qry.ExecSQL;

The advantage to this method comes when you're doing the same SQL statement multiple times - with parameters the query is only "prepared" once. When you change the actual SQL, the query is prepared every time it's executed.

-D

 

[LucieLastic]

...a small addition to hilfy's post, the field types.

qry.ParamByName('ID').AsInteger := Edit1.Text;
qry.ParamByName('Cdate').AsDateTime := Date;
qry.ParamByName('Desc').AsString := Edit2.Text;

lou

 

[sanjna000]

Hi Andrew,

I tried with u r code. It raised an exception with the message "Key Violation". I cannot understand why.

Sanjna.

 

[LucieLastic]

You must be trying to insert a record which already exists in the table with the same key.

lou

 

[towerbase]

"Key Violation" is raised when attempting to insert a record if a record with the same primary key already exists in the table.

You might find it worthwhile to check if the record already exists before attempting to insert into the table.


Andrew