SQL to Access insert into statement

[Joshua Grow]

Ok, so I am getting the following error when I run the code at the bottom of this page. I am using Visual Studio 2010 Professional, Access 2000 .mdb database. Any suggestions on why this doesn't work would be great!

Error:

System.Data.OleDb.OleDbException (0x80040E14): Syntax error in INSERT INTO statement.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at Kitchen_Helper.Calendar.btnCalendarSave_Click(Obje ct sender, EventArgs e) in E:\CINS 237\Kitchen Helper\Kitchen Helper\Kitchen Helper\Calendar.vb:line 344

 Try
            If txtCalendarEntry.Text = "" Then
                MsgBox("Please enter a message!", MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Error")
                Exit Sub
            End If
 
            con.Open()
            Dim mySQL As String =
                "INSERT INTO Calendar (Selected Date, User, Note) VALUES ('" &
                    txtNote.Text & "','" &
                    txtUserName.Text & "','" &
                    txtCalendarEntry.Text & ")"
 
            Dim rs As New OleDb.OleDbCommand(mySQL, con)
            MsgBox(mySQL)
            rs.ExecuteNonQuery()
            con.Close()
            RefreshGrid("")
        Catch ex As Exception
            MsgBox(Convert.ToString(ex))
        Finally
            con.Close()
        End Try
 

[Rabbit]

Your selected date field has a space in its name. Either there is no space or there is one and you need to demarcate it as a field with square brackets. Also, your values that you're inserting do not match the column order that you specified.

[Joshua Grow]

Ok, I am new to SQL, so could you please elaborate on "demarcate" the field with brackets? Does that mean this...
{Selected Date}
It does have a space in the access table header. Also, the fields are denoted corrected, i know they are labeled weird, but I had to "fanagle" them to make it work with what I had.
txtNote.Text = (just for testing) Date
txtUserName.Text = User Name
txtCalendarEntry.Text = Note
I know it sounds dumb, but it would make sense if you saw the gui or the way it was setup. I will attempt to edit the "Selected Date" thing. :\

[Rabbit]

Close, instead of curly brackets, use the square brackets.

[Joshua Grow]

Ok, just tried that, same error message, also added an ' at the end for possible syntax... like this :

txtCalendarEntry.Text & "')"
 

Any other possible ideas by chance? This is driving me loopy! lol.

[Rabbit]

The line breaks are just for our benefit right? Because you can't have line breaks in a statement unless you use an underscore.

Also, I assume there are no single quotes in the input data because otherwise you would have to escape it.

And have you output the string to verify it? And did you try running that string directly in SQL?

[Joshua Grow]

The line breaks are just for our benefit right? Because you can't have line breaks in a statement unless you use an underscore.

The line breaks are there, but they return the same output whether they are on the same line or not (I moved them all onto the same line to double check, but yeah I thought it was weird to that it worked)

Also, I assume there are no single quotes in the input data because otherwise you would have to escape it.

Forgive my ignorance, but can you explain this a little further? Their shouldnt be any quotes in the searchable values, but I don't quite understand the question.

And have you output the string to verify it? And did you try running that string directly in SQL?

That is the msgbox(mySQL) part at line 15. Here is what the output string says...

* - denotes what the user input in the fields, not a wildcard or literal character.

INSERT INTO Calendar (Occurs, User, Note) VALUES ('*', '*', '*')
 

[Rabbit]

As far as escaping quotes goes, assume someone has the name O'Connor. What the string would look like then is
'O'Connor'

I'm sure you can see how that would mess up the SQL. So to escape the single quote, you need to do this.
'O''Connor'

The resulting string looks fine. Did you try running it in Access?

[Joshua Grow]

It has been resolved! It was something to do with the declaration! Lucky guess (or not so lucky considering it took almost 7 hours of failure!!!) But thank you anyway! See below for final code.

Dim rs As New OleDb.OleDbCommand(
                "INSERT INTO tblCalendar ([Occurs], [User], [Note]) VALUES ('" &
                dtpCalendar.Value & "','" &
                txtUserName.Text & "','" &
                txtCalendarEntry.Text & "')", con)