Syntax error in INSERT INTO statement, Problem.

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 1905 Views - Last Post: 25 March 2012 - 09:24 AM Rate Topic: -----

#1 KyleRose26  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 28
  • Joined: 02-March 12

Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 07:31 AM

Hello, thanks for the help in advance.

Basically I've had the 'Syntax error in INSERT INTO statement' problem for the last couple of hours. I first was adding a new row into a dataset and then using a commandbuilder to update my database and that wasn't working. So I switched to doing a query as seen below without any datasets etc, but still getting the same problem.

The fields you see in the SQL statemenet are all those in the table, except for 'QuestionID' which is an autonumber found after the field 'Help'. Do I need to put it in my statement, if yes what value do i assign it??

But basically I can't see any reason why it isn't working?, someone on this site told me last time that 'Level' might cause some problems hence the use of [Level].

All the values being enetered are all correct format, and like mentioned above the autonumber 'questionid' is the only field not being included.

  'updates database with new row with values.
            Dim cmd As New OleDb.OleDbCommand
            cmd.Connection = Con
            cmd.CommandText = "INSERT INTO Connect4Learning ([Level], Time, Question, CorrectAnswer, SingleorLine, Help, Language) " & _
            "VALUES(" & LevelTxt.Text & ", " & 0 & ", " & QuestionTxt.Text & ", " & CorrectAnswer & ", " & SingleLineTxt.Text & ", " & _
            HelpTxt.Text & ", " & LanguageTxt.Text & ")"
            MsgBox(cmd.CommandText)
            cmd.ExecuteNonQuery()



Thanks again.

This post has been edited by KyleRose26: 25 March 2012 - 07:31 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Syntax error in INSERT INTO statement, Problem.

#2 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 07:44 AM

I see 2 mistakes.

1). You don't have to wrap it around with ampersands or quotes when you're inserting numeric values. " & 0 & ". Just use 0 there.

2). You're missing the ampersand in the front here. HelpTxt.Text & "
Was This Post Helpful? 1
  • +
  • -

#3 xnn  Icon User is offline

  • D.I.C Head

Reputation: 36
  • View blog
  • Posts: 227
  • Joined: 10-February 10

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 07:49 AM

NVM suggestion was for different DB engine.

This post has been edited by xnn: 25 March 2012 - 07:51 AM

Was This Post Helpful? 0
  • +
  • -

#4 Ryano121  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1363
  • View blog
  • Posts: 3,002
  • Joined: 30-January 11

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 07:50 AM

You really need to be using parametrized queries. It will save you from these kinds of errors and protect against SQL injection.

Take a look at this tutorial (it's in C#, but you should be able to get the generally points).

This post has been edited by Ryano121: 25 March 2012 - 07:50 AM

Was This Post Helpful? 1
  • +
  • -

#5 KyleRose26  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 28
  • Joined: 02-March 12

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 07:54 AM

Thanks for the quick response.
I changed the the statement to have 0 without the &'s. Also, for the second part you mentioned it actually does already have that the coding is being cut in line 5. On view source you can see the full code. I'll post below the updated version + whats coming, as it's still not working.

  cmd.CommandText = "INSERT INTO Connect4Learning ([Level], Time, Question, CorrectAnswer, SingleorLine, Help, Language) " & _
            "VALUES (" & LevelTxt.Text & ", 0, '" & QuestionTxt.Text & "', " & CorrectAnswer & ", '" & SingleLineTxt.Text & "', '" & _
            HelpTxt.Text & "', '" & LanguageTxt.Text & "')"



Posted Image
'there should be a ' before the first d. still dosn't work with it there.

This post has been edited by KyleRose26: 25 March 2012 - 07:56 AM

Was This Post Helpful? 0
  • +
  • -

#6 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 08:04 AM

where are the single quotation marks around this, " & LevelTxt.Text & ".

btw as Ryano121 has suggested, you should really consider using parameterized queries. It saves you the troubles like this.
Was This Post Helpful? 0
  • +
  • -

#7 KyleRose26  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 28
  • Joined: 02-March 12

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 08:14 AM

Ok followed your advice and used the parameterized queries like suggested, this is the first time i've done them this way so hopefully they are right. Below is the coding I used, still getting the same error message, so confused as to why.

  'updates database with new row with values.
            Dim cmd As New OleDb.OleDbCommand
            cmd.Connection = Con

            cmd.CommandText = "INSERT INTO Connect4Learning ([Level], Time, Question, CorrectAnswer, SingleorLine, Help, Language) " & _
            "VALUES (@Level, @Time, @Question, @Answer, @SingleorLine, @Help, @Language)"

            With cmd.Parameters
                .AddWithValue("@Level", LevelTxt.Text)
                .AddWithValue("@Time", 0)
                .AddWithValue("@Question", QuestionTxt.Text)
                .AddWithValue("@Answer", CorrectAnswer)
                .AddWithValue("@SingleorLine", SingleLineTxt.Text)
                .AddWithValue("@Help", HelpTxt.Text)
                .AddWithValue("@Language", LanguageTxt.Text)
            End With

            cmd.ExecuteNonQuery()


Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1606
  • View blog
  • Posts: 5,163
  • Joined: 25-September 09

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 08:15 AM

Time needs to be wrapped in square brackets too
Was This Post Helpful? 1
  • +
  • -

#9 KyleRose26  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 28
  • Joined: 02-March 12

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 08:19 AM

Charlie, wrapped time with the brackets still no luck.
Is the parameterized queries coding i've done ok, for those who asked me to change it.
Was This Post Helpful? 0
  • +
  • -

#10 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 08:22 AM

Looks about right. Did you get any errors when you run the program?
Was This Post Helpful? 1
  • +
  • -

#11 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1606
  • View blog
  • Posts: 5,163
  • Joined: 25-September 09

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 08:22 AM

Have you tried going into your Access database and writing the query with the results you want to enter and see where it is detecting a syntax error?
Was This Post Helpful? 1
  • +
  • -

#12 KyleRose26  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 28
  • Joined: 02-March 12

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 08:29 AM

No it didn't work, i'll try the access query now and see what results I get.
I also changed the parameterized queries, but neither of the two seemed to work.

 Dim cmdText As String = "INSERT INTO Connect4Learning ([Level], [Time], Question, CorrectAnswer, SingleorLine, Help, Language) " & _
                        "VALUES (?, ?, ?, ?, ?, ?, ?)"

            Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdText, Con)
            cmd.CommandType = CommandType.Text

            With cmd.Parameters
                .Add("@Level", OleDb.OleDbType.Integer).Value = LevelTxt.Text
                .Add("@Time", OleDb.OleDbType.Integer).Value = 0
                .Add("@Question", OleDb.OleDbType.VarChar).Value = QuestionTxt.Text
                .Add("@Answer", OleDb.OleDbType.VarChar).Value = CorrectAnswer
                .Add("@SingleorLine", OleDb.OleDbType.VarChar).Value = SingleLineTxt.Text
                .Add("@Help", OleDb.OleDbType.VarChar).Value = HelpTxt.Text
                .Add("@Language", OleDb.OleDbType.VarChar).Value = LanguageTxt.Text
            End With

            cmd.ExecuteNonQuery()



Anyways, i'll run the access query and let you know what I'm getting.
Was This Post Helpful? 0
  • +
  • -

#13 KyleRose26  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 28
  • Joined: 02-March 12

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 08:46 AM

I went on access done the following query.
INSERT INTO Connect4Learning ([Level], [Time], Question, CorrectAnswer, SingleorLine, Help, Language)
VALUES (1, 2, 'ds', 'ffd', 'Line', 'DFS', 'English')

and it worked fine added the new row with the certain values.

Did those exact same values in my parameterized queries on access and no luck, same error message problem with INSERT INTO.
Was This Post Helpful? 0
  • +
  • -

#14 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1606
  • View blog
  • Posts: 5,163
  • Joined: 25-September 09

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 08:48 AM

OK, then wrap question, help and language in square brackets. One of those is being misinterpreted as a keyword inside vb and not access.

EDIT:
It's Language, wrap it in square brackets

This post has been edited by CharlieMay: 25 March 2012 - 08:49 AM

Was This Post Helpful? 1
  • +
  • -

#15 KyleRose26  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 28
  • Joined: 02-March 12

Re: Syntax error in INSERT INTO statement, Problem.

Posted 25 March 2012 - 08:52 AM

Charlie I could kiss you right now!!
3 hours later, lesson learnt from now on just square bracket everything.

Thanks for all those who replied, much appreciated.

This post has been edited by KyleRose26: 25 March 2012 - 09:01 AM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2