Wayne Wayne - 4 months ago 18
Vb.net Question

vb.net - SQL - Error paring the query

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

Answer

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
Comments