insert into error

Granted, I'm a bit rusty with this stuff but looking at everything else
i've done for an example, I see no errors but I'm still getting one.
Here is the error I'm getting in IE:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/nogo.asp, line 19

I've done a response.write with the SQL statement and I get this (wordwrap):

INSERT INTO master (UserName, WorkstationIP, datetime) VALUES
('domain\jim','192.168.1.3','5/10/2005 2:08:54 PM')

And here's my code:

<%@ Language=VBScript %>
<%
Option Explicit
Dim EmpName, EmpIP, pStr, Conn, SQL

EmpName = Request.ServerVariables("AUTH_USER")
EmpIP = Request.ServerVariables("REMOTE_ADDR")

pStr = "provate, no-cache, must-revalidate"
Response.ExpiresAbsolute = #2000-01-01#
Response.AddHeader "pragma", "no-cache"
Response.AddHeader "cache-control", pStr

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
server.MapPath("nogo.mdb")

Set SQL = Conn.Execute("INSERT INTO master (UserName, WorkstationIP,
datetime) VALUES ('" & EmpName & "','" & EmpIP & "','" & NOW() & "')")

'Response.Write SQL

' Clean Up
Conn.Close
Set SQL = Nothing
Set EmpName = Nothing
Set EmpIP = Nothing
%>

Thanks for the help.

Re: insert into error

Jim in Arizona wrote:
> Granted, I'm a bit rusty with this stuff but looking at everything
> else i've done for an example, I see no errors but I'm still getting
> one.
> Here is the error I'm getting in IE:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /nogo.asp, line 19
>
> I've done a response.write with the SQL statement and I get this
> (wordwrap):
>
> INSERT INTO master (UserName, WorkstationIP, datetime) VALUES
> ('domain\jim','192.168.1.3','5/10/2005 2:08:54 PM')
>

Date/Time literals need to be delimited with #'s in JetSQL.

Of course, it's silly to be forced to worry about delimiters (let alone
unsafe) ...

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl


or, if you don't want to deal with saved queries:

http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: insert into error

Bob Barrows [MVP] wrote:
> Jim in Arizona wrote:
>
>>Granted, I'm a bit rusty with this stuff but looking at everything
>>else i've done for an example, I see no errors but I'm still getting
>>one.
>>Here is the error I'm getting in IE:
>>
>>Error Type:
>>Microsoft JET Database Engine (0x80040E14)
>>Syntax error in INSERT INTO statement.
>>/nogo.asp, line 19
>>
>>I've done a response.write with the SQL statement and I get this
>>(wordwrap):
>>
>>INSERT INTO master (UserName, WorkstationIP, datetime) VALUES
>>('domain\jim','192.168.1.3','5/10/2005 2:08:54 PM')
>>
>
>
> Date/Time literals need to be delimited with #'s in JetSQL.
>
> Of course, it's silly to be forced to worry about delimiters (let alone
> unsafe) ...
>
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
>
> or, if you don't want to deal with saved queries:
>
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> Bob Barrows

I did have the datetime field in access as a date/time type but then
changed it to text. I'll play around with it some more to make sure I
didn't screw that up somehow.

Re: insert into error

Bob Barrows [MVP] wrote:
> Jim in Arizona wrote:
>
>>Granted, I'm a bit rusty with this stuff but looking at everything
>>else i've done for an example, I see no errors but I'm still getting
>>one.
>>Here is the error I'm getting in IE:
>>
>>Error Type:
>>Microsoft JET Database Engine (0x80040E14)
>>Syntax error in INSERT INTO statement.
>>/nogo.asp, line 19
>>
>>I've done a response.write with the SQL statement and I get this
>>(wordwrap):
>>
>>INSERT INTO master (UserName, WorkstationIP, datetime) VALUES
>>('domain\jim','192.168.1.3','5/10/2005 2:08:54 PM')
>>
>
>
> Date/Time literals need to be delimited with #'s in JetSQL.
>
> Of course, it's silly to be forced to worry about delimiters (let alone
> unsafe) ...
>
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
>
> or, if you don't want to deal with saved queries:
>
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> Bob Barrows


I tried this:
Set SQL = Conn.Execute("INSERT INTO master (username, empip, datetime)
VALUES ('" & EmpName & "','" & EmpIP & "','#" & NOW() & "#')")

And, I tried this (no single quotes used in date field):
Set SQL = Conn.Execute("INSERT INTO master (username, empip, datetime)
VALUES ('" & EmpName & "','" & EmpIP & "',#" & NOW() & "#)")

In both cases, I get the same frustrating error.

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/nogo.asp, line 19

Like I said in a my post right before this one, I was doing the
date/time as a text field and not a date/time field (data type) and just
passing a string instead of a date using hash marks.

nogo.mdb master table
FIELD DATA TYPE FIELD SIZE
ID Auto Numb NA
EmpName Text 50
EmpIP Text 20
datetime Text 50

I was still getting the same error.

I'm using access 2003 for the back end this time (for the first time,
all others were 2K or XP). Should I be using a different style of INSERT
INTO statement or something different in my connection string? I did
check and make sure file permissions were accurate on the MDB file,
although i'm not getting any error to point me in such a direction.

Thanks Bob.

Re: insert into error

Jim in Arizona wrote:

>
> I tried this:
> Set SQL = Conn.Execute("INSERT INTO master (username, empip, datetime)
> VALUES ('" & EmpName & "','" & EmpIP & "','#" & NOW() & "#')")
>
> And, I tried this (no single quotes used in date field):
> Set SQL = Conn.Execute("INSERT INTO master (username, empip, datetime)

Wait a minute. Why are you doing this "Set SQL = " business? The Execute
method returns a recordset object, but your query is not returning any
data - you don't need a recordset!


> VALUES ('" & EmpName & "','" & EmpIP & "',#" & NOW() & "#)")

> In both cases, I get the same frustrating error.
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /nogo.asp, line 19
>
> Like I said in a my post right before this one, I was doing the
> date/time as a text field and not a date/time field (data type) and
> just passing a string instead of a date using hash marks.

Why are you storing a date in a Text field? Never mind ...

Access DOES have a Now() function, as well as a CStr() function:


SQL = "INSERT INTO master (UserName, WorkstationIP,
datetime) VALUES ('" & EmpName & "','" & EmpIP & "', CStr(Now()))"

Response.Write SQL

Conn.Execute SQL,,1

>
> I'm using access 2003 for the back end this time (for the first time,
> all others were 2K or XP). Should I be using a different style of
> INSERT INTO statement or something different in my connection string? I

No

> did
> check and make sure file permissions were accurate on the MDB file,
> although i'm not getting any error to point me in such a direction.

Exactly - this was irrelevant
>
> Thanks Bob.
Look. Open your database in Access, create a new query in Design View,
switch to SQL View and try to run te response.written sql statement. You
will likely get a better error message.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: insert into error

Maybe datetime is a reserved word in Jet? It surely is in SQL Server, try
square brackets around the field names.

-Mark


"Bob Barrows [MVP]" wrote in message
news:uSGRAHbVFHA.1384@TK2MSFTNGP09.phx.gbl...
> Jim in Arizona wrote:
>
>>
>> I tried this:
>> Set SQL = Conn.Execute("INSERT INTO master (username, empip, datetime)
>> VALUES ('" & EmpName & "','" & EmpIP & "','#" & NOW() & "#')")
>>
>> And, I tried this (no single quotes used in date field):
>> Set SQL = Conn.Execute("INSERT INTO master (username, empip, datetime)
>
> Wait a minute. Why are you doing this "Set SQL = " business? The Execute
> method returns a recordset object, but your query is not returning any
> data - you don't need a recordset!
>
>
>> VALUES ('" & EmpName & "','" & EmpIP & "',#" & NOW() & "#)")
>
>> In both cases, I get the same frustrating error.
>>
>> Error Type:
>> Microsoft JET Database Engine (0x80040E14)
>> Syntax error in INSERT INTO statement.
>> /nogo.asp, line 19
>>
>> Like I said in a my post right before this one, I was doing the
>> date/time as a text field and not a date/time field (data type) and
>> just passing a string instead of a date using hash marks.
>
> Why are you storing a date in a Text field? Never mind ...
>
> Access DOES have a Now() function, as well as a CStr() function:
>
>
> SQL = "INSERT INTO master (UserName, WorkstationIP,
> datetime) VALUES ('" & EmpName & "','" & EmpIP & "', CStr(Now()))"
>
> Response.Write SQL
>
> Conn.Execute SQL,,1
>
>>
>> I'm using access 2003 for the back end this time (for the first time,
>> all others were 2K or XP). Should I be using a different style of
>> INSERT INTO statement or something different in my connection string? I
>
> No
>
>> did
>> check and make sure file permissions were accurate on the MDB file,
>> although i'm not getting any error to point me in such a direction.
>
> Exactly - this was irrelevant
>>
>> Thanks Bob.
> Look. Open your database in Access, create a new query in Design View,
> switch to SQL View and try to run te response.written sql statement. You
> will likely get a better error message.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: insert into error

That is indeed the issue...

"INSERT INTO master (username, empip, [datetime]) ..."

-Mark



"Mark J. McGinty" wrote in message
news:uWmB0YcVFHA.2684@TK2MSFTNGP09.phx.gbl...
> Maybe datetime is a reserved word in Jet? It surely is in SQL Server, try
> square brackets around the field names.
>
> -Mark
>
>
> "Bob Barrows [MVP]" wrote in message
> news:uSGRAHbVFHA.1384@TK2MSFTNGP09.phx.gbl...
>> Jim in Arizona wrote:
>>
>>>
>>> I tried this:
>>> Set SQL = Conn.Execute("INSERT INTO master (username, empip, datetime)
>>> VALUES ('" & EmpName & "','" & EmpIP & "','#" & NOW() & "#')")
>>>
>>> And, I tried this (no single quotes used in date field):
>>> Set SQL = Conn.Execute("INSERT INTO master (username, empip, datetime)
>>
>> Wait a minute. Why are you doing this "Set SQL = " business? The Execute
>> method returns a recordset object, but your query is not returning any
>> data - you don't need a recordset!
>>
>>
>>> VALUES ('" & EmpName & "','" & EmpIP & "',#" & NOW() & "#)")
>>
>>> In both cases, I get the same frustrating error.
>>>
>>> Error Type:
>>> Microsoft JET Database Engine (0x80040E14)
>>> Syntax error in INSERT INTO statement.
>>> /nogo.asp, line 19
>>>
>>> Like I said in a my post right before this one, I was doing the
>>> date/time as a text field and not a date/time field (data type) and
>>> just passing a string instead of a date using hash marks.
>>
>> Why are you storing a date in a Text field? Never mind ...
>>
>> Access DOES have a Now() function, as well as a CStr() function:
>>
>>
>> SQL = "INSERT INTO master (UserName, WorkstationIP,
>> datetime) VALUES ('" & EmpName & "','" & EmpIP & "', CStr(Now()))"
>>
>> Response.Write SQL
>>
>> Conn.Execute SQL,,1
>>
>>>
>>> I'm using access 2003 for the back end this time (for the first time,
>>> all others were 2K or XP). Should I be using a different style of
>>> INSERT INTO statement or something different in my connection string? I
>>
>> No
>>
>>> did
>>> check and make sure file permissions were accurate on the MDB file,
>>> although i'm not getting any error to point me in such a direction.
>>
>> Exactly - this was irrelevant
>>>
>>> Thanks Bob.
>> Look. Open your database in Access, create a new query in Design View,
>> switch to SQL View and try to run te response.written sql statement. You
>> will likely get a better error message.
>>
>> Bob Barrows
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so I
>> don't check it very often. If you must reply off-line, then remove the
>> "NO SPAM"
>>
>
>

Re: insert into error

Interesting. This is the first time I've seen a reserved word impact the
columns listed inside the parentheses of an INSERT clause. I had seen
reserved words without brackets used successfully (to my surprise) in the
past and I assumed the parser was ignoring the words inside the parentheses.

I've been getting quite the education this week ...

Bob

Mark J. McGinty wrote:
> That is indeed the issue...
>
> "INSERT INTO master (username, empip, [datetime]) ..."
>
> -Mark
>
>


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: insert into error

Bob Barrows [MVP] wrote:
>
> Conn.Execute SQL,,1
>
Oops! I meant to type:

Conn.Execute SQL,,129

When executing a non-records-returning query, you should use
adExecuteNoRecords (128) to tell ADO not to bother constructing a recordset.
Combined with adCmdText (1), the resulting value is 129, which says "I am
passing a string containing a sql statement that does not return records".

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: insert into error

Jim in Arizona wrote:
>>>
>>> INSERT INTO master (UserName, WorkstationIP, datetime) VALUES
>>> ('domain\jim','192.168.1.3','5/10/2005 2:08:54 PM')

And here is the list of words you should avoid for field and table names:
http://www.aspfaq.com/show.asp?id=2080

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: insert into error

Bob Barrows [MVP] wrote:
> Jim in Arizona wrote:
>
>
>>I tried this:
>>Set SQL = Conn.Execute("INSERT INTO master (username, empip, datetime)
>>VALUES ('" & EmpName & "','" & EmpIP & "','#" & NOW() & "#')")
>>
>>And, I tried this (no single quotes used in date field):
>>Set SQL = Conn.Execute("INSERT INTO master (username, empip, datetime)
>
>
> Wait a minute. Why are you doing this "Set SQL = " business? The Execute
> method returns a recordset object, but your query is not returning any
> data - you don't need a recordset!
>
>
>
>>VALUES ('" & EmpName & "','" & EmpIP & "',#" & NOW() & "#)")
>
>
>>In both cases, I get the same frustrating error.
>>
>>Error Type:
>>Microsoft JET Database Engine (0x80040E14)
>>Syntax error in INSERT INTO statement.
>>/nogo.asp, line 19
>>
>>Like I said in a my post right before this one, I was doing the
>>date/time as a text field and not a date/time field (data type) and
>>just passing a string instead of a date using hash marks.
>
>
> Why are you storing a date in a Text field? Never mind ...
>
> Access DOES have a Now() function, as well as a CStr() function:

I had actually tried converting it to a string using CStr() but it made
no difference. I don't really care if the date is in actual date format
or string format, as long as its there.

>
> SQL = "INSERT INTO master (UserName, WorkstationIP,
> datetime) VALUES ('" & EmpName & "','" & EmpIP & "', CStr(Now()))"
>
> Response.Write SQL
>
> Conn.Execute SQL,,1
>

I will do it this way. About half of my code is done in that fasion, and
the other half done using record sets. I don't know why other than at
the time, if it worked, it worked.


>
> Look. Open your database in Access, create a new query in Design View,
> switch to SQL View and try to run te response.written sql statement. You
> will likely get a better error message.
>
> Bob Barrows

In the future, if I run into problems again, I'll do it that way. Thanks.

Re: insert into error

Mark J. McGinty wrote:
> That is indeed the issue...
>
> "INSERT INTO master (username, empip, [datetime]) ..."
>
> -Mark


Thanks Mark. I was hoping I wasn't crazy.

I didn't even think of datetime as a reserved word.

Re: insert into error

Bob Barrows [MVP] wrote:
> Bob Barrows [MVP] wrote:
>
>>Conn.Execute SQL,,1
>>
>
> Oops! I meant to type:
>
> Conn.Execute SQL,,129
>
> When executing a non-records-returning query, you should use
> adExecuteNoRecords (128) to tell ADO not to bother constructing a recordset.
> Combined with adCmdText (1), the resulting value is 129, which says "I am
> passing a string containing a sql statement that does not return records".
>
> Bob Barrows

I had been told to use 1 and 129 in the past and even though I had
looked those up somewhere once, it didn't make a whole lot of sense to
me, until now. Thanks.