0

Hello all, I'm trying to add a new row to a new datatable. The following is my code,but I get the error of "Cannot find the column 0." Anyone can help me on this?

Imports System.Windows.Forms
Imports System.Data
Imports System.Data.OleDb
Public Class Form1
    Dim cn As New OleDbConnection
    Dim ObjComm As New OleDbCommand
    Dim DtSet As New System.Data.DataSet
    Dim DtSet1 As New System.Data.DataSet
    Dim MyDataAdapter As New System.Data.OleDb.OleDbDataAdapter
    Dim MyDataAdapter1 As New System.Data.OleDb.OleDbDataAdapter
    Dim SQL As String
    Dim dt As New DataTable
    Dim dt1 As New DataTable
    Dim DtRow As DataRow

    Dim Open_Stock As Boolean

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\JACSON\Desktop\Phama Data\Phama.mdb;Persist Security Info=True"

        Try
            SQL = "SELECT DISTINCT(ITEM) FROM DATA"

            cn.Open()
            MyDataAdapter1 = New OleDbDataAdapter(SQL, cn)
            MyDataAdapter1.Fill(DtSet1, "Item_Code")
            dt = DtSet1.Tables("Item_Code")

            For i = 0 To dt.Rows.Count - 1
                ComboBox1.Items.Add(dt.Rows(i).Item(0))
            Next
            cn.Close()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       
        Dim i As Integer
        Dim BATCH, TRNCDJ, RESONJ, REFNOJ, INVT, CUSTCODE, CUSTNAME, ADD1, ADD2 As String
        Dim DD, MM, YY, INVNO, QTY As Integer
       
        Try

            If ComboBox1.SelectedItem <> "" Then
                cn.Open()

                MyDataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM DATA WHERE ITEM='" & ComboBox1.Text & "' ORDER BY DD,MM,YY", cn)
                MyDataAdapter.TableMappings.Add("Table", "OrderTable")
                DtSet = New System.Data.DataSet

                MyDataAdapter.Fill(DtSet)
                dt = DtSet.Tables("OrderTable")
                DataGridView1.DataSource = DtSet.Tables(0)

                cn.Close()
                For i = 0 To dt.Rows.Count - 1
                    MessageBox.Show("This is " & i & " ")

                    BATCH = DataGridView1.Item(3, i).Value
                    INVT = DataGridView1.Item(12, i).Value
                    INVNO = DataGridView1.Item(13, i).Value
                    CUSTCODE = DataGridView1.Item(14, i).Value
                    CUSTNAME = DataGridView1.Item(15, i).Value
                    ADD1 = DataGridView1.Item(16, i).Value
                    ADD2 = DataGridView1.Item(17, i).Value

                    DD = DataGridView1.Item(4, i).Value
                    MM = DataGridView1.Item(5, i).Value
                    YY = DataGridView1.Item(6, i).Value
                    QTY = DataGridView1.Item(7, i).Value
                    TRNCDJ = DataGridView1.Item(8, i).Value
                    RESONJ = DataGridView1.Item(9, i).Value
                    REFNOJ = DataGridView1.Item(10, i).Value

                    MessageBox.Show(" Hello" & TRNCDJ & "   " & RESONJ & " ")
                    DtRow = dt1.NewRow()

                    DtRow.Item(0) = DD             <<== The Error appeared when it comes to this line.
                    DtRow.Item(1) = CUSTNAME

                    dt1.Rows.Add(DtRow)

                Next i
            End If

            DataGridView2.DataSource = dt1
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

       

    End Sub

    
End Class

Thank you for reading and hope someone can provide me some advice.I would like to rebuild a new datatable and put it into a datagridview for display.

Regards
Drex

2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by __avd
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.