Passing values to database from C# application form

Question

A trying to insert a couple of values received from textboxes into a table in access database. I have perfectly written my statement without passing value for the primary key in the table since its datatype is set to auto number.

Upon running the program. i click the button that runs the insert statement and i recieve the following error "NUMBER OF QUERY VALUES AND DESTINATION FIELDS ARE NOT THE SAME"

How can i fix this...?

please let me know if any further information is required.... Thanks...

Answer 1

Have you listed the columns you're inserting into?

INSERT INTO table (col1, col2) VALUES (val1, val2)

You must do this if you are not inserting into every column, and should do it anyway as it's best practice.

Answer 2

You should post your query with a question like this, but i think i know what your problem is.

Since you're not using the primary key column and therefore inserting into only a subset of the fields, you need to list the fields you will be using like this:

INSERT INTO SomeTable (col1,col2,col3) VALUES ('val1','val2',3)

You were probably trying to do something like this:

INSERT INTO SomeTable VALUES ('val1','val2',3)

Which will not work because your primary key field is auto numbered.

Try this:

sqlQuery = 
"INSERT INTO Youth (" + 
"NumbersOfSport, " + 
"YouthID, " + 
"Price, " + 
"TotalCostOfTraining, " + 
"PercentageDiscount, " + 
"AmountDue," + 
"DatePurchased" + 
") VALUES (" + 
toSql(qtyInt) + ", " + 
toSql(youthInt) + ", " + 
toSql(priceStr) + ", " + 
toSql(totalCstStr) + ", " + 
toSql(discountStr) + ", " + 
toSql(amtDueStr) + ", " + 
toSql(Convert.ToDateTime(purDate)) + ")"

You were missing a comma here: "AmountDue" + should be: "AmountDue," +

VB format:

sqlQuery = "INSERT INTO Youth (" + "NumbersOfSport, " + "YouthID, " + "Price, " + "TotalCostOfTraining, " + "PercentageDiscount, " + "AmountDue" + "DatePurchased" + ") VALUES (" + toSql(qtyInt) + ", " + toSql(youthInt) + ", " + toSql(priceStr) + ", " + toSql(totalCstStr) + ", " + toSql(discountStr) + ", " + toSql(amtDueStr) + ", " + toSql(Convert.ToDateTime(purDate)) + ")"