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

Question

I get this SQL Server error and I can't figure out where the trouble is:

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.

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

Source Error: Line: 46

Error Line: cmdsql.ExecuteNonQuery()

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 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

Answer 1

Keep your database objects local so you can be sure they are closed and disposed. Enclosing these objects with `Using...End Using blocks will accomplish this even if there is an error. You don't need variables for DataAdapters, DataSets, or DataReaders. I suggest only one form level variable for the Excel connection string since it is used in 2 methods.

A little bit of Linq will get the retrieved sheet names from the DataTable and fill an array. The array can then be passed to the list box with .AddRange.

I wouldn't use the SelectedIndexChanged event because the user can too easily click the wrong sheet or change their mind. I used a Button.Click event to fill the grid.

The Sql connection string looks strange to me. I suggest you test it separately. If it doesn't work, this is a good resource. https://www.connectionstrings.com/

I would specifically state the column names in the Insert statement. Replace FirstColumnName and SecondColumnName with the real column names. The parameter names can be anything you wish as long as the names in the statement match the names in the Parameters.Add method. I have guessed at the datatypes and the size. Check your database for correct values.

We add the parameters only once outside the loop then change only values inside the loop.

Private ExcelConString As String

Private Sub BtnImpExcelFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnImpExcelFile.Click
    Dim strFileName As String
    Dim dtSheets As DataTable
    OpenFileDialog1.Filter = "(* .xls) | * .xls | (*. Xlsx) | *. xlsx | All files (*. *) | *. * "
    OpenFileDialog1.ShowDialog()
    strFileName = OpenFileDialog1.FileName
    ExcelConString = "provider = Microsoft.ace.OLEDB.12.0; data source =" & strFileName & "; Extended Properties = Excel 8.0;"
    Using connexcel = New OleDbConnection(ExcelConString)
        connexcel.Open()
        dtSheets = connexcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
    End Using
    Dim exSheets() As Object = (From dRow In dtSheets.AsEnumerable() Select dRow("TABLE_Name")).ToArray
    ExcelSheetList.Items.AddRange(exSheets)
End Sub

Private Sub DisplayData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DisplayData.Click
    Dim dt As New DataTable
    Using cn As New OleDbConnection(ExcelConString)
        'In older versions of Visual Studio you may have to use String.Format instead of the interpolated string.
        Using cmd As New OleDbCommand($"select * from [{ExcelSheetList.Text}];", cn)
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    DGVImpData.DataSource = dt
    DGVImpData.ReadOnly = True
End Sub

Private Sub BtnSaveImpData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSaveImpData.Click
    Using cn As New SqlConnection("data source =. \ MSSMLBIZ; initial catalog = MyInvoice; integrated security = true")
        Using cmd As New SqlCommand("Insert Into InvoiceData (FirstColumnName, SecondColumnName) Values ​​(@FirstColumn, @SecondColumn);", cn)
            cmd.Parameters.Add("@FirstColumn", SqlDbType.VarChar, 100)
            cmd.Parameters.Add("@SecondColumn", SqlDbType.VarChar, 100)
            cn.Open()
            For line As Integer = 0 To DGVImpData.RowCount - 2
                cmd.Parameters("@FirstColumn").Value = DGVImpData.Rows(line).Cells(0).Value
                cmd.Parameters("@SecondColumn").Value = DGVImpData.Rows(line).Cells(1).Value
                cmd.ExecuteNonQuery()
            Next
        End Using
    End Using
    MsgBox("data saved successfully")
    DGVImpData.Columns.Clear()
End Sub

As to error handling... On Error Resume Next is generally not used in new code. We have Try...Catch...Finally blocks. After your code is running add these blocks where needed.

EDIT

To use String.Format...

Using cmd As New OleDbCommand(String.Format("select * from [{0}];", ExcelSheetList.Text))

The first parameter is the string in which you wish to place variables. It contains indexed placeholders enclosed in braces. The following parameters are the variables you want for the placeholder substitution.

Answer 2

Thank you for helping me to a fixed error in my code. Here is Final code without System.Data.SqlClient.SqlException: 'Incorrect syntax near '​'.' error.

Now I tried to improve code with the last section(mention below) to define parameters for exporting data. Because I have a large number of data for exporting to SQL Server I get a Timeout error. Can anyone be able to improve code for quick exporting data to SQL Server?

connsql.Open() "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.'"

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
    'OpenFileDialog1.Filter = "(*.xls)|*.xls|(*.xlsx)|*.xlsx|All files (*.*)|*.*"
    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

'Last Section

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

Thanks for Your Help.