SQL - insert logic error

Hi, i'm designing a shopping cart system for a freind from a tutorial in a book. Only problem is that the tutorial doesn't include a option for the quantity of a single item in the cart. So i got the whole thing running and then thought i'd add the quantity field in the table cart in the database called tblQuantity. So i did this and now i get this error when i try to add to the cart:

----------

ADODB.Connection.1 error '80004005'

SQLState: S1000
Native Error Code: 1136
[TCX][MyODBC]Column count doesn't match value count at row 1


/store/specials/TMPgdfpqhggkb.asp, line 116

-------

the code for the insert record is below

strUsername = Session("MM_Username")
MM_editQuery = "insert into " & MM_editTable & " values (''," & MM_dbValues & ",'" & strUsername & "','')"

---------

Im sure that it's somthing to do with the insert code not specifing all the fields that need to be updated because when i delete the field in the database i added for the quantity the add to cart works again. i don't know any asp so i havn't a clue what to change so any help would be much appreciated.

Cheers.

It's not an ASP issue, but a SQL issue. Unless you specify a field list before "VALUES" in your query, the order and data types of the values you're inserting must match the order and data types of the fields in the database. Maybe you're not building MM_dbValues correctly. For example, if you added the quantity field to the end of your table, but it's being passed as part of MM_dbValues, you'll get an error because you're inserting other values after inserting MM_dbValues, and your insert order doesn't match your actual field order. This can be solved either by sending a properly ordered field list in parentheses before "VALUES."

Alright Mate Cheers for the Reply.

Could you give me an example of this as i am not very good with SQL :

" This can be solved either by sending a properly ordered field list in parentheses before "VALUES." "


This -

strUsername = Session("MM_Username")
MM_editQuery = "insert into " & MM_editTable & " values (''," & MM_dbValues & ",'" & strUsername & "','')"

works for inserting a record into this SQL table -

cartID int not null auto_increment,
prodID varchar(50),
username varchar(50),
orderID varchar(50),
primary key (cartID)
)

all i want to do is add a field "prodQuantity" to the table. I don't want it to instert anything into at this stage. Just for it to be part of the table so i can use it later on.

Thanks for the help.

You need to take dhouston's advice. Essentially, what the error message is saying is that the number of fields you have in the FIELDS section of the SQL statement outnumber the VALUES section.

e.g. INSERT INTO foo (field1, field2, field3) VALUES (value, value2)

This is incorrect. They need to match up.

90% of all programming problems can be solved if you simply print out the value of the variable that you're having problems with. In this case, print out your SQL string BEFORE executing it. This will allow you to see what's going wrong.

To extend stumpy's example a little, let's pretend you've got a three-column table. The columns are named "one," "two," and "three." Your initial query is:



Now you want to add a column named "four" to your table. Your original query will break because the default behavior of a query without a field list preceding VALUES is to compare your VALUES list to the full table schema and to bomb if the field types or the number of fields don't match the full table schema. The solution is one of the following:



or



See that if you don't give a column list and the items you're inserting don't match up to what's in the table, you need to put placeholders in the spots you don't have values for. So I've got empty quotes as the fourth value in the second revised query above.

For code re-ability, it's always advisable to use the full INSERT notation. i.e.
It'll make things easier on you, and anyone else who might need to work on your code at a later time.