473,836 Members | 1,340 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Syntax error in Insert Into sql statement

86 New Member
Hi.

I am trying to execute the following SQL statement, but I'm getting a syntax error

Expand|Select|Wrap|Line Numbers
  1.     Dim strSQL As String
  2.  
  3.     strSQL = "INSERT INTO usr_problem_list "
  4.     strSQL = strSQL & "(trouble_no, date, user, notes) "
  5.     strSQL = strSQL & "VALUES (" & Text30.Value & ", '" & Now() & "', '" & user_name & "', '" & txtNotes & "');"
  6.  
  7.     DoCmd.RunSQL strSQL
trouble_no is a number field
date is a date/time field
user is a text field
notes is a memo field

thanks!
Jan 13 '12 #1
11 4206
Jeffrey Tan
86 New Member
ok after playing around with it, the culprit seems to be the "date" bc it's a reserved word?

is there any way around this?

thanks!
Jan 13 '12 #2
NeoPa
32,584 Recognized Expert Moderator MVP
Use brackets (Actually - better not to use reserved words at all where possible) around it.

EG. [Date].
Jan 13 '12 #3
Jeffrey Tan
86 New Member
@NeoPa:

thanks for the reply. I'll have to test that out when im at work.

I tried doing table.column, but no luck.

The only other way would be to rename it to something else, but not sure where else the table is used in VBA. so it might create problems, which I definitely do not want that to happen :)
Jan 13 '12 #4
NeoPa
32,584 Recognized Expert Moderator MVP
Normally Table.Date would work reliably, I'm not so sure for the syntax here though. The parentheses enclose a field list specifically. IE. A list of field names rather than references to fields.

The brackets [] are also useful for specifying names which include spaces, as SQL typically treats spaces (and any white-space) as separators (but not when they're within brackets of course).
Jan 14 '12 #5
Monty sing
5 New Member
Expand|Select|Wrap|Line Numbers
  1. "INSERT INTO usr_problem_list(trouble_no, date, user, notes)VALUES(" & Text30.Value & ",'" & Format(System.DateTime.Today.Date, "MM/dd/yyyy") & "', '" & user_name & "', '" & txtNotes & "')"
The Sql Server save Date in above format so use this and he datatype in DB should be Datetime. Try above query
Jan 15 '12 #6
NeoPa
32,584 Recognized Expert Moderator MVP
I think you misunderstand Monty. The problem is not with the use of the Date() function (and certainly not with Now() - however inappropriate this *might* be). The problem is with the use of the word 'Date' to name the field in the record.
Jan 15 '12 #7
Monty sing
5 New Member
Dear NeoPa i Checked the Date Keyword in my Sql server DB but it's not showing anytype of error as you said Date is reserved keyword. I m using the below query for Checking.

Expand|Select|Wrap|Line Numbers
  1. clsobjact.InsertData("insert into Customer(Date)values('" & Format(System.DateTime.Today.Date, "MM/dd/yyyy") & "')")
Jan 16 '12 #8
Mariostg
332 Contributor
@Monty
Expand|Select|Wrap|Line Numbers
  1.     Dim strSQL As String 
  2.  
  3.     strSQL = "INSERT INTO usr_problem_list " 
  4.     strSQL = strSQL & "(trouble_no, [date], [user], notes) " 
  5.     strSQL = strSQL & "VALUES (" & Text30.Value & ", '" & Now() & "', '" & user_name & "', '" & txtNotes & "');" 
  6.  
  7.     DoCmd.RunSQL strSQL 
  8.  
Look at line 4, field date and user between square brackets. As NeoPa mentionned, date is a field name to avoid. As far as user go, it is a DAO object. I am a little bit surprised, but in my case, it would not work unless I used square brackets.
Jan 16 '12 #9
NeoPa
32,584 Recognized Expert Moderator MVP
Monty, you seem to be under the impression that we are in the SQL Server forum. This forum is for Access / VBA.

In Access, there is a function called Date() which is not only available within VBA, but also within the Jet SQL engine. Hence, a reference to Date within Jet SQL (The native SQL engine of MS Access), without the clarification of the brackets around it (as in [Date]) would be likely to be misconstrued. On the other hand of course, with the brackets the problem disappears. A fairly convincing confirmation that this identifies the problem correctly I would think.

PS. Please remember in future to post all your code in [ CODE ] tags as the site rules require.
Jan 16 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

7
6684
by: kosta | last post by:
hello! one of my forms communicates with a database, and is supposed to add a row to a table using an Insert statement... however, I get a 'oledb - syntax error' exception... I have double checked, and the insert works fine (tried to use it from access)... im using visual C# express 2k5... what could be wrong? thanks!
2
12966
by: Tim::.. | last post by:
Can someone please tell me why I keep getting the following error from the code below! Error: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblOfficePageContent_tblPageContent'. The conflict occurred in database 'CPNCMS', table 'tblPageContent', column 'pageID'. The statement has been terminated. I cant seem to see why I keep getting an error when I do the insert!
3
1634
by: jinhy82 | last post by:
Hi! I am currently creating a Registration form which contained: UserID Password, FirstName and LastName. These details would be inserted into Ms Access when I click submi button. But I encounter with the problem to insert 'Password'. It work fine if I did not insert 'Password' but if I did, it will shows that have "syntax error: INSERT INTO..." Did I miss something to write? Please kindly have a look on the cod below, Thank you!
3
2505
by: Nathan Sokalski | last post by:
When trying to submit data to an Access database using ASP.NET I recieve the following error: System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92
5
2149
by: amitbadgi | last post by:
Hi guys, I am getting the following error in teh insert statement , I am converting this asp application to asp.net, here is teh error, Exception Details: System.Runtime.InteropServices.COMException: Syntax error in INSERT INTO statement. Source Error: Line 118: MM_editCmd.ActiveConnection = MM_editConnection
2
3213
by: Geoffrey KRETZ | last post by:
Hello, I'm wondering if the following behaviour is the correct one for PostGreSQL (7.4 on UNIX). I've a table temp_tab with 5 fields (f1,f2,f3,...),and I'm a launching the following request : INSERT INTO temp_tab VALUES (1,2,3)
1
1413
by: vasanth chandrasekaran | last post by:
The error is :Syntax error in INSERT INTO statement. This is my code: try { DataSet ds1; OleDbConnection ConnSql; OleDbConnection ConnExcel; OleDbDataAdapter da1; OleDbDataAdapter da2; string ConnStringSql = "Provider=sqloledb;Data Source = cst;Initial Catalog = Testido;User ID=sa;Pwd=connect#963;";
9
2774
by: Ed Pisa | last post by:
Hello, I have been working with this problem now for several days. I can delete and Update my data but I can not get it to insert a new record. I receive the syntax error insert into. I am not using any reserved words and I have not been able to find my answer googling like crazy. Any help would be greatly appreciated. below is the code that seems to give me the problem. ElseIf NEWMODE = True Then Dim oAdapter As...
0
2355
by: Ed Pisa | last post by:
Hello, I have been working with this problem now for several days. I can delete and Update my data but I can not get it to insert a new record. I receive the syntax error insert into. I am not using any reserved words and I have not been able to find my answer googling like crazy. Any help would be greatly appreciated. below is the code that seems to give me the problem. ElseIf NEWMODE = True Then Dim oAdapter As...
0
9813
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9661
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10831
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10536
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10247
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6976
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5815
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4446
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.