ASP | help with (simple) query

[burns]

Hi,

I have a form that user can enter text which is then added to a DB table.

From the submited form, a value(found) is inserted into a table using the following query:

SQL = "INSERT INTO tblFound Values (' "& found &" ')"

However, I keep getting an error.
-----------
Microsoft JET Database Engine error '80004005'

Number of query values and destination fields are not the same.
---------------
I think that it is because the form has 2 fields:

ID autonumber (pk)
Found text

I think the query needs to "skip" the ID autonumber and just insert the found value but the the DB needs to assign a number to the new value.

Any suggestions?

Many Thanks

[antizip]

if you're not going to specify the column you're inserting into, then you need to insert all values for this entry.

Example:


SQL = "INSERT INTO tblFound (foundField) VALUES ('" & found & "')"


Other wise the way you're doing it ...



SQL = "INSERT INTO tblFound VALUES ('"& id & "','" & found & "','" & ... & ')"

where ... are the rest of the columns in that table

[burns]

thanks for the reply, however i get a:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/insert_found.asp, line 35

error, I have changed the query to :

SQL = "INSERT INTO tblFound (Found) VALUES ('" & found & "')"

I only want to add the one found value to the DB although it has two fields; ID & Found?

Thanks once again

[antizip]

If your ID is a primarykey then it can't be left empty. Is the ID type Autonumber/IDENTITY?

Another thing you can try is to not run the sql and just output it to the screen to make sure you're syntax is well formed.

[burns]

If your ID is a primarykey then it can't be left empty. Is the ID type Autonumber/IDENTITY?

Another thing you can try is to not run the sql and just output it to the screen to make sure you're syntax is well formed.

Thanks for the reply,

no its not a primary key and it is an Autonumber.

How do i output the sql to screen?

many thanks

[antizip]

first comment out your
db.execute( ... )
and anything else that uses the database.

then simply Response.Write(SQL) and this will print your sql statement on the screen you can then look to make sure your sql statement is well formed.

Something else I thought of ... is the found variable a string or a number. If it is a number, it doesn't need the single quotes around it so your SQL would look like this:


SQL = "INSERT INTO tblFound (Found) VALUES (" & found & ")"

[burns]

Hi, thanks for the reply,

here is what the query returned:

INSERT INTO tblFound (Found) VALUES ('google')

does that look right?

btw the "found" variable is a string.

thanks

[burns]

HAH! it turns out that Found is a reserved word in SQL! :)