|
|
You're in the
vb.net data
group:Problem running query in code
vb.net data:
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 [quoted text, click to view] "Van T. Dinh" <VanThien.Dinh@discussions.microsoft.com> wrote in message news:O1NDrZOOHHA.4992@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" <John@nospam.infovis.co.uk> wrote in message > news:OtvMnJOOHHA.2232@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?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 "John" <John@nospam.infovis.co.uk> schreef in bericht news:OtvMnJOOHHA.2232@TK2MSFTNGP02.phx.gbl... [quoted text, click to view] > 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/ [quoted text, click to view] "John" <John@nospam.infovis.co.uk> wrote in message
news:ukqCugOOHHA.5000@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" <VanThien.Dinh@discussions.microsoft.com> wrote in message > news:O1NDrZOOHHA.4992@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" <John@nospam.infovis.co.uk> wrote in message >> news:OtvMnJOOHHA.2232@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 >>> >> >> > >
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) [quoted text, click to view] "John" <John@nospam.infovis.co.uk> wrote in message news:OtvMnJOOHHA.2232@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) [quoted text, click to view] "John" <John@nospam.infovis.co.uk> wrote in message news:ukqCugOOHHA.5000@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" <VanThien.Dinh@discussions.microsoft.com> wrote in message > news:O1NDrZOOHHA.4992@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" <John@nospam.infovis.co.uk> wrote in message >> news:OtvMnJOOHHA.2232@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é [quoted text, click to view] 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 '==================================================== [quoted text, click to view] 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. --------------------------------------- [quoted text, click to view] "John Spencer" <spencer@chpdm.umbc> wrote in message news:eeZ8YymXHHA.3984@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 >>>
Don't see what you're looking for? Try a search.
|
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |