Problem w/ AutoNumber in INSERT query

[ccshine via AccessMonster.com]

I'm creating a C# .NET Winform application with Access 2000 on the back-end.
In order to write the dataset back to the database, it was necessary to
create my own InsertCommand for the DataAdapter. I decided to create an
Access stored query to insert new rows for use as the InsertCommand.

If I run the application in .NET and attempt to save via this stored query,
the datagrid displays exclamation points in red circles in the left-hand
column and doesn't update the table. When I run the query in Access itself
without the AutoNumber field as a parameter in the query, it tells me that I
don't have enough parameters. If I add the AutoNumber field to the list of
parameters and run it in Access, it inserts the info that I provide but it
won't AutoNumber - which completely misses the point of AutoNumber.

Here's the table design: inventoryID (AutoNumber), date, chemicalID,
amtInStock

Here's the query to insert: INSERT INTO dailyInventory VALUES (date,
chemicalID, amtInStock);

Can someone help me out?

 

[Douglas J Steele]

You need to explicitly list the fields to which you're inserting.

INSERT INTO dailyInventory (date, chemicalID, amtInStock)
VALUES (date, chemicalID, amtInStock)

 

[ccshine via AccessMonster.com]

Actually that's exactly what I was in the process of doing, but it wasn't
working either. I kept getting a error message about the syntax of the
Insert statement. So.... I changed the date field to tdate and it started
working. Sounds like date is a reserved word and cannot be used to field
names????

You need to explicitly list the fields to which you're inserting.

INSERT INTO dailyInventory (date, chemicalID, amtInStock)
VALUES (date, chemicalID, amtInStock)

I'm creating a C# .NET Winform application with Access 2000 on the back-end.
In order to write the dataset back to the database, it was necessary to

[quoted text clipped - 16 lines]

Can someone help me out?

 

[Douglas J Steele]

Correct, date is a reserved word. If you absolutely cannot get around that
field name, enclose it in square brackets:

INSERT INTO dailyInventory ([date], chemicalID, amtInStock)

I'm assuming you're building this string in code and are trying to
concatenate the values of the variables in to the SQL string. You'll have to
delimit the date value with # characters, and ensure that the date is in
mm/dd/yyyy format (regardless of what your Short Date format might have been
set to through Regional Settings. If chemicalID is a text field, you'll need
to surround its value with quotes. Here's how you'd do it in VBA:

strSQL = "INSERT INTO dailyInventory ([date], chemicalID, amtInStock) " &
_
"VALUES (" & Format(MyDateValue, "\#mm\/dd\/yyyy\#") & ", " & _
Chr$(34) & chemicalID & Chr$(34) & ", " & amtInStock & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Actually that's exactly what I was in the process of doing, but it wasn't
working either. I kept getting a error message about the syntax of the
Insert statement. So.... I changed the date field to tdate and it started
working. Sounds like date is a reserved word and cannot be used to field
names????

You need to explicitly list the fields to which you're inserting.

INSERT INTO dailyInventory (date, chemicalID, amtInStock)
VALUES (date, chemicalID, amtInStock)

I'm creating a C# .NET Winform application with Access 2000 on the back-end.
In order to write the dataset back to the database, it was necessary to

[quoted text clipped - 16 lines]

Can someone help me out?