[KyleRose26]

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

[nK0de]

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 & "

[xnn]

NVM suggestion was for different DB engine.

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

[Ryano121]

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

[KyleRose26]

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 & "')"

'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

[nK0de]

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.

[KyleRose26]

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()

[CharlieMay]

Time needs to be wrapped in square brackets too

[KyleRose26]

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.

[nK0de]

Looks about right. Did you get any errors when you run the program?

[CharlieMay]

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?

[KyleRose26]

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.

[KyleRose26]

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.

[CharlieMay]

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

[KyleRose26]

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