How to populate records from ASCII to ACCESS table? 1

[Miao]

Hi,

I tried to read all the data stored in an ASCII file and populated them into an existed ACCESS table. I used OleDBConnection and InsertCommand. However, I got an error "Expected Query name after Execute". Is there any error with my code. I've struggled with this problem for a long time. Any help would be appreciate. Thanks in advance.

My code is:

Private Sub fromASCIItoACCESS(ByVal outpath As String, ByVal mdbPath As String, ByVal selectSQL As String)
        Dim sr As StreamReader
        sr = File.OpenText(outpath)

        Dim i As Long = 1
        Dim SplitArray() As String
        Dim dataLength As Integer
        Dim Col As String
        Dim Row As String
        Dim XCorner As String
        Dim YCorner As String
        Dim CellSize As String
        Dim NoData As String
        Dim Xval As Double
        Dim Yval As Double

        Dim ColNo As Integer
        Dim RowNo As Integer
        Dim TotalRecord As Integer
        Dim x As String

        Dim colCount As Integer
        Dim rowCount As Integer

        ' Get the data Table
        Dim conn As New OleDb.OleDbConnection()
        'Dim adapter As New OleDb.OleDbDataAdapter()
        Dim ds As New DataSet()
        Dim dt As New DataTable()
        Try
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & mdbPath
                       'create the dataAdapter using the SQL string
            Dim adapter As New OleDb.OleDbDataAdapter(selectSQL, conn)


            adapter.SelectCommand = New OleDb.OleDbCommand(selectSQL, conn)
            adapter.InsertCommand = New OleDb.OleDbCommand("INSERT INTO DIOXIN(Col, Row, Longitude, Latitude, Dioxin) VALUES(@Col, @Row, @Longitude, @Latitude, @Dioxin)", conn)
            adapter.InsertCommand.CommandType = CommandType.StoredProcedure

            adapter.InsertCommand.Parameters.Add("@Col", OleDbType.Integer)
            adapter.InsertCommand.Parameters.Add("@Row", OleDbType.Integer)
            adapter.InsertCommand.Parameters.Add("@Longitude", OleDbType.Double)
            adapter.InsertCommand.Parameters.Add("@Latitude", OleDbType.Double)
            adapter.InsertCommand.Parameters.Add("@Dioxin", OleDbType.Double)

            conn.Open()
            adapter.Fill(ds, "Dioxin")


            Dim newRow As DataRow = ds.Tables("Dioxin").NewRow

            'Read each line in the file

            While sr.Peek <> -1
                If i < 7 Then
                    x = sr.ReadLine()
                    dataLength = x.Length

                    'SplitArray = Split(x, " ")
                    ' Get the information of the file such as columns, rows, cell size
                    Select Case i
                        Case 1
                            Col = x.Substring(5, dataLength - 5) 'Get the column no. 
                            'Col = SplitArray(1)
                            Col = Trim(Col)
                            MsgBox(Col)

                        Case 2
                            Row = x.Substring(5, dataLength - 5) 'Get the row no.
                            Row = Trim(Row)
                            MsgBox(Row)
                        Case 3
                            XCorner = x.Substring(9, dataLength - 9)
                            XCorner = Trim(XCorner)
                            MsgBox(XCorner)
                        Case 4
                            YCorner = x.Substring(9, dataLength - 9)
                            YCorner = Trim(YCorner)
                            MsgBox(YCorner)
                            Yval = CDbl(YCorner)
                            Yval = -Yval
                        Case 5
                            CellSize = x.Substring(8, dataLength - 8)
                            CellSize = Trim(CellSize)
                            MsgBox(CellSize)
                        Case 6
                            NoData = x.Substring(12, dataLength - 12)
                            NoData = Trim(NoData)
                            MsgBox(NoData)
                    End Select

                Else

                    ColNo = CInt(Col)

                    Xval = CDbl(XCorner)

                    'The latitude of the first row is 90.25
                    If i <> 7 Then
                        Yval = Yval - 0.5 'the latitude decrease by 0.5 degree for each cell
                    End If
                    ReDim SplitArray(ColNo)
                    x = sr.ReadLine()

                    SplitArray = Split(x, " ")
                    For colCount = 0 To ColNo - 1
                        newRow = ds.Tables("Dioxin").NewRow
                        newRow("Col") = colCount
                        newRow("Row") = RowNo
                        ' The longitude of the first column is -177.75
                        If colCount > 0 Then
                            Xval = Xval + 0.5  'the longitude increase by 0.5 degree
                        End If

                        newRow("Longitude") = Xval
                        newRow("Latitude") = Yval
                        newRow("Dioxin") = SplitArray(colCount)
                       
                        ds.Tables("Dioxin").Rows.Add(newRow)
                        

                    Next
                    RowNo = RowNo + 1

                End If

                i = i + 1

            End While
          
               
            Dim oCommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(adapter)

            adapter.Update(ds, "Dioxin")
       

            ' Tell user the operation is over and close the file.
            MsgBox("The end of the stream has been reached.")
            conn.Close()
            sr.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try


    End Sub

 

[ThatRickGuy]

Me personally, I'm a big fan of the file->Import Data->From text file option in access. (Not sure about the specific menu path, but it's up there)

-Rick

----------------------

http://www.ringdev.com

 

[Miao]

Thanks Rick,

But my text file is in Raster format which stored the information such as no. of columns and rows of the raster file and also the values of each grid cell. What I want to do now is to extract the values in each cell and then stored in the ACCESS table assoicating with its longitude, latitude, column and row. As such, I cannot simply import the file with the Import Data function in ACCESS.

Vickie

 

[ThatRickGuy]

So much for the quick easy fix What line is giving you that error?

-Rick

----------------------

http://www.ringdev.com

 

[Miao]

The line
adapter.Update(ds, "Dioxin")
has this error.

Thanks for your help.

Vickie

 

[ThatRickGuy]

not positive on this, but should this line:

adapter.InsertCommand.CommandType = CommandType.StoredProcedure

be commandtype.text?

or the query you have in the insert command would be in the database as a stored procedure and the insert command would be a call to the name of the stored procedure?

-Rick

----------------------

http://www.ringdev.com

 

[Miao]

Hi Rick,

It works perfectly after changing this line. Thanks a lot for your help.

Cheers,

Vickie