SQL Help Please

[Illuma]

Ok, Don't shout I know I am going blind the answer is out there I just cant find it! I am totally new to SQL database stuff and I am attempting to put data in the database from a variable, however I cant seem to work it out.

This is the line I am using:-

_SQLite_Exec (-1, "INSERT INTO BarcodeInfo (ScanTime,ScanDate,Barcode,MachineNumber,Points) VALUES ( $Time,'2','05500103641113','055','123');")

From this if I was to replace $Time with '1' all the data gets put in the database, what I am now trying to do is put actual data in not test data so if $Time = 15:30 how do I put that in?? It's driving me nuts!

Thanks in advance.

Mark

[PsaltyDS]

You have to make your variable reference outside the quoting:

_SQLite_Exec (-1, "INSERT INTO BarcodeInfo (ScanTime,ScanDate,Barcode,MachineNumber,Points) VALUES (" & $Time & ",'2','05500103641113','055','123');")

If the value requires literal single quotes around it, then:

_SQLite_Exec (-1, "INSERT INTO BarcodeInfo (ScanTime,ScanDate,Barcode,MachineNumber,Points) VALUES ('" & $Time & "','2','05500103641113','055','123');")

[Illuma]

Thanks PsaltyDS, I can't believe I missed the second &, I tried " $Time ", arghhh.

Regards

Mark

[jchd]

As a reminder, when a text column (or a text value, since SQLite doesn't actually strictly enforce column types) _may_ even remotely contain one or more single quote, then you must use _SQLite_Escape($text) instead of $text alone. This way, you're safe as any single quote within text will be transparently escaped. I personally always use an equivalent function to wrap any text value in any table.

[Illuma]

As a reminder, when a text column (or a text value, since SQLite doesn't actually strictly enforce column types) _may_ even remotely contain one or more single quote, then you must use _SQLite_Escape($text) instead of $text alone. This way, you're safe as any single quote within text will be transparently escaped. I personally always use an equivalent function to wrap any text value in any table.

Way over my head! Thanks for the info though.

[jchd]

That's simple: SQL expect a litteral text value to be surrounded by quotes. E.g. "insert into mytable (a,b,c) values (1, 'Illuma', '2010-11-17')". If your text contains one or more quote, you need to escape each of them by doubling then. E.g. "insert into mytable (a,b,c) values (1, 'Illuma: it''s very simple', '2010-11-17')" here it's needs to be passed a it''s (two single quotes). That's exactly what _SQLite_Escape does for you. Omitting this you end up passing ... values (1, 'Illuma: it's very simple', '2010-11-17') and the part in bold causes an error in an otherwise working program because the quote encountered is parsed as the closing quote of the litteral text, while it really is part of the text.