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,
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,
VB.NET:
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