ExecuteNonQuery() Error

[sohraab]

Hello everyone,

I'm a beginner. I have an urgent problem to which I would appreciate quick replies.
I'm trying to read an excel file and then insert the data into a SQL table. But this error occurs:

"SqlException was unhandled: Must declare the scalar variable "@Time"."
A SqlException is generated when a warning or error is returned by SQL Server. In SQL table, all columns have a Data Type of nchar(10). Can anyone tell me why this error happens?

Thank you for your help,

Option Strict Off
Imports Microsoft.Office.Interop.Excel
Imports WindowsApplication1.Form1
Imports Microsoft.Office.Core
Imports System.Data.SqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim con As New SqlConnection
    Private cmd As SqlCommand

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sqlcommand As String
        sqlcommand = "INSERT INTO table1 VALUES (@DocumentDate,@Time,@OperationCode,@IssuingBranch,@DocumentNumber,@Liquidator,@Credit,@Debit,@NewBalance)"
        con.Open()

        Dim cmdInsert As New SqlCommand
        
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim range As Microsoft.Office.Interop.Excel.Range
        Dim rCnt As Integer
        Dim cCnt As Integer
        Dim Obj As Object

        xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("D:\school\test.xls")
        xlWorkSheet = xlWorkBook.Worksheets(1)

        range = xlWorkSheet.UsedRange

        For rCnt = 7 To range.Rows.Count
            For cCnt = 1 To 9
                Obj = CType(range.Cells(rCnt, cCnt), Microsoft.Office.Interop.Excel.Range)
                MsgBox(Obj.value)
                Select Case cCnt
                    Case 1
                        cmdInsert.Parameters.AddWithValue("@DocumentDate", Obj.value)
                    Case 2
                        cmdInsert.Parameters.AddWithValue("@Time", Obj.value)
                    Case 3
                        cmdInsert.Parameters.AddWithValue("@OperationCode", Obj.value)
                    Case 4
                        cmdInsert.Parameters.AddWithValue("@IssuingBranch", Obj.value)
                    Case 5
                        cmdInsert.Parameters.AddWithValue("@DocumentNumber", Obj.value)
                    Case 6
                        cmdInsert.Parameters.AddWithValue("@Liquidator", Obj.value)
                    Case 7
                        cmdInsert.Parameters.AddWithValue("@Credit", Obj.value)
                    Case 8
                        cmdInsert.Parameters.AddWithValue("@Debit", Obj.value)
                    Case 9
                        cmdInsert.Parameters.AddWithValue("@NewBalance", Obj.value)
                End Select
                cmdInsert.Connection = con
                cmdInsert.CommandText = sqlcommand

                cmdInsert.ExecuteNonQuery()
            Next
        Next

        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)




        con.Close()

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            'obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        con.ConnectionString = "Data Source=localhost;Initial Catalog=school;Integrated Security=True"


    End Sub


End Class

 

[jmcilhinney]

You should not be adding parameters in the loop. If you want to do it with a command and ExecuteNonQuery then you should be adding the parameters once and only once. Inside the loop, you set the Value of each parameter.

What you should be doing though is using a data adapter and Update. In your loop, populate a DataTable. Once you're done, call Update on your adapter to save the whole lot in one go.