title: VB import excel to sql
body: <p>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.</p>
  
  <pre><code>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
  </code></pre>