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