VB import excel to sql
I am trying to import an excel sheet to a sql database. I was able to get it to work using sqlbulkcopy. I am now trying to do it line by line. The code works fine except that only the first row is added to the database. I used a msgbox to ensure that I am iterating through all of the excel values. So I am not sure what is wrong.
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO
Imports System.Text
Module Module1
Sub Main()
Dim strSql As String = ""
strSQL = "INSERT INTO tblExcel (Name, Location) VALUES (@Name, @Location)"
'SQL Server Connection String
Dim cn As New SqlClient.SqlConnection
cn.ConnectionString = "Data Source=MyDB;Initial Catalog=DB123;Integrated Security=True"
Dim cmd As New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandType = CommandType.Text
cmd.CommandText = strSql
cn.Open()
'Connection String to Excel Workbook
开发者_如何学运维 Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\cl3j\Desktop\ExcelTest.xlsx;Extended Properties=""Excel 12.0;HDR=YES;"""
' Create Connection to Excel Workbook
Using connection As New System.Data.OleDb.OleDbConnection(excelConnectionString)
'List columns you need from the Excel file
Dim command As New System.Data.OleDb.OleDbCommand("Select [Name],[Location] FROM [Sheet1$]", connection)
connection.Open()
' Create DbDataReader to Data Worksheet
Using dr As System.Data.OleDb.OleDbDataReader = command.ExecuteReader()
If dr.HasRows() then
While dr.Read()
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Convert.ToString(dr.Item("Name"))
cmd.Parameters.Add("@Location", SqlDbType.VarChar).Value = Convert.ToString(dr.Item("Location"))
cmd.ExecuteScalar()
End While
End If
End Using
End Using
cn.Close()
cn = Nothing
End Sub
End Module
Found the problem. Need to move the cmd lines into the dr.hasrows loop. Looks like this:
If dr.HasRows() then
While dr.Read()
Dim cmd As New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandType = CommandType.Text
cmd.CommandText = strSql
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Convert.ToString(dr.Item("Name"))
cmd.Parameters.Add("@Location", SqlDbType.VarChar).Value = Convert.ToString(dr.Item("Location"))
cmd.ExecuteScalar()
End While
End If
精彩评论