Problem running query in code

[John]

Hi

I am using the below code to run a sql on an underlying access table;

insStr = "INSERT INTO [Web Site Action Queue] ( Action, [Client ID],
[Operator], Request_Date ) " & _
"VALUES (""Modify Client"", 93, ""Administrator"", Now())"

Dim insCmd As New OleDbCommand(insStr, dbConContacts)

insCmd.ExecuteNonQuery()

I get a 'Syntax error in INSERT INTO statement.' error on the last line.

But if I run the same query as below directly in access it works fine.

INSERT INTO [Web Site Action Queue] ( Action, [Client ID], [Operator],
Request_Date ) VALUES ("Modify Client", 93, "Administrator", Now())

What is the problem and how can I fix it?

Thanks

Regards

 

[Van T. Dinh]

I think the problem is Now() is a VBA function and this is available in JET
only if you are running the Query in Access (via the Expression Service).

From the newsgroups you posted to, I guess you are running this in VB.Net
and I don't think JET would recognize the Now() used in your SQL.

Try with a literal date/time value and see if Now() is the cause.

Perhaps, the VB.Net experts can advise you of an alternative.

 

[John]

Tried following, still no luck.

INSERT INTO [Web Site Action Queue] ( Action, [Client ID], [Operator],
Request_Date ) VALUES ("Modify Client", 93, "Administrator", "15/01/2007
20:57:54")

Works in Access but not from in vb.net ExecuteNonQuery().

Thanks

Regards

I think the problem is Now() is a VBA function and this is available in JET
only if you are running the Query in Access (via the Expression Service).

From the newsgroups you posted to, I guess you are running this in VB.Net
and I don't think JET would recognize the Now() used in your SQL.

Try with a literal date/time value and see if Now() is the cause.

Perhaps, the VB.Net experts can advise you of an alternative.

--
HTH
Van T. Dinh
MVP (Access)

Hi

I am using the below code to run a sql on an underlying access table;

insStr = "INSERT INTO [Web Site Action Queue] ( Action, [Client ID],
[Operator], Request_Date ) " & _
"VALUES (""Modify Client"", 93, ""Administrator"", Now())"

Dim insCmd As New OleDbCommand(insStr, dbConContacts)

insCmd.ExecuteNonQuery()

I get a 'Syntax error in INSERT INTO statement.' error on the last line.

But if I run the same query as below directly in access it works fine.

INSERT INTO [Web Site Action Queue] ( Action, [Client ID], [Operator],
Request_Date ) VALUES ("Modify Client", 93, "Administrator", Now())

What is the problem and how can I fix it?

Thanks

Regards

 

[Van T. Dinh]

Date literal in JET must be of the US format "mm/dd/yyyy hh:nn:ss" (or an
unambiguous format like "yyyy-mm-dd hh:nn:ss") and enclosed in hashes (#),
not double-quotes. Try:

INSERT INTO [Web Site Action Queue]
( [Action], [Client ID], [Operator], [Request_Date] )
VALUES
("Modify Client", 93, "Administrator", #01/15/2005 20:57:54#)

--
HTH
Van T. Dinh
MVP (Access)

Tried following, still no luck.

INSERT INTO [Web Site Action Queue] ( Action, [Client ID], [Operator],
Request_Date ) VALUES ("Modify Client", 93, "Administrator", "15/01/2007
20:57:54")

Works in Access but not from in vb.net ExecuteNonQuery().

Thanks

Regards

I think the problem is Now() is a VBA function and this is available in
JET only if you are running the Query in Access (via the Expression
Service).

From the newsgroups you posted to, I guess you are running this in VB.Net
and I don't think JET would recognize the Now() used in your SQL.

Try with a literal date/time value and see if Now() is the cause.

Perhaps, the VB.Net experts can advise you of an alternative.

--
HTH
Van T. Dinh
MVP (Access)

Hi

I am using the below code to run a sql on an underlying access table;

insStr = "INSERT INTO [Web Site Action Queue] ( Action, [Client ID],
[Operator], Request_Date ) " & _
"VALUES (""Modify Client"", 93, ""Administrator"", Now())"

Dim insCmd As New OleDbCommand(insStr, dbConContacts)

insCmd.ExecuteNonQuery()

I get a 'Syntax error in INSERT INTO statement.' error on the last line.

But if I run the same query as below directly in access it works fine.

INSERT INTO [Web Site Action Queue] ( Action, [Client ID], [Operator],
Request_Date ) VALUES ("Modify Client", 93, "Administrator", Now())

What is the problem and how can I fix it?

Thanks

Regards

 

[Cor Ligthert [MVP]]

John.

Try to use parameters especially for datetime

http://www.vb-tips.com/dbpages.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071

It is at the bottom of the page

Be aware that the way it is showed is the most simple one as you will
probably never use it.

Cor

 

[Miha Markic [MVP C#]]

You should use a parametrized sql statement instead, something like:
INSERT INTO [Web Site Action Queue] ( Action, [Client ID], [Operator],
Request_Date ) VALUES (?, ?, ?, ?)
And pass values as parameters. It will save you the trouble of formatting
and what's most important, it will protect you from sql injection attacks.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Tried following, still no luck.

INSERT INTO [Web Site Action Queue] ( Action, [Client ID], [Operator],
Request_Date ) VALUES ("Modify Client", 93, "Administrator", "15/01/2007
20:57:54")

Works in Access but not from in vb.net ExecuteNonQuery().

Thanks

Regards

I think the problem is Now() is a VBA function and this is available in
JET only if you are running the Query in Access (via the Expression
Service).

From the newsgroups you posted to, I guess you are running this in VB.Net
and I don't think JET would recognize the Now() used in your SQL.

Try with a literal date/time value and see if Now() is the cause.

Perhaps, the VB.Net experts can advise you of an alternative.

--
HTH
Van T. Dinh
MVP (Access)

Hi

I am using the below code to run a sql on an underlying access table;

insStr = "INSERT INTO [Web Site Action Queue] ( Action, [Client ID],
[Operator], Request_Date ) " & _
"VALUES (""Modify Client"", 93, ""Administrator"", Now())"

Dim insCmd As New OleDbCommand(insStr, dbConContacts)

insCmd.ExecuteNonQuery()

I get a 'Syntax error in INSERT INTO statement.' error on the last line.

But if I run the same query as below directly in access it works fine.

INSERT INTO [Web Site Action Queue] ( Action, [Client ID], [Operator],
Request_Date ) VALUES ("Modify Client", 93, "Administrator", Now())

What is the problem and how can I fix it?

Thanks

Regards

 

[=?ISO-8859-1?Q?Ren=E9_Jensen?=]

Could it have something to do with your string notation you use " to
tell that it is a string try with ' instead.

René

 

[John Spencer]

My guess is that NOW() is causing the problem. It is probably not
understood by the database engine. Try inserting the date and time as a
literal value using # (or perhaps ') as the delimiter.

Something like:

insStr = "INSERT INTO [Web Site Action Queue] ( Action, [Client ID],
[Operator], Request_Date ) " & _ "VALUES (""Modify Client"", 93,
""Administrator"", #" & Now() & "#)"

This of course assumes that you have NOW as an available function in
your code environment.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Could it have something to do with your string notation you use " to
tell that it is a string try with ' instead.

René

Hi

I am using the below code to run a sql on an underlying access table;

insStr = "INSERT INTO [Web Site Action Queue] ( Action, [Client ID],
[Operator], Request_Date ) " & _
"VALUES (""Modify Client"", 93, ""Administrator"", Now())"

Dim insCmd As New OleDbCommand(insStr, dbConContacts)

insCmd.ExecuteNonQuery()

I get a 'Syntax error in INSERT INTO statement.' error on the last line.

But if I run the same query as below directly in access it works fine.

INSERT INTO [Web Site Action Queue] ( Action, [Client ID], [Operator],
Request_Date ) VALUES ("Modify Client", 93, "Administrator", Now())

What is the problem and how can I fix it?

Thanks

Regards

 

[RobinS]

He's using Access, so Now() should work, and he should take out the #
delimiters.

And use single quotes around 'Modify Client' and 'Administrator'.

Robin S.
---------------------------------------

My guess is that NOW() is causing the problem. It is probably not
understood by the database engine. Try inserting the date and time as a
literal value using # (or perhaps ') as the delimiter.

Something like:

insStr = "INSERT INTO [Web Site Action Queue] ( Action, [Client ID],
[Operator], Request_Date ) " & _ "VALUES (""Modify Client"", 93,
""Administrator"", #" & Now() & "#)"

This of course assumes that you have NOW as an available function in your
code environment.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Could it have something to do with your string notation you use " to
tell that it is a string try with ' instead.

René

Hi

I am using the below code to run a sql on an underlying access table;

insStr = "INSERT INTO [Web Site Action Queue] ( Action, [Client ID],
[Operator], Request_Date ) " & _
"VALUES (""Modify Client"", 93, ""Administrator"", Now())"

Dim insCmd As New OleDbCommand(insStr, dbConContacts)

insCmd.ExecuteNonQuery()

I get a 'Syntax error in INSERT INTO statement.' error on the last
line.

But if I run the same query as below directly in access it works fine.

INSERT INTO [Web Site Action Queue] ( Action, [Client ID], [Operator],
Request_Date ) VALUES ("Modify Client", 93, "Administrator", Now())

What is the problem and how can I fix it?

Thanks

Regards