SQLite INSERT command

phaworth · Post by **phaworth** » Fri Jul 17, 2009 11:22 pm

I'm using revExecuteSQL to issue an SQL INSERT command to add a new entry to a table in an SQLite database. the command is:

revExecuteSQL gDBID, tSQL

gDBID contains 1
tSQL contains:
INSERT INTO Products (ProdName,Active,Revenue,Track,Ship,SalesCategory,ProdPrice,ChargeExpenses,ProdType) VALUES ("New CD",1,1,1,0,"Anril","15",0,"CD")

I display the value of "it" after the revExecuteSQL is executed and it is blank.

The new record is not added to the table. The above SQL does not include the field that is the primary key for the table but I've tried including that with a value of NULL and same result.

Hoping there is an SQL expert out there can tell me what I'm doing wrong!

Thanks,
Pete

Mark · Post by **Mark** » Sat Jul 18, 2009 1:16 am

Pete,

You can't use NULL or empty for primary keys. You must provide a unique value. It is not necessary to define a primary key in a table. It might be easer to do without.

A great way to find the cause of problems is to execute your syntax in a DOS console or unix terminal window. If you use the sqlite comand line utility to execute yor SQLite code, you may get an error returned that is mre specific about the mistake you made. Once you have corrected the problem, you can go back to Revolution.

Best,

Mark

phaworth · Post by **phaworth** » Sat Jul 18, 2009 1:21 am

Thanks Mark, that's a great tip to use the sqlite command in a terminal window.

I'm confused about the primary key thing though. The primary key is set up as an autoincrement field in sqlite, so why would I need to supply a value?

Pete

Mark · Post by **Mark** » Sat Jul 18, 2009 1:28 am

Pete,

I'm not sure, but I wouldn't be suprised if autoincrement just doesn't work with the version of SQLite included with Revolution or if there is another reason why it doesn't work. I gave up on that and just wrote a simple script to set the value of an ID field to MAX(sqlite field name)+1. It should be possible to do all that in one line of SQLite syntax.

Best,

Mark

phaworth · Post by **phaworth** » Sat Jul 18, 2009 1:54 am

Thanks Mark, I think I have everything working again now. I didn't specify the name of the primary key in the fieldlist I gave to INSERT or a value in the VALUES list and that seemed to work just fine with the autoincrement taking care of the primary key value.

I also got bitten by the double quotes vs single quotes problem around tect filed in the value list.

Thanks for the suggestions,

Pete