title: Import an Excel file into an Access database
body: <p>I have written some code to import data from an Excel file into an Access database. The code starts with choosing a directory and then clicking a button to import.</p>
  
  <p>This are the fields that I have to import into the database: </p>
  
  <pre><code>dataid  - ID
  
  name - NAME
  
  company - COMPANY
  
  phone - PHONE NUMBER
  
  Add1 - ADDRESS 1
  
  Add2 - ADDRESS 2
  
  Add3 - ADDRESS 3
  
  pcode - POSCODE
  
  city - CITY NAME
  
  state - STATE
  
  remark - REMARK
  
  ShipperRef - REFFERENCE
  </code></pre>
  
  <p>There may be multiple rows which is something I cannot predict.</p>
  
  <p>Code:</p>
  
  <pre><code>  Private Sub TransferExcelFile2Database_Click(sender As System.Object, e As System.EventArgs) Handles TransferExcelFile2Database.Click
  
              Try
  
              Dim connectexcelstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" &amp; Text1.Text &amp; "';" &amp; _
                   "Extended Properties=""Excel 8.0;HDR=yes"""
              Dim con As OleDbConnection = New OleDbConnection(connectexcelstring)
  
              con.Open()
  
              Dim query As String = "Select * from [Sheet1$]"
  
              Dim excelcmd As OleDbCommand = New OleDbCommand(query, con)
  
  
              dt.Load(excelcmd.ExecuteReader())
  
              If dt.Rows.Count &gt; 0 Then
                  Dim conn As OleDbConnection = New OleDbConnection(constring)
                  Dim cmdAccess As New OleDbCommand
  
                  Dim dataid As New OleDbParameter With {.DbType = DbType.Int32, .ParameterName = "dataid"}
                  Dim name As New OleDbParameter With {.DbType = DbType.String, .ParameterName = "name"}
                  Dim company As New OleDbParameter With {.DbType = DbType.String, .ParameterName = "company"}
                  Dim phone As New OleDbParameter With {.DbType = DbType.String, .ParameterName = "phone"}
                  Dim Add1 As New OleDbParameter With {.DbType = DbType.String, .ParameterName = "Add1"}
                  Dim Add2 As New OleDbParameter With {.DbType = DbType.String, .ParameterName = "Add2"}
                  Dim Add3 As New OleDbParameter With {.DbType = DbType.String, .ParameterName = "Add3"}
                  Dim pcode As New OleDbParameter With {.DbType = DbType.String, .ParameterName = "pcode"}
                  Dim City As New OleDbParameter With {.DbType = DbType.String, .ParameterName = "City"}
                  Dim state As New OleDbParameter With {.DbType = DbType.String, .ParameterName = "state"}
                  Dim remark As New OleDbParameter With {.DbType = DbType.String, .ParameterName = "remark"}
                  Dim ShipperRef As New OleDbParameter With {.DbType = DbType.String, .ParameterName = "ShipperRef"}
  
                  cmdAccess.CommandText = "INSERT INTO TblProject VALUE (@dataid,@name,@company,@phone,@Add1,@Add2,@Add3,@pcode,@City,@state,@remark,@ShipperRef)"
  
                  conn.Open()
  
                  Dim Col1Value As Integer
                  Dim Result As Integer
                  For Each row As DataRow In dt.Rows
                      If Integer.TryParse(row("dataid").ToString, Col1Value) Then
                          cmdAccess.Parameters("dataid").Value = row.Item("dataid")
                          cmdAccess.Parameters("name").Value = row.Item("name")
                          cmdAccess.Parameters("company").Value = row.Item("company")
                          cmdAccess.Parameters("phone").Value = row.Item("phone")
                          cmdAccess.Parameters("Add1").Value = row.Item("Add1")
                          cmdAccess.Parameters("Add2").Value = row.Item("Add2")
                          cmdAccess.Parameters("Add3").Value = row.Item("Add3")
                          cmdAccess.Parameters("pcode").Value = row.Item("pcode")
                          cmdAccess.Parameters("city").Value = row.Item("city")
                          cmdAccess.Parameters("state").Value = row.Item("state")
                          cmdAccess.Parameters("remark").Value = row.Item("remark")
                          cmdAccess.Parameters("ShipperRef").Value = row.Item("ShipperRef")
  
                          Result = cmdAccess.ExecuteNonQuery()
                          If Result = 0 Then
                              MsgBox("Mission to insert failed")
                          End If
                      Else
                          '
                          ' Value does not represent a valid integer.
                          ' You need to decide how to handle this i.e.
                          ' Abort insert as done here or place a default value in.
                          '
                      End If
                  Next
  
              End If
  
  
          Catch ex As Exception
  
              MessageBox.Show(ex.Message)
          End Try
  
      End Sub
  </code></pre>
  
  <p>I want to import the Excel data into an Access database but I am getting this error:</p>
  
  <p><a href="https://i.stack.imgur.com/MS1yp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/MS1yp.png" alt=""></a></p>