SQLite, ZEOS 6.6.6, fields type
lazarus / 16-Databases

----------------------------------------------------------------------
maleficmax · 2011-02-02 14:40

Hi! I'm having some problems inserting data from the form to the
database.
Here is my table:
Code: [Select]"id" INTEGER PRIMARY KEY  NOT NULL ,
"ord_date" DATETIME NOT NULL  DEFAULT (CURRENT_DATE),
"client" INTEGER NOT NULL,
"work" INTEGER,
"maket" INTEGER,
"count" INTEGER NOT NULL,
"paper_format" INTEGER,
"print_type" INTEGER,
"paper_dens" INTEGER,
"colors" VARCHAR,
"price" DOUBLE NOT NULL ,
"prepayment" DOUBLE,
"payed" BOOL,
"comments" VARCHAR,
"ord_expire" DATETIME,
...
Here is my form (with some comments):
(http://habreffect.ru/files/387/9c2432e2e/Безымянный.png)

Please help me to insert:
- DBLookupCombobox keyfield value to "client" INTEGER
- DateEdit value to "ord_expire" DATETIME
- Current date value to another DATETIME
- Memo value to "comments" VARCHAR

What i do now is:
Code: [Select] DataModule1.ZMakAdd.SQL.Text:='INSERT INTO orders
(client, ord_date, work, maket, count, price, prepayment, comments,
ord_expire) VALUES ("'+DBLookupComboBox1.KeyValue+'","'+Date+'",4,"'+D
BLookupComboBox2.KeyValue+'","'+Edit2.Text+'","'+Edit1.Text+'",0,"'+Me
mo1.Lines.Text+'","'+DateEdit1.Date+'")';

----------------------------------------------------------------------
jixian.yang · 2011-02-02 15:15

Try to set DateEdit format to ___-__-__.

----------------------------------------------------------------------
maleficmax · 2011-02-02 16:02

Format is the same now.
And here is error:
(http://habreffect.ru/files/f29/b877cc71f/Безымянный2.png)
And dblookup.ini is opening with the cursor on the first of these
lines:
Code: [Select]  if FListField.AsString=AListFieldValue then
  begin
    result := TField(FKeyFields[0]).Value;
    exit;
  end;

----------------------------------------------------------------------
maleficmax · 2011-02-03 14:05

Trying another way:
I've added ZTable component and
Code: [Select]ZTable1.Open;
ZTable1.Insert;
ZTable1.FieldByName('client').Value:=DBLookupComboBox1.KeyValue;
ZTable1.FieldByName('ord_date').Value:=FormatDateTime('YYYY-MM-
DD',Date);
ZTable1.FieldByName('work').Value:=4;
ZTable1.FieldByName('maket').Value:=DBLookupComboBox2.KeyValue;
ZTable1.FieldByName('count').Text:=Edit2.Text;
ZTable1.FieldByName('price').Text:=Edit1.Text;
ZTable1.FieldByName('prepayment').Value:=0;
ZTable1.FieldByName('comments').Text:=Memo1.Lines.Text;
ZTable1.FieldByName('ord_expire').Value:=FormatDateTime('YYYY-MM-
DD',DateEdit1.Date);
ZTable1.Post;And this is what i have:
Project bla.exe raised exception class 'EConvertError' with message:
"2011-02-03" is not a valid date format
i press Continue and
Project bla.exe raised exception class 'EVariantError' with message:
Invalid variant type cast
Then
Invalid value for field 'ord_date'

----------------------------------------------------------------------
garlar27 · 2011-02-03 16:05

SQLite and DateTime ...

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i // I don't
do it this
way...//ZTable1.FieldByName('ord_date').Value:=FormatDateTime('YYYY-
MM-DD',Date); // This has worked fine for
me:ZTable1.FieldByName('ord_date').AsDateTime := Date; 
By the way:
Have you read this? Datatypes In SQLite Version 3

when setting DateTime fields from an SQL INSERT, UPDATE clause a
typecast is needed.

----------------------------------------------------------------------
maleficmax · 2011-02-04 09:48

Thank you! That worked for me!

----------------------------------------------------------------------
maleficmax · 2011-02-04 10:00

But now i have some new problems.
One of them is that DBGrid shows values of DOUBLE (REAL?) type as
"2E2" instead of "200", "5.2E2" instead of "517"

----------------------------------------------------------------------
maleficmax · 2011-02-04 12:15

Ok, DisplayFormat of that column had to be "0.00"

----------------------------------------------------------------------
garlar27 · 2011-02-04 13:34

Remember that you can set TFloatField.Currency to True if that fields
stores prices, costs etc. and will format it with your Currency format
configured in your system.

May be it would be better not to use variants when assigning values to
the fields. Variants are slow and have a big overhead.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 ZTable1.FieldByName('prepayment').AsCurrency :=
StrToCurrency(Edit1.Text);//ZTable1.FieldByName('prepayment').Value :=
Edit1.Text; 
By the way, don't forget the particularities of SQLite's data types.
According to what their wiki says, you can store any thing (as a
string) in any field regardless of the type or size you defined to
that specific field for instance you can store the text "Molly was
cutting daisies in the garden" in an Integer field.

----------------------------------------------------------------------
maleficmax · 2011-02-07 14:17

Thank you! I like that! No problems left for now, but they are coming,
i can hear that...