getting data in dynamic controls through msaccess database

[lanmou]

hi,
i am creating a form in my application which dynamically creates controls by getting information from a table in ms access database .now i would like it to get the text by using another table .
my code:

 Private Sub frmDataEntry_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\customer.mdb")
        Dim cn1 As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\customer.mdb")
        Dim cmd As OleDbCommand
        Dim dr As OleDbDataReader
        Dim str As String
        Dim txtBox As TextBox
        Dim labl As Label
        Dim pt As Point
        Dim pt1 As Point
        Dim cnt As Integer
        Dim cnt1 As Integer
        Dim siz As Size = New Size(200, 20)
        Dim i As Integer
        Dim ds As DataSet
        Dim pbndTemp As Binding
 
        Try
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
 
            Dim ad As New OleDbDataAdapter("SELECT FldAn FROM tblCustomFieldAnswers WHERE AcctNum='" & m_stracctnum1 & "' And CustID = " & m_intcustnum1 & "", cn)
            ds = New DataSet
            ad.Fill(ds, "tblCustomFieldAnswers")
 
            str = "SELECT FieldName FROM tblCustomFields WHERE AcctNum='" & m_stracctnum1 & "'And CustID = " & m_intcustnum1 & ""
            cmd = New OleDbCommand(str, cn)
            dr = cmd.ExecuteReader
 
            While dr.Read
 
                pt = New Point(140, cnt + 10)
                pt1 = New Point(10, cnt1 + 10)
                txtBox = New TextBox
                txtBox.Location = pt
                txtBox.Size = siz
                Me.Controls.Add(txtBox)
 
                pbndTemp = New Binding("text", ds, "tblCustomFieldAnswers.FldAn")
                txtBox.DataBindings.Add(pbndTemp)
 
                labl = New Label
                labl.Text = dr(i)
                labl.Location = pt1
                labl.Size = siz
                Me.Controls.Add(labl)
 
                cnt = cnt + (txtBox.Height + 15)
                cnt1 = cnt1 + (txtBox.Height + 15)
 
            End While
 
        Catch ex As OleDbException
 
            MessageBox.Show(ex.ToString())
 
        End Try
        If cn.State <> ConnectionState.Closed Then
            cn.Close()
        End If
 
    End Sub

iam able to get text in the controls but it only reads one row and shows the same data in all the controls.
how should i loop it so that it reads throught the entire table and displays data from all the rows into the controls?

[lanmou]

hi,
i tried doing this. but now it only displays the data in the last control but all other controls are empty.can anyone help?

While dr.Read
 
                pt1 = New Point(10, cnt1 + 10)
 
                labl = New Label
                labl.Text = dr(i) & ":"
                labl.TextAlign = ContentAlignment.MiddleRight
                labl.Location = pt1
                labl.Size = lablsiz
                Me.Controls.Add(labl)
 
                pt = New Point(175, cnt + 10)
                txtBox = New TextBox
                txtBox.Location = pt
                txtBox.Size = txtsiz
                Me.Controls.Add(txtBox)
 
                cnt1 = cnt1 + (labl.Height + 15)
                cnt = cnt + (txtBox.Height + 15)
            End While
 
            '-------------------------------
            If cn1.State = ConnectionState.Closed Then
                cn1.Open()
            End If
 
            str1 = "SELECT FldAn FROM tblCustomFieldAnswers WHERE AcctNum='" & m_stracctnum1 & "' And CustID = " & m_intcustnum1 & ""
            cmd1 = New OleDbCommand(str1, cn1)
            dr1 = cmd1.ExecuteReader
 
            While dr1.Read
 
 
                txtBox.Text = dr1(i)
 
            End While

[lanmou]

got it done.

 Try
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
 
            cn1.Open()
            str1 = "SELECT FldAn FROM tblCustomFieldAnswers WHERE AcctNum='" & m_stracctnum1 & "' And CustID = " & m_intcustnum1 & ""
            cmd1 = New OleDbCommand(str1, cn1)
            dr1 = cmd1.ExecuteReader
 
 
            str = "SELECT FieldName FROM tblCustomFields WHERE AcctNum='" & m_stracctnum1 & "'And CustID = " & m_intcustnum1 & ""
            cmd = New OleDbCommand(str, cn)
            dr = cmd.ExecuteReader
 
 
            If dr1.HasRows = False Then
 
                While dr.Read
                    pt1 = New Point(10, cnt1 + 10)
                    labl = New Label
                    labl.Text = dr(i) & ":"
                    labl.TextAlign = ContentAlignment.MiddleRight
                    labl.Location = pt1
                    labl.Size = lablsiz
                    Me.Controls.Add(labl)
                    pt = New Point(175, cnt + 10)
                    txtBox = New TextBox
                    txtBox.Location = pt
                    txtBox.Size = txtsiz
                    Me.Controls.Add(txtBox)
 
                    cnt = cnt + (txtBox.Height + 15)
                    cnt1 = cnt1 + (labl.Height + 15)
                End While
 
            Else
                While dr.Read
                    pt1 = New Point(10, cnt1 + 10)
                    labl = New Label
                    labl.Text = dr(i) & ":"
                    labl.TextAlign = ContentAlignment.MiddleRight
                    labl.Location = pt1
                    labl.Size = lablsiz
                    Me.Controls.Add(labl)
                    While dr1.Read
                        pt = New Point(175, cnt + 10)
                        txtBox = New TextBox
                        txtBox.Location = pt
                        txtBox.Size = txtsiz
                        Me.Controls.Add(txtBox)
                        txtBox.Text = dr1(i)
                        cnt = cnt + (txtBox.Height + 15)
                    End While
                    cnt1 = cnt1 + (labl.Height + 15)
                End While
            End If
 
        Catch ex As OleDbException
 
            MessageBox.Show(ex.ToString())
 
        End Try
        If cn.State <> ConnectionState.Closed Then
            cn.Close()
        End If
        If cn1.State <> ConnectionState.Closed Then
            cn1.Close()
        End If