开发者

VB import excel to sql

开发者 https://www.devze.com 2023-02-16 14:33 出处:网络
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

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
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号