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 8 1505
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)
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:Ot**************@TK2MSFTNGP02.phx.gbl...
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
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
"Van T. Dinh" <Va***********@discussions.microsoft.comwrote in message
news:O1**************@TK2MSFTNGP04.phx.gbl...
>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)
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:Ot**************@TK2MSFTNGP02.phx.gbl...
>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
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)
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:uk**************@TK2MSFTNGP03.phx.gbl...
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
"Van T. Dinh" <Va***********@discussions.microsoft.comwrote in message
news:O1**************@TK2MSFTNGP04.phx.gbl...
>>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) "John" <Jo**@nospam.infovis.co.ukwrote in message news:Ot**************@TK2MSFTNGP02.phx.gbl...
>>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
John.
Try to use parameters especially for datetime http://www.vb-tips.com/dbpages.aspx?...6-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
"John" <Jo**@nospam.infovis.co.ukschreef in bericht
news:Ot**************@TK2MSFTNGP02.phx.gbl...
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
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/
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:uk**************@TK2MSFTNGP03.phx.gbl...
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
"Van T. Dinh" <Va***********@discussions.microsoft.comwrote in message
news:O1**************@TK2MSFTNGP04.phx.gbl...
>>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) "John" <Jo**@nospam.infovis.co.ukwrote in message news:Ot**************@TK2MSFTNGP02.phx.gbl...
>>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
Could it have something to do with your string notation you use " to
tell that it is a string try with ' instead.
René
John wrote:
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
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
'================================================= ===
René Jensen wrote:
Could it have something to do with your string notation you use " to
tell that it is a string try with ' instead.
René
John wrote:
>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
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.
---------------------------------------
"John Spencer" <sp*****@chpdm.umbcwrote in message
news:ee**************@TK2MSFTNGP02.phx.gbl...
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
'================================================= ===
René Jensen wrote:
>Could it have something to do with your string notation you use " to tell that it is a string try with ' instead.
René
John wrote:
>>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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: James |
last post by:
My form and results are on one page.
If I use :
if ($Company) {
$query = "Select Company, Contact
From tblworking
Where ID = $Company
Order By Company ASC";
}
|
by: Raghu |
last post by:
Hello...
I am running into a problem while running a query..can some1 help..
this is the query :
**************
SELECT *
from Table S
where S.dtDate1 BETWEEN...
|
by: Squirrel |
last post by:
Hi everyone,
I've created a mail merge Word doc. (using Office XP) , the data source is
an Access query.
Functionality I'm attempting to set up is:
User sets a boolean field to true for...
|
by: jsale |
last post by:
I'm currently using ASP.NET with VS2003 and SQL Server 2003. The ASP.NET app
i have made is running on IIS v6 and consists of a number of pages that allow
the user to read information from the...
|
by: Development - multi.art.studio |
last post by:
Hello everyone,
i just upgraded my old postgres-database from version 7.1 to 7.4.2.
i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with
copy-commands and to one file using...
|
by: Salvador |
last post by:
Hi,
I am using WMI to gather information about different computers (using win2K
and win 2K3), checking common classes and also WMI load balance. My
application runs every 1 minute and reports...
|
by: billmiami2 |
last post by:
I'm experiencing a strange problem that I believe is related to ADO.NET
but I can't say for sure.
I have a simple ASP.NET reporting interface to a SQL Server 2000
database. One report that we...
|
by: wizofaus |
last post by:
I previously posted about a problem where it seemed that changing the
case of the word "BY" in a SELECT query was causing it to run much much
faster.
Now I've hit the same thing again, where...
|
by: HC |
last post by:
Hello, all, I started out thinking my problems were elsewhere but as I
have worked through this I have isolated my problem, currently, as a
difference between MSDE and SQL Express 2005 (I'll just...
|
by: Dave |
last post by:
Hi guys,
I have just set up a duplicate server running:
apache 2.54, mysql 5.04 and php 5.04
This is the same setup as as the server we are using now, apart from
the hardware inside. I have...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |