syntax error in INSERT INTO statement

Question

Hi all... :-(

i have searched quite a bit but could not find any solution to my problem.... =(

im using an access database and trying to take the data from the text boxes and store it into my database...

im using a ready made database that access 2007 offers called Students and i am trying to add the first name, last name and ID information to the database... ive checked almost every where but could not solve the problem... so i thought why not ask over here... please help ....

here is my code....

using System;<br/>
using System.Collections.Generic;<br/>
using System.ComponentModel;<br/>
using System.Data;<br/>
using System.Drawing;<br/>
using System.Linq;<br/>
using System.Text;<br/>
using System.Windows.Forms;<br/>
using System.IO;<br/>
using System.Data.OleDb;<br/>
<br/>
namespace DBFinal_IA<br/>
{<br/>
  public partial class Form1 : Form<br/>
  {<br/>
    private OleDbConnection myConnection;<br/>
    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Students.accdb";<br/>
    public Form1()<br/>
    {<br/>
      InitializeComponent();<br/>
      myConnection = new OleDbConnection(connectionString);<br/>
    }<br/>
<br/>
    private void cmdAdd_Click(object sender, EventArgs e)<br/>
    {<br/>
      OleDbCommand cmd = new OleDbCommand();<br/>
      cmd.CommandType = CommandType.Text;<br/>
      cmd.CommandText = "INSERT INTO Students (ID, Last Name, First Name ) "<br/>
        + "VALUES ('" + txtID.Text + "' , '" + txtLName.Text + "' , '" + txtFName.Text + "') ";<br/>
<br/>
      cmd.Connection = myConnection;<br/>
      try<br/>
      {<br/>
        myConnection.Open();<br/>
        cmd.ExecuteNonQuery();<br/>
      }<br/>
      catch (Exception ex)<br/>
      {<br/>
        MessageBox.Show(ex.Message);<br/>
      }<br/>
      finally<br/>
      {<br/>
        myConnection.Close();<br/>
      }<br/>
    }<br/>
<br/>
    private void Form1_Load(object sender, EventArgs e)<br/>
    {<br/>
      // TODO: This line of code loads data into the 'studentsDataSet.Students' table. You can move, or remove it, as needed.<br/>
      this.studentsTableAdapter.Fill(this.studentsDataSet.Students);<br/>
<br/>
    }<br/>
  }<br/>
}

it gives me a run time error saying syntax error in INSERT INTO statement.... and its driving me crazy ...

so once again please help and thanx in advance...

Answer 1

That's because you are composing an SQL using string combination. It will cause all kinds of problems including SQL injection attacks. 

I suggest you to create a strong typed dataset and let the dataset designer generate all the necessary code for your parameterized query. If you need more help on strong typed datasets, check http://social.msdn.microsoft.com/Forums/en/adodotnetdataset/threads

 

---

---

The following is signature, not part of post
Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
Visual C++ MVP

Answer 2

Maybe try putting "[" and "]" around the column names containing spaces, like [Last Name], [First Name]

K

Answer 3

@Sheng jiang:   ummmm... thnx for the link ill chk it out, but the code i provided should b working as well because i was following a video tutorial ... and the guy who made the video his code worked fine.... but mine didn't... you got any idea why this might be happening ???

 

@specialk83:  I tried that as well it didn't work... =(

it says something about duplicating data when i do that...

Answer 4

Check the SQL string before sending it to the database. Maybe you typed some characters that are used in sql grammar. 

---

---

The following is signature, not part of post
Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
Visual C++ MVP

Answer 5

you mean the connection string right ????

well if you mean that then thats not the problem because i copy pasted the connection string from when i was creating the dataset....

anything else ???

Answer 6

No, cmd.CommandText . Set a breakpoint at the line that generates its value and watch its value when you debug the code.

---

---

The following is signature, not part of post
Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
Visual C++ MVP

Answer 7

k , ill check that out ... can you give me a heads up on whats supposed to happen....

Answer 8

The line after cmd.CommandText = is not whats supposed to happen. It is an invitation to all kinds of sql errors because you let the user to write part of the SQL. 

---

---

The following is signature, not part of post
Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
Visual C++ MVP

Answer 9

hmmmmmm... so what do you suggest i do???

or is there another way to use an access database ???

Answer 10

How to: Connect to Data in an Access Database 

---

---

The following is signature, not part of post
Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
Visual C++ MVP

Answer 11

Hi Devillish,

Welcome to MSDN Forums!

 

There’s a blank in the fields in your data table. So if you want write these fields’ name into a sql statement you need use the square brackets surround that names.

 

So in your project the sql statement need to be written like this:

INSERT INTO Table1 (ID, [Last Name], [First Name]) VALUES ('6' , 'add1' , 'add11')

 

And you can take a look at the following article to know how to test the sql statement in access 2007. Then you can test the sql statement first then place the tested sql statement into your project to avoid such mistake.

Testing the sql statement in Access 2007

 

If there’s anything unclear, please feel free to let me know.

 

Have a nice day!

Mike

-------------------------------------------------------------------

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to the others community members reading the thread.

 

*****************************************************

[All-In-One Code Framework]

Sample world! You will get more from this world!

Welcome to the new world!

=================================================

Third party disclaimer

 

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information is found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

Answer 12

Dear Devillish,

In order to find the actual issue in your SQL query please give the exact SQL query.

 

You can perform the following steps

1. Set break point on the following line

cmd.Connection = myConnection;

2. In Immediate window, type "?cmd.CommandText " and press enter

3. Now copy that query string and give us.

 

Looking for your reply.

---

Thanks and Regards, Bharath S.

Answer 13

Use parameters in your command instead of concatenating if you want to be able to handle names such as "O'Hara"

Answer 14

Hello Mike... thnx for the help...

actually i tried putting those two fields in square brackets but then it gives an error

about something like duplicating data .... and as for the testing sql statement in access link you gave ill check it out

and with the query you wrote whats inside the VALUES brackets??? can you explain that please ...

thnx =)

Answer 15

sorry for the incredibly noob question but where is the Immediate window???

i set the break point but no luck in finding the Immediate window =/

help

Answer 16

If you are getting an error about duplicating data, are you sure you don't already have a record in your table with the same ID as what you're trying to add?

If I were you, I'd create a stored procedure which first of all checks to see whether the record already exists and if not, adds the new record to the table.

You can get to the immediate window from the View menu within Visual Studio, there should be a menu item called Immediate Window which, after clicking, will then appear towards the bottom of VS.

I also think Mike stole my idea of the square brackets... :D

K

Answer 17

yup im sure ... i have an empty database. But its its still giving that error...

here is what it says:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. "

any idea what it means ????

this comes up when i put the square brackets...

 

Answer 18

Ah, is the ID column automatically generated as Identity Specification? If so, you can't insert an ID value

K

Answer 19

oh man... thanks guys =D ummmmm...1 more problem... when i open data sources, right click on the students table and click on preview data... i click the preview button but nothing shows in that preview... any reason why??? how do i preview the data ??? once again thnx =D

Answer 20

Hi Devillish,

Welcome to MSDN Forums!

 

I had tested that link again, and I can access that article. Do the others also cannot access my article?

 

 

Testing the sql statement in Access 2007

1.       Create a table in access2007, and click on the “Create” tab, then you can see the following information, and then you can click the “Query Design” to create a query in access2007.

 

2.       After you click the “Query Design” you can see the following information. Because of we will write the sql statement manually in access2007 so we close this window (you also can click “Add” button, it doesn’t meter for the following steps).

 

3.       After you close the window, then you can right click on the panel or on the tab head (“Query1”), select the “SQL View”.

 

4.       Then we can open this interface and we can write the sql statement on this window, then click on the “Run” you can execute this sql statement in access2007.

 

5.       This is the result information after you run the above sql statement. And you also can right click on the “Query1” then select the “SQL View” to go back to the sql view and test the next sql statement.

 

 

If you want preview the data from vs2010 you need add this data table as the project’s data source, and then right click on the dataset and select “Preview Data…”

 

 

After you click on the “Preview” button you will see the data in this data table from the database.

 

 

 

Please create a new thread if you have the second question. Thanks for your cooperation and understanding.

 

If there’s anything unclear, please feel free to let me know.

 

Best wishes,

Mike

-------------------------------------------------------------------

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to the others community members reading the thread.

 

*****************************************************

[All-In-One Code Framework]

Sample world! You will get more from this world!

Welcome to the new world!