CODEWITHSUNDEEP
.netvb.netms-access
Tahi
Tahi

Reputation: 95

Import an Excel file into an Access database

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.

This are the fields that I have to import into the database: 

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

There may be multiple rows which is something I cannot predict.

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='" & Text1.Text & "';" & _
                 "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 > 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

I want to import the Excel data into an Access database but I am getting this error:

Upvotes: 0

Views: 108

Answers (1)

Bugs
Bugs

Reputation: 4489

Good to see you are at least attempting to use parameters. First thing I would do is prepend @ to your parameter names:

Dim dataid As New OleDbParameter With {.DbType = DbType.Int32, .ParameterName = "@dataid"}

Next thing I note is you haven't added the parameters to your OleDbCommand:

cmd.Parameters.Add(dataid)

Lastly the Insert statement doesn't look quite right. You should specify the column names in your table as well as the values you want to insert. Also change VALUE to VALUES:

"INSERT INTO TblProject (field1, field2, field3...) VALUES (@dataid, @name ,@company...)"

You would change field1, field2, field3 to the right column names and of course ensure all the column names have been specified.

With OleDbCommand it's important to note that it's not the names of the parameters but the order in which they are declared that is important.

Continuing on I would also consider implementing Using:

Sometimes your code requires an unmanaged resource, such as a file handle, a COM wrapper, or a SQL connection. A Using block guarantees the disposal of one or more such resources when your code is finished with them. This makes them available for other code to use.

Here is some sample code. Do note that I use ? as my parameter placeholders:

Using con As New OleDbConnection(connectexcelstring),
      cmd As New OleDbCommand("INSERT INTO TblProject (field1, field2, field3...) VALUES (?, ?, ?...)", con)

    'You could also add the parameters to the command at this point
    cmd.Parameters.Add("@dataid", OleDbType.Integer)
    cmd.Parameters.Add("@name", OleDbType.VarChar)
    ...

    For Each row As DataRow In dt.Rows
        Dim Col1Value As Integer
        If Integer.TryParse(row("dataid").ToString(), Col1Value) Then
            cmd.Parameters("@dataid").Value = Col1Value 'If Integer.TryParse is successful then "Col1Value" will also contain the value of "row("dataid")"
            cmd.Parameters("@name").Value = row.Item("name").ToString()
            ...

            If cmd.ExecuteNonQuery() = 0 Then
                MessageBox.Show("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 Using

As I said it's only sample code so you will have to fill in the blanks with the rest of your fields, parameters but this should get you back on your way.

Upvotes: 1

Related Questions