vb.net - SQL - Error paring the query

Question

I received this SQL error in my AutoCAD plug-in application. “[Token line number =1, Token line offset = 110, Token in error = desc]”. I step through the table pulling the dbText information and pass it into my database. Based on some posts I’ve attempt adding [] around the table name and @ before the values but neither solution worked.

        Using myTrans As Transaction = myDB.TransactionManager.StartTransaction
        Dim myLayerTable As LayerTable = myDB.LayerTableId.GetObject(OpenMode.ForRead)
        For Each myLayerID As ObjectId In myLayerTable
            Dim myLayer As LayerTableRecord = myLayerID.GetObject(OpenMode.ForRead)
            If myLayer <> Nothing Then
                Dim name As String = myLayer.Name,
                    isoff As Boolean = myLayer.IsOff,
                    frozen As Boolean = myLayer.IsFrozen,
                    locked As Boolean = myLayer.IsLocked,
                    color As String = myLayer.Color.ColorNameForDisplay,
                    linetype As String = myLayer.LinetypeObjectId.ToString,
                    lineweight As String = myLayer.LineWeight.ToString,
                    transparency As String = myLayer.Transparency.ToString,
                    plotstyle As String = myLayer.PlotStyleNameId.ToString,
                    isplottable As Boolean = myLayer.IsPlottable,
                    vv As Boolean = myLayer.ViewportVisibilityDefault,
                    desc As String = myLayer.Description
                Dim nd As String = "INSERT INTO layers (name, isoff, frozen, locked, color, linetype, lineweight, transparency, isplottable, vv, desc) " & _
                                                            "VALUES (" & _
                                                            "'" & name & "', " & _
                                                            "'" & isoff & "', " & _
                                                            "'" & frozen & "', " & _
                                                            "'" & locked & "', " & _
                                                            "'" & color & "', " & _
                                                            "'" & linetype & "', " & _
                                                            "'" & lineweight & "', " & _
                                                            "'" & transparency & "', " & _
                                                            "'" & isplottable & "', " & _
                                                            "'" & vv & "', " & _
                                                            "'" & desc & "')"
                CeCon.DataUpdate(nd)
            End If
        Next
        myTrans.Abort()
    End Using
Private CeCon As New SqlCeConnection("Data Source=D:\Documents\Test.sdf;Persist Security Info=False;")
Private CeCmd As SqlCeCommand
Public CeDA As SqlCeDataAdapter
Public CeDT As DataTable
Public Params As New List(Of SqlCeParameter)
Public RecordCount As Integer
Public Exception As String
Public Sub ExecQuery(Query As String)
    '   Reset query stats
    RecordCount = 0
    Exception = ""
    Try
        CeCon.Open()
        CeCmd = New SqlCeCommand(Query, CeCon)
        Params.ForEach(Sub(p) CeCmd.Parameters.Add(p))
        Params.Clear()
        CeDT = New DataTable
        CeDA = New SqlCeDataAdapter(CeCmd)
        RecordCount = CeDA.Fill(CeDT)
    Catch ex As Exception
        Exception = ex.Message
    Finally
        If CeCon.State = ConnectionState.Open Then CeCon.Close()
    End Try
End Sub
Public Function DataUpdate(Command As String) As Integer
    Try
        CeCon.Open()
        CeCmd = New SqlCeCommand(Command, CeCon)
        Dim ChangeCount As Integer = CeCmd.ExecuteNonQuery
        CeCon.Close()
        Return ChangeCount
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    If CeCon.State = ConnectionState.Open Then CeCon.Close()
    Return 0
End Function

What do you think will happen if any of your variables contains a single quote? — Steve, Jul 21 '16 at 19:20
What is the error message and what does "paring the query" mean? Also, ticks are not all purpose SQL delimiters. You have what appears to be numbers and booleans forced to string in that mess. — Ňɏssa Pøngjǣrdenlarp, Jul 21 '16 at 19:23
By the way, DESC is a reserved keyword in any SQL database of this world. You need square brackets around that word. [desc] or better change that name to something less problematic — Steve, Jul 21 '16 at 19:25
None of the variables contain a single quote. The error message is “There was an error parsing the query. [Token line number = 1,Token offset 110,Token in error = desc ]” — , Jul 21 '16 at 19:27

score 0 · Accepted Answer · edited May 23 '17 at 11:58

DESC is a reserved keyword in any SQL database of this world. You need square brackets around that word. [desc] or better change that name to something less problematic.

And this is not the only problem in your code. If any of those variables contains a single quote an invalid sql command is produced by concatenation of strings. (I let you discover by yourself the problems connected to Sql Injection that could simply destroy your database)

So you need a parameterized query

Dim nd As String = "INSERT INTO layers 
    (name, isoff, frozen, locked, color, linetype, lineweight, 
     transparency, isplottable, vv, [desc]) 
    VALUES (@name, @isoff, @frozen, @locked, @color, @linetype, @lineweight, 
            @transparency, @isplottable, @vv, @desc)"
Dim prms = new List(Of SqlCeParameter)()
prms.Add(new SqlCeParameter("@name", SqlDbType.NVarChar) With {.Value = name})
prms.Add(new SqlCeParameter("@isoff", SqlDbType.Bit) With {.Value = isoff})
prms.Add(new SqlCeParameter("@frozen", SqlDbType.Bit) With {.Value = frozen})
prms.Add(new SqlCeParameter("@locked", SqlDbType.Bit) With {.Value = locked})
prms.Add(new SqlCeParameter("@color", SqlDbType.NVarChar) With {.Value = color})
prms.Add(new SqlCeParameter("@linetype", SqlDbType.NVarChar) With {.Value = linetype})
prms.Add(new SqlCeParameter("@lineweight", SqlDbType.NVarChar) With {.Value = lineweight})
prms.Add(new SqlCeParameter("@transparency", SqlDbType.NVarChar) With {.Value = transparency})
prms.Add(new SqlCeParameter("@isplottable", SqlDbType.Bit) With {.Value = isplottable})
prms.Add(new SqlCeParameter("@vv", SqlDbType.Bit) With {.Value = vv})
prms.Add(new SqlCeParameter("@desc", SqlDbType.NVarChar) With {.Value = desc})
CeCon.DataUpdate(nd, prms)

This means that you need also to change the DataUpdate procedure to receive the list of parameters values

Public Function DataUpdate(Command As String, prms As List(Of SqlCeParameter)) As Integer
    Try
        CeCon.Open()
        CeCmd = New SqlCeCommand(Command, CeCon)
        if prms Is Not Nothing Then
            CeCmd.Parameters.AddRange(prms.ToArray())
        End if
        Dim ChangeCount As Integer = CeCmd.ExecuteNonQuery
        CeCon.Close()
        Return ChangeCount
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    If CeCon.State = ConnectionState.Open Then CeCon.Close()
    Return 0
End Function

vb.net - SQL - Error paring the query

1 Answers1