OleDB Array to DBF file Problem with Parameters

fooswolf

New member
Joined
May 15, 2012
Messages
1
Location
Lincoln, NE
Programming Experience
10+
I have a subroutine that reads from Excel a table with headings and puts the headings into and array and the table values into an array. I then call the ArrytoDBF with these arrays in order to create the tempdbf.dbf database where I am going to create a table tempdbf with the headings used to create the fields and the Table array for the database records. My code below will create the table with the appropriate field headings, however no data is copied into the database. The arrays (Headings and Table) both are have data in this subroutine. The problem I believe is with how I am using the parameters, CommandType.Text, CommandType.StoredProcedure, CommandType.TableDirect.

I basically want to copy the entire array to the DBF table format. I realize that in the Parameter section I am only copying one record with all of the columns/fields. I fully expect to add another loop to copy all of the rows unless there is an easier method (CommandType.TableDirect).

Eventually I will take this DBF table and perform a merge/join with an existing ESRI Shape file using the field ID. Any help would be appreciative as I am new to database programming.
Dan
    Sub ArrytoDBF(ByVal Table As Array, ByVal Headings As Array, ByVal nrow As Integer, ByVal ncol As Integer)
        Dim path As String
        path = "C:\Users\jimhoffa\Documents\Visual Studio 2008\Projects\XLStoDBF\XLStoDBF\SHP"
        Dim objConn As New OleDbConnection()
        Dim objCmd As System.Data.OleDb.OleDbCommand
        Dim strConnString, strSQL As String
        Dim myProvider As String = "Provider=Microsoft.ACE.OLEDB.12.0"
        Dim myDataSource As String = "Data Source = " & path
        Dim myExtendedProperties As String = "Extended Properties=dBASE IV"
        Dim myUserID As String = "Admin"
        Dim myPassword As String = ""
        strConnString = myProvider & ";" & myDataSource & ";" & myExtendedProperties
        objConn.ConnectionString = strConnString
        objConn.Open()
        'CREATE NEW DBF TABLE
        Dim SQLCreateTable As String
        SQLCreateTable = "CREATE TABLE TEMPDBF (" & _
                           "FID Numeric(20,5), " & _
                           "MAP_TIP Character(50), " & _
                           "CC Numeric(11,0), " & _
                           "ID Numeric(11,0), " & _
                           "ROUTEID Numeric(11,0), " & _
                           "ROUTE Character(50), " & _
                           "NO_ASBUILT Numeric(11,0), " & _
                           "ASB_SHEET Character(20), " & _
                           "COUNTY Character(30), " & _
                           "STATE Character(2), " & _
                           "FIBERTYPE Character(50), " & _
                           "RR_ROW Character(150), " & _
                           "SECOND_ROW Character(150), " & _
                           "STA_EQU Character(30), " & _
                           "BEGINSTA1 Numeric(20,5), " & _
                           "ENDSTA1 Numeric(20,5), " & _
                           "BEGINSTA2 Numeric(20,5), " & _
                           "ENDSTA2 Numeric(20,5), " & _
                           "BEGINSTA3 Numeric(20,5), " & _
                           "ENDSTA3 Numeric(20,5), " & _
                           "BEGINSTA4 Numeric(20,5), " & _
                           "ENDSTA4 Numeric(20,5), " & _
                           "BEGINSTA5 Numeric(20,5), " & _
                           "ENDSTA5 Numeric(20,5), " & _
                           "BEGINSTA6 Numeric(20,5), " & _
                           "ENDSTA6 Numeric(20,5), " & _
                           "FIBERLENGT Numeric(20,5), " & _
                           "TOTAL Numeric(20,5), " & _
                           "FILE_NAME Character(50), " & _
                           "LINK Character(200), " & _
                           "LID Numeric(11,0), " & _
                           "ASBCNT Numeric(11,0), " & _
                           "TOTASBUILT Numeric(11,0)" & _
                           ")"
        If (System.IO.File.Exists(path & "\TEMPDBF.DBF")) Then
            System.IO.File.Delete(path & "\TEMPDBF.DBF")
        End If
        strSQL = SQLCreateTable
        objCmd = New OleDbCommand(strSQL, objConn)
        Dim strParam As String
        With objCmd
            For j = 0 To ncol - 1
                strParam = "@" & Headings(j)
                .Parameters.Add(New OleDbParameter(strParam, Table(1, j)))
            Next j
        End With
        objCmd.CommandText = strSQL
        objCmd.CommandType = CommandType.Text
        objCmd.ExecuteNonQuery()
        objConn.Close()
    End Sub
 
Back
Top