Help with query values and destination fields

[TC]

I'm getting the following error attempting an insert:
Number of query values and destination fields are not the
same.

INSERT INTO table VALUES
('test','test','test','test','test','test')

I am trying to enter 6 values into a row with 7 fields.
However, 1 of those fields is a primary key autoNumber
data type. Therefore, I did not provide a value for it in
my insert statement.

When I remove the primary key field from my database I no
longer get the error. Can someone suggest what I am doing
wrong?

You need to name the fields to insert into:

INSERT INTO (f1, f2, ...) VALUES (v1, v2, ...)

Exclude the autonumber field.

Put the primary key back on!

HTH,
TC

 

[nate heaton]

I'm getting the following error attempting an insert:
Number of query values and destination fields are not the
same.

INSERT INTO table VALUES
('test','test','test','test','test','test')

I am trying to enter 6 values into a row with 7 fields.
However, 1 of those fields is a primary key autoNumber
data type. Therefore, I did not provide a value for it in
my insert statement.

When I remove the primary key field from my database I no
longer get the error. Can someone suggest what I am doing
wrong?

 

[John Vinson]

I'm getting the following error attempting an insert:
Number of query values and destination fields are not the
same.

INSERT INTO table VALUES
('test','test','test','test','test','test')

I am trying to enter 6 values into a row with 7 fields.
However, 1 of those fields is a primary key autoNumber
data type. Therefore, I did not provide a value for it in
my insert statement.

Ok, it's a mismatch: seven fields, six values. That's why the error!

You'll need to explicitly name the fields, leaving out the autonumber.

 

[nate heaton]

I originally tried:

'Create the command object
Set objCmd = Server.CreateObject("ADODB.Command")

'Set the command object properties
Set objCmd.ActiveConnection = conn
objCmd.CommandText = "INSERT INTO portfolio
(title,date,dimensions,medium,type,file) VALUES
('test','test','test','test','test','test')"

objCmd.CommandType = adCmdText

'Execute the Command
objCmd.Execute

I get the error:
Syntax error in INSERT INTO statement.

here are the fields cut and pasted from access:

table name = portfolio

title
date
dimensions
medium
type
file

I have removed the primary key.

my connection to the db works. I'm stumped. The only odd
thing that I notice is that there seems to be a lock next
to the db icon even when the asp pages are closed. I make
sure that I exit out of access and know of no other
programs using it. Any help is much appreciated.

 

[Tim Ferguson]

objCmd.CommandText = _
"INSERT INTO portfolio (title,date,dimensions,medium,type,file) " & _
"VALUES ('test','test','test','test','test','test')"

1) If the [Date] field is a date, then putting a string 'test' into it is
not going to work; GOK what type [Dimensions] is, but if it's numeric then
it's not going to work either.

2) The word DATE is a reserved word in SQL and needs at least to be hidden
inside [brackets] -- better still would be to rename the field to a legal
name. The same applies to TYPE, and Title and File are asking for problems
later on.


But the overall structure of the command is correct this time.

HTH


Tim F

 

[Tim Ferguson]

Thank you. I thought that I may be using a reserved word.
Is there a list of them? Thanks for the answer.

Look in help for "SQL Reserved Words"





Tim F