|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Problem running query in codeI 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. -- Show quoteHide quoteHTH 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 > 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 Show quoteHide quote "Van T. Dinh" <VanThien.D***@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 >> > > 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) Show quoteHide quote "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.D***@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é John wrote: Show quoteHide quote > 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: Show quoteHide quote > 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. --------------------------------------- Show quoteHide quote "John Spencer" <spen***@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 >>> 
Other interesting topics
Make the value of MyObjectA equal to the value of MyObjectB?
Option Strict issue not CLS-compliant System.Net.Sockets.SocketException Error How to go to the next control? Automate file downloads Newbie Missing Something with Access DB Update Using VB .NET setting form properties during runtime converting bitarray to int64 ListView and Registry Key |
|||||||||||||||||||||||
