SQL statement syntax error

[youngnoviceinneedofh]

Hi Guys, just trying to save some information to excel, have set up the excel worksheet and Im trying to write to it using:

Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
        conn1.Open()
        Dim cmd As New System.Data.OleDb.OleDbCommand()
        cmd.Connection = conn1
        cmd.CommandText = "INSERT INTO [Failed$] (OrderTime, Phone, MessageDetails) values ('" & Order_date & "','" & incomingtextno & "','" & textmsg & "')"
        cmd.ExecuteNonQuery()
        conn1.Close()

However I get
Syntax error (missing operator) in query expression ''Hi Guys ')'.
Where Hi Guys is the contents of textmsg.
I think i understand the problem is because it thinks that the bracket is part of the expression or something? what have i done wrong in the syntax?
thanks

[stanav]

Show us the actual query string (with the values in place - not the variable names). You can put a breakpoint on the line cmd.ExecuteNonQuery and when you debug run your app, copy the value of cmd.CommandText in the watch window and paste it here (or you can do debug.print(cmd.CommandText) then copy that)

[youngnoviceinneedofh]

ye here it is:
INSERT INTO [Failed$] (OrderTime, Phone, MessageDetails) values ('07/09/2011 18:28:05','+4477256*****','Hey guys ')
I *'d out my number...

[youngnoviceinneedofh]

could it be that the string has spaces or anything?

[kareninstructor]

Try using parameters as shown below

Public ExcelProvider As String = "Microsoft.Jet.OLEDB.4.0"
Public ExcelVersion As String = "Excel 8.0"
Public Sub InsertNewRow(ByVal FileName As String, ByVal SheetName As String)
    Dim cn As New OleDbConnection With _
    { _
       .ConnectionString = _
          String.Format("provider={1};Data Source={0};Extended Properties=""{2}; HDR=Yes;""", _
                        FileName, ExcelProvider, ExcelVersion) _
    }

    Dim cmd = New OleDbCommand() With {.Connection = cn}
    cmd.CommandText = String.Format("INSERT INTO [{0}$] (OrderTime, Phone, MessageDetails) VALUES (@OrderTime, @Phone, @MessageDetails)", SheetName)
    cmd.Parameters.AddWithValue("@OrderTime", "07/09/2011 18:28:05")
    cmd.Parameters.AddWithValue("@Phone", "+4477256")
    cmd.Parameters.AddWithValue("@MessageDetails", "Hey guys")

    cn.Open()

    Try
        cmd.ExecuteNonQuery()
    Catch OleDbExceptionErr As OleDbException
        MessageBox.Show(OleDbExceptionErr.Message)
    End Try
End Sub

[GaryMazzone]

The issue is the extra single quotes in the string. When the SQL parser runs into them it things that is the end of the value. But there is more so there is a SQL error. There are two ways to fix. The first (not the perfered) is to use a replace function like this:

cmd.CommandText = "INSERT INTO [Failed$] (OrderTime, Phone, MessageDetails) values ('" & Order_date & "','" & incomingtextno & "','" & textmsg.Replace("'","''") & "')"

The second way and the perfered is to use parameters in the command object.

[youngnoviceinneedofh]

Sorry I dont see why the textmsg part is any different to the Orderdate part or the incomingtextno part. why is the textmsg part causing a problem?

[stanav]

The query string looks OK to me... I don't know if that trailing blank space has any effects but you can always test it out by removing it, right?

[youngnoviceinneedofh]

yeah could it be that?
oh, not so straight forward, I was going to do a .repalce("vbcrlf", "") but that will take them all out. is there a way to remove the last character in one command?

[stanav]

The issue is the extra single quotes in the string. When the SQL parser runs into them it things that is the end of the value. But there is more so there is a SQL error. There are two ways to fix. The first (not the perfered) is to use a replace function like this:

cmd.CommandText = "INSERT INTO [Failed$] (OrderTime, Phone, MessageDetails) values ('" & Order_date & "','" & incomingtextno & "','" & textmsg.Replace("'","''") & "')"

The second way and the perfered is to use parameters in the command object.

That was what I thought too, but his actual query (post # 3) doesn't show any extra single quotes though. I'm puzzled by this

[kareninstructor]

In regards to a trailing space I tested for this in my reply using parameters

cmd.Parameters.AddWithValue("@MessageDetails", "Hey guys  ")

Then

cmd.Parameters.AddWithValue("@MessageDetails", "Hey guys")

Both inserted just fine.

Now if one of the columns DataType was set in Excel that could be an issue. For instance +4477256******** works fine without formatting but change the column to numeric it will fail.

[stanav]

yeah could it be that?
oh, not so straight forward, I was going to do a .repalce("vbcrlf", "") but that will take them all out. is there a way to remove the last character in one command?

Use String.Trim, String.TrimStart or String.TrimEnd to trim white spaces off your string.

[kareninstructor]

The following shows results from using parameters. The first row has spaces and a line feed, second has line feed embedded while the third has the cell set to wrap. All inserts worked so the provider is handling things differently is my guess between a parmeterized vs non-parameterized SQL INSERT statement.

[Ram2Curious]

Parameters will be the best solution. The ' " & textmsg & " ' will work properly if you try to insert just Hi Guys, but will not work if you try to insert Hi Guys ' which is followed by a single quote. Like suggested earlier, Replace function should work in this case.

Try to insert some text with a single quote and without a single quote. Test it.

I tested it on my old projects where i did not use parameters or replace function, it exactly throws the same error !.

[youngnoviceinneedofh]

I'm not trying to pass any ' to the excel, if this is whats going on i have missed it. I am only interested in passing the text

[si_the_geek]

You should always use parameters.

If you did, this problem would almost certainly disappear with no effort - as would many other problems.

As several people have told you in this thread (and in previous ones), you should be using parameters. If you don't, you just create more problems for yourself.

For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).