none
System.Data.SqlClient.SqlException: 'Incorrect syntax near '​'.' RRS feed

  • Question

  • Hey everyone. I received this sql server error and I can't figure out where the trouble is:

    Name Value Type
    $exception {"Incorrect syntax near '​'."} System.Data.SqlClient.SqlException

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: 
    System.Data.SqlClient.SqlException: 'Incorrect syntax near '​'.'

    Source Error:  Line: 46

    Error Line:  cmdsql.ExecuteNonQuery()

    Dim connexcel As OleDbConnection
        Dim daexcel As OleDbDataAdapter
        Dim dsexcel As DataSet
        Dim cmdexcel As OleDbCommand
        Dim drexcel As OleDbDataReader
    
        Dim connsql As SqlConnection
        Dim dasql As SqlDataAdapter
        Dim dssql As DataSet
        Dim cmdsql As SqlCommand
        Dim drsql As SqlDataReader
    
        Private Sub import_excel_to_sql_server_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Me.CenterToScreen()
        End Sub
    
        Private Sub BtnImpExcelFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnImpExcelFile.Click
            On Error Resume Next
            OpenFileDialog1.Filter = "(* .xls) | * .xls | (*. Xlsx) | *. xlsx | All files (*. *) | *. * "
                   OpenFileDialog1.ShowDialog()
            FileAdd.Text = OpenFileDialog1.FileName
            connexcel = New OleDbConnection("provider = Microsoft.ace.OLEDB.12.0; data source =" & FileAdd.Text & "; Extended Properties = Excel 8.0;")
            connexcel.Open()
    
            Dim dtSheets As DataTable = connexcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            Dim listSheet As New List(Of String)
            Dim drSheet As DataRow
    
            For Each drSheet In dtSheets.Rows
                listSheet.Add(drSheet("TABLE_NAME").ToString())
            Next
    
            For Each sheet As String In listSheet
                ExcelSheetList.Items.Add(sheet)
            Next
        End Sub
    
        Private Sub ExcelSheetList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExcelSheetList.SelectedIndexChanged
            daexcel = New OleDbDataAdapter("select * from [" & ExcelSheetList.Text & "]", connexcel)
            dsexcel = New DataSet
            daexcel.Fill(dsexcel)
            DGVImpData.DataSource = dsexcel.Tables(0)
            DGVImpData.ReadOnly = True
        End Sub
    
        Sub connections()
            connsql = New SqlConnection("data source =. \ MSSMLBIZ; initial catalog = MyInvoice; integrated security = true")
            connsql.Open()
        End Sub
    
        Private Sub BtnSaveImpData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSaveImpData.Click
            For line As Integer = 0 To DGVImpData.RowCount - 2
                Call connections()
                Dim save As String = "insert into InvoiceData values ​​('" & DGVImpData.Rows(line).Cells(0).Value & "', '" & DGVImpData.Rows(line).Cells(1).Value & "')"
            cmdsql = New SqlCommand(save, connsql)
                cmdsql.ExecuteNonQuery()
            Next
            MsgBox("data saved successfully")
            DGVImpData.Columns.Clear()
        End Sub

    Can anyone help? I'm not sure '".' the error is referring to.

    Thanks! 

    Friday, September 20, 2019 8:28 PM

All replies

  • See if this fixes things by using this which removes an unwanted space.

    Sub connections()
    connsql = New SqlConnection("data source =.\MSSMLBIZ; initial catalog = MyInvoice; integrated security = true")
    connsql.Open()
    End Sub


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, September 20, 2019 8:53 PM
    Moderator
  • Hello,

    Just checking in to see if you can connect to your database with the new connection string.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, September 21, 2019 1:14 AM
    Moderator
  • Hi Karen,

    Thank you for solution. I made change as per your solution but error still there. It's not help me get out from this problem.


    • Edited by Faizan R Saturday, September 21, 2019 5:54 PM
    Saturday, September 21, 2019 5:54 PM
  • If the error occurs at ExecuteNonQuery line, then give details about the save string, or use Parameterised Queries to avoid possible problems.

    Sunday, September 22, 2019 5:16 PM
  • Hi Karen,

    Thank you for solution. I made change as per your solution but error still there. It's not help me get out from this problem.


    A sure fire method to get a proper connection string

    • With Visual Studio open.
    • Select view menu, select "SQL Server Explorer"
    • If there are no "Data Connections" click the add button
    • A dialog appears (see below), populate "Server name" and "Connect to database".
    • Test the connection with the "Test Connection" button
    • Press OK.
    • The server is now visible in the server explorer.
    • Select the database.
    • Select property window.
    • Copy the "Connection String"

    If you made it to the last bullet (and tested the connection) the copied connection string can be used for the connection string in your VB.NET project.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, September 22, 2019 11:13 PM
    Moderator
  • Hi Karen Payne, 

    Thanks for your help. Your last answer work perfectly and i'm able to remove error from code and data export successfully into Sql Server, but i have little bit more problem face in my code. Error Line "System.InvalidOperationException: 'Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.'"

    Would you help me to write to define parameter for data exporting or any other tricks to import quickly data before timeout expired.

    Final Code

        Dim connexcel As OleDbConnection
        Dim daexcel As OleDbDataAdapter
        Dim dsexcel As DataSet
        Dim cmdexcel As OleDbCommand
        Dim drexcel As OleDbDataReader
    
        Dim connsql As SqlConnection
        Dim dasql As SqlDataAdapter
        Dim dssql As DataSet
        Dim cmdsql As SqlCommand
        Dim drsql As SqlDataReader
    
        Private Sub Import_excel_to_sql_server_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Me.CenterToScreen()
        End Sub
    
        Private Sub PKGAbtnImpExcelFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PKGAbtnImpExcelFile.Click
            On Error Resume Next
            
            PKGAofdImpOpenExcel.ShowDialog()
            PKGAtxtImpFileAdd.Text = PKGAofdImpOpenExcel.FileName
            connexcel = New OleDbConnection("provider=Microsoft.ace.OLEDB.12.0;data source=" & PKGAtxtImpFileAdd.Text & ";Extended Properties=Excel 8.0;")
            connexcel.Open()
    
    
            Dim dtSheets As DataTable = connexcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            Dim listSheet As New List(Of String)
            Dim drSheet As DataRow
    
            For Each drSheet In dtSheets.Rows
                listSheet.Add(drSheet("TABLE_NAME").ToString())
            Next
    
            For Each sheet As String In listSheet
                PKGAtxtImpExlSheetL.Items.Add(sheet)
            Next
        End Sub
    
        Private Sub PKGAtxtImpExlSheetL_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PKGAtxtImpExlSheetL.SelectedIndexChanged
            daexcel = New OleDbDataAdapter("select * from [" & PKGAtxtImpExlSheetL.Text & "]", connexcel)
            dsexcel = New DataSet
            daexcel.Fill(dsexcel)
            PKGAdgvImpData.DataSource = dsexcel.Tables(0)
            PKGAdgvImpData.ReadOnly = True
        End Sub
    
        Sub Connectonsql()
            connsql = New SqlConnection("Data Source=DESKTOP-MIQGJTK\MSSMLBIZ;Initial Catalog=PkGlobalAccounting;Integrated Security=True")
            connsql.Open()
        End Sub
    
        Private Sub PKGAbtnImpSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PKGAbtnImpSave.Click
            For Line As Integer = 0 To PKGAdgvImpData.RowCount - 2
                Call Connectonsql()
                Dim save As String = "insert into Test values('" & PKGAdgvImpData.Rows(Line).Cells(0).Value & "','" & PKGAdgvImpData.Rows(Line).Cells(1).Value & "')"
                cmdsql = New SqlCommand(save, connsql)
                cmdsql.ExecuteNonQuery()
            Next
            MsgBox("Data Saved Successfully")
            PKGAdgvImpData.Columns.Clear()
    
        End Sub


    • Edited by Faizan R Sunday, September 29, 2019 11:32 AM
    Sunday, September 29, 2019 11:32 AM
  • Hello,

    There are several improvements that should be made.

    • Create connections once when needed as shown below
    • Use Try-Catch rather than On Error Resume Next
    • Never iterate a DataGridView rows and cells when the .DataSource is set as in this case to a DataTable, instead cast the DataSource to a DataTable e.g. Dim dt = CType(PKGAdgvImpData.DataSource,DataTable)
    • Always consider using a class to perform data operations when possible, I will focus on one aspect here.

    Here is a class which in the form gets setup as follows Dim ops = New Operations() then call InsertData as follows 

    Dim dt = CType(PKGAdgvImpData.DataSource,DataTable)

    Dim ops = New Operations()

    ops.InsertData(dt)

    Here is the full definition of the class.

    Imports System.Data.SqlClient
    
    Public Class Operations
        Private ConnectionString As String =
                    "Data Source=DESKTOP-MIQGJTK\MSSMLBIZ;" &
                    "Initial Catalog=PkGlobalAccounting;" & "
                    Integrated Security=True"
    
        Public Sub InsertData(dt As DataTable)
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
    
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO Test VALUES (@P1,@P2)"
    
                    '
                    ' If one or the other column is not string then adjust .DbType, same
                    ' goes for in the for-each, adjust from Field(Of String to Field(Of whatever
                    '
                    cmd.Parameters.Add(New SqlParameter() With
                                          {
                                              .ParameterName = "@P1",
                                              .DbType = DbType.String
                                          })
    
                    cmd.Parameters.Add(New SqlParameter() With
                                          {
                                              .ParameterName = "@P2",
                                              .DbType = DbType.String
                                          })
    
                    Try
    
                        cn.Open()
    
                        For Each row As DataRow In dt.Rows
                            cmd.Parameters(0).Value = row.Field(Of String)("Field1")
                            cmd.Parameters(1).Value = row.Field(Of String)("Field2")
                            Console.WriteLine(cmd.ExecuteNonQuery())
                        Next
    
                    Catch ex As Exception
                        ' decide how to handle for now 
                        Console.WriteLine(ex.Message)
                    End Try
    
                End Using
            End Using
        End Sub
    End Class
    
    
     

    Note that I create the connection once so that alone may solve the issue. Notice the use of parameters, this is very important for many reasons, the first, when dealing with strings if a value has a single apostrophe e.g. Karen's when passed to the INSERT statement will be transformed to 'Karen's' and raise a runtime exception as it needs to be 'Karen''s' which using parameters will automatically do for you. In general it's rare for many yet when it happens and not knowing about parameters causes grief and extra coding.

    I setup parameters in this case to type string, adjust if the columns are not string.

    The Console.WriteLine should be done differently (see the following but also note it's going to be a learning curve and noted here for consideration)

    In closing, you may not want to adapt all the above but at least consider one connection left open as done above.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, September 29, 2019 2:04 PM
    Moderator