Trouble getting to grips with a SQLite db

[enonod]

I cannot see what is wrong here can someone help please.
I had a db in DirInternal with a table with two fields. It was fine.
I needed a third field between the original two.
I uninstalled the program being debugged.
deleted the db using...

SQL1.ExecNonQuery("DROP TABLE IF EXISTS Items")

I then ran the program with a SELECT statement and got the expected error that the Table did not exist. I then ran with...

SQL1.ExecNonQuery("CREATE TABLE items (name TEXT, qty INTEGER, code INTEGER)")

and again with...

      SQL1.ExecNonQuery("INSERT INTO scores (name,score) VALUES ("& " '"&sName&"' " &",iQty " &",iCode)")

which are variables string and two integers.
The error says that the added field Qty does not exist and I am trying to insert 3 items into 2 fields.

 

[gregmartin64]

I cannot see what is wrong here can someone help please.
I had a db in DirInternal with a table with two fields. It was fine.
I needed a third field between the original two.
I uninstalled the program being debugged.
deleted the db using...

SQL1.ExecNonQuery("DROP TABLE IF EXISTS Items")

I then ran the program with a SELECT statement and got the expected error that the Table did not exist. I then ran with...

SQL1.ExecNonQuery("CREATE TABLE items (name TEXT, qty INTEGER, code INTEGER)")

and again with...

      SQL1.ExecNonQuery("INSERT INTO scores (name,score) VALUES ("& " '"&sName&"' " &",iQty " &",iCode)")

which are variables string and two integers.
The error says that the added field Qty does not exist and I am trying to insert 3 items into 2 fields.

That's because you are trying to insert three values into two fields!

In the following snippet you haven't included the qty field.

      SQL1.ExecNonQuery("INSERT INTO scores (name,score) VALUES ("& " '"&sName&"' " &",iQty " &",iCode)")

 

[mangojack]

enonod , You have Created a Table ' items ' with fields ' name , qty, code ' .... but then you try to Insert Into Table 'scores' , 3 values for only 2 fields .. ' name , score '

Try this .. (If your inserting data into all fields ,it is not necessary to include the field name in your insert statement.

SQL1.ExecNonQuery("CREATE TABLE items (name TEXT, qty INTEGER, code INTEGER)")

SQL1.ExecNonQuery("INSERT INTO items VALUES ('"& sName &"' ,'"& iQty &"' ,'"& iCode &"')")

I'm still learning SQL myself , maybe someone could comment on the syntax for the to last variables which are Integers, Loging the above statement shows the Values as 'enonod', '3', '12345' . Although the statement works,I was led to believe Integer values in SQL statements were not enclosed with single quotes , Or is the above statement OK


Edit ... enonod , I think this is the more accurate Insert statement ,the Integer variables do not need to be enclosed in single quotes as I thought.There is probably another way the insert could be done but for the purpose of the excersise, and based on your above attempts this one will do.

SQL1.ExecNonQuery("INSERT INTO items VALUES ('"& sName &"' ,"& iQty &" , "& iCode &" )")

Cheers mj

 

[Mahares]

@mj and enonod: The best way I found to avoid the quotes confusion is to use one of these 2 methods:
For 3 variables:

SQL1.ExecNonQuery2("INSERT INTO items VALUES ( ?,?,?)" , array as object(sName , qty ,code ))

or for the actual data:

SQL1.ExecNonQuery2("INSERT INTO items VALUES ( ?,?,?)" , array as string("enonod", "12345","3"))

merci beaucoup

 

[mangojack]

Mahares .. Thanks again. much cleaner approach. And also solved some other queries I had.

and @ enonod .. to follow on from Mahares examples , if you had previously declared an array , then ...

SQL1.ExecNonQuery2("INSERT INTO items VALUES ( ?,?,?)" , MyArray)

Cheers mj

 

[enonod]

Thank you for your valuable time and inputs.
Sorry, :sign0161: I accidentally posted an old half edited copy line from something else which didn't help you.

SQL1.ExecNonQuery("INSERT INTO scores (name,score) VALUES ("& " '"&sName&"' " &",iQty " &",iCode)")

The correct line I should have posted, which I assumed gave the error was...

SQL1.ExecNonQuery("INSERT INTO items (name,qty,code) VALUES ("& " '"&sname&"' " &",iqty " &",icode)")

@Mahares: I tried the array method which said too many parameters for some reason. So I dropped that before getting into a bigger mess. (Edit: Now it works I can afford to try again.) [EDIT] FINALLY, thanks, I did not spot the 2 on ExecNonQuery.
The final solution and thank you all for your input, was...

SQL1.ExecNonQuery("INSERT INTO items (name, qty, code) VALUES ('"& sname &"' ,'"& iqty &"' ,'"& icode &"')")

with or without the single quote on the INTEGER fields. I have it without.
So I presume something to do with the quotes or the database did not delete and recreate, leaving the old with two fields. I cannot know! I copied direct from mangojack (thank you) and the quotes seemed clearer than what I had.
Undying gratitude, despite my now being bald.