Bad INSERT INTO command???

[Noozer]

I have an ASP page that is trying to add a record to an MS Access database,
but I'm getting a MS Jet error 80040e14. Syntax error in INSERT INTO
statement.

The INSERT command I'm using is:

INSERT INTO Calls (Who, When) VALUES ('10.0.94.11', #1/18/2005 9:56:37
AM#)

...where "Who" is a 16 character text field and When is a General format
Date/Time field. The only other field in the Calls table is "CallID" with is
an autonumber.

Code I'm using is:

'Create database objects
set adoCon = server.CreateObject("ADODB.Connection")
set rsSet = server.CreateObject("ADODB.Recordset")

'Connect to database
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("Calls.mdb")

'Add Call to database
strSQL="INSERT INTO Calls (Who, When) VALUES ('" & AgentIP & "', #" & When
& "#);"
rsSet.open strSQL, adoCon

....any idea's why this is happening? I believe it has something to do with
the date, but I can't figure it out.

Thx!

 

[Noozer]

INSERT INTO Calls (Who, When) VALUES ('10.0.94.11', #1/18/2005 9:56:37
AM#)

OK, I'm an idiot... WHEN is a reserved word.

INSERT INTO Calls (Who, [When]) VALUES ('10.0.94.11', #1/18/2005 9:56:37
AM#)

....works.

 

[Noozer]

INSERT INTO Calls (Who, When) VALUES ('10.0.94.11', #1/18/2005 9:56:37

AM#)

OK, I'm an idiot... WHEN is a reserved word.

INSERT INTO Calls (Who, [When]) VALUES ('10.0.94.11', #1/18/2005 9:56:37
AM#)

Table has has it's columns renamed, the SQL statement below matches what the
Calls table columns are named. Now it can't find the columns!


INSERT INTO Calls (IP, Submitted) VALUES ("10.0.94.11", #1/18/2005 10:34:42
AM#);
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name
or ordinal.

 

[Guest]

I don't think you can perform an action query with an open command.

Try changing your rsSet.Open to

adoCon.Execute strsql

HTH
Dale

INSERT INTO Calls (Who, When) VALUES ('10.0.94.11', #1/18/2005 9:56:37

AM#)

OK, I'm an idiot... WHEN is a reserved word.

INSERT INTO Calls (Who, [When]) VALUES ('10.0.94.11', #1/18/2005 9:56:37
AM#)

....works.

 

[Noozer]

OK, I'm an idiot... WHEN is a reserved word.

INSERT INTO Calls (Who, [When]) VALUES ('10.0.94.11', #1/18/2005 9:56:37
AM#)

Table has has it's columns renamed, the SQL statement below matches what the
Calls table columns are named. Now it can't find the columns!


INSERT INTO Calls (IP, Submitted) VALUES ("10.0.94.11", #1/18/2005 10:34:42
AM#);
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name
or ordinal.

....and I'm still an idiot. Made a typo when I renamed the columns.

 

[Noozer]

Thanks... I was hoping to use the recordset to get the Autonumber generated
after the insert.

The table I'm inserting into has three columns, "Call ID" which is an
autonumber, plus the two columns below.

How can I read the CallID autonumber after doing the insert?

Thx!

I don't think you can perform an action query with an open command.

Try changing your rsSet.Open to

adoCon.Execute strsql

INSERT INTO Calls (Who, When) VALUES ('10.0.94.11', #1/18/2005

9:56:37
AM#)

OK, I'm an idiot... WHEN is a reserved word.

INSERT INTO Calls (Who, [When]) VALUES ('10.0.94.11', #1/18/2005 9:56:37
AM#)

....works.

 

[Guest]

The autonumber function of Access generates a new number each time an item is
inserted into a table. This number is always increasing, so your best bet
would be to write a SQL string to select the MAX() value from your autonumber
field, right after you execute the other SQL. The down side of this would be
that if someone else performs the same function, at almost exactly the same
time, they could insert to the table between your insert and your select.

Another way would be to write a select statement that looks at your [Who]
and [When] fields in a where clause, something like:

SELECT MAX([ID]) as ID_Value
FROM yourTable
WHERE [Who] = '10.0.94.11'
AND [WHEN] = #1/18/2005 9:56:37#

This would be much less likely to have the previously described situation
occur.

HTH
Dale