11 replies to this topic

[2710]

Hi,

I have a field in my database that is 'Currency'. Now in delphi I have this code:

Qryupdatestock.SQL.Clear;

Qryupdatestock.SQL.Add('Update Z1Read');

Qryupdatestock.SQL.Add('Set '+ Category + ' = '+Category+ '+ ' +FloattoStr(Price));

Qryupdatestock.SQL.Add('Where Dates = '+DatetoStr(Date));

Qryupdatestock.execsql;

And example of FloattoStr(Price) = 2.4 for example

Now, I try this, but it doesn't work. I want it to 'add' onto what is already there. It runs fine, but it doesn't update the database. I am assuming that the format is entered incorrectly

Any help appreciated

[WingedPanther]

What database type is this and table structure?

[2710]

Um...what do you mean by database type? I am using Microsoft Access if it answers your question :P, and table structure.... I'm not sure, I have got a picture though:



Thanks

[2710]

i have done some testing and I have realised that it is not the currency which is the problem, it is the 'Dates'

Qryupdatestock.SQL.Clear;
Qryupdatestock.SQL.Add('Update Z1Read');
Qryupdatestock.SQL.Add('Set '+ Category + ' = '+Category+ '+ ' +FloattoStr(Price));
Qryupdatestock.SQL.Add('Where Dates = '+DatetoStr(Date));
Qryupdatestock.execsql;

If I get rid of the 'Where Dates = '+DatetoStr(Date) , it works, ie it updates the whole Books column. If I replace the Where Dates with Where Something_Else, it also works. So basically, my Date Format is wrong.... how comes?

Thanks

PS: Right, I think I have got a clue for you guys to help solve this issue:



Basically, it doesn't actually add what you type into SQL, it adds some weird time format. Thank you! The end is in sight :D

[WingedPanther]

Try wrapping your date value in single quotes.

[2710]

UPDATE Z1Read
Set Books = 10
Where Dates = '21/03/2010'

If I have that, it doesn't work, it gives me a 'Data Mismatch' error.

Whereas if I had this:

UPDATE Z1Read
Set Dates = '21/03/2010'
Where Books = 10

It does work. Any ideas? The thing is, I need the first one.

[WingedPanther]

It looks like you need to use # to delimit your date strings. Access Tips: Query and Filter Criteria

[2710]

You are a legend :D THANKS!! :D

[WingedPanther]

Nah, I'm just good at Googling.

[2710]

WHY? It works, but now its doing something stupid, Look:

procedure TForm1.Button1Click(Sender: TObject);
begin
with AdoQuery1 do
begin
SQL.Clear;
SQL.Add('Insert into Table1');
Sql.Add('(Dates)');
Sql.Add('Values') ;
sql.Add('(#' + DatetoStr(Date) + '#);' );
ExecSql;
Showmessage(DatetoStr(Date));
end;
end;

This is just one form with one button which executes this code



But look what it inserts!



It inserts 4/3/2010 rather than 3/4/2010
It's either doing it in an american format, or its just wrong....

I havent touched my program.....

Any help?

[WingedPanther]

Welcome to the evils of regional settings. You may have to call formatdatetime to get it into Access correctly.

[2710]

Thank you it works now :D