how to update data into the database from dynamically created controls

[moushmif]

hi,
Iam developing a application where a form displays dynamically created contols and displays data in it from the database.
this form allows the user to edit records and any changes made to it should be updated in to the database.
since the contols are dynamically created iam not able to figure out how to refer to them and save the changes.
can anyone guide me here please.i would really appreciate the help.
thanks.

[moushmif]

well to be more clearer i would like to provide more information on my form.
this form is displaying only the text boxes right now.so all the input would have to be updated from these dynamic textboxes to the database.
this is my code in the forms load event:



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 dr1 As OleDbDataReader 
        Dim str As String 
        Dim str1 As String 
        Dim cmd1 As OleDbCommand 
        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 lablsiz As Size = New Size(150, 20) 
        Dim txtsiz As Size = New Size(300, 20) 
        Dim i As Integer 
        Dim ds As DataSet 
 
        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 
 
    End Sub

so now when they make changes and hit the edit button the changes made to the text of any textboxes should be stored back to FldAn in customfieldanswers.
please help!!
thanks,

[stanav]

When you dynamically create your controls, it's a good idea to always name them so that you can refer back to it by name later.
For example, if you create a textbox in the form load event like this

Private Sub Form1_Load(ByVal sender As System.Object, _
                                 ByVal e As System.EventArgs) _
                                 Handles MyBase.Load
        Dim txtBox As TextBox
        txtBox = New TextBox
        txtBox.Name = "myTextBox"
        txtBox.Size = New Size(128, 20)
        txtBox.Location = New Point(208, 24)
        txtBox.Text = "This is a test"
        Me.Controls.Add(txtBox)
    End Sub

Then later you can access it via its name

Private Sub Button2_Click(ByVal sender As System.Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles Button2.Click
        'Loop thru the form's controls and look for the name
        For Each ctrl As Control In Me.Controls
            If ctrl.Name = "myTextBox" Then
                'If find it, you need to cast it back to whatever control it is
                'In this case, it's a TextBox
                MsgBox(CType(ctrl, TextBox).Text)
            End If
        Next
    End Sub

[moushmif]

hi,
thanks for the suggestion .so this is what i did:

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 dr1 As OleDbDataReader
        Dim str As String
        Dim str1 As String
        Dim cmd1 As OleDbCommand
        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 lablsiz As Size = New Size(150, 20)
        Dim txtsiz As Size = New Size(300, 20)
        Dim i As Integer
        Dim ds As DataSet
        '--------------
        Dim id As String
        Dim obj As Object
        Dim MyID As String
        Dim ctr As Integer
 
        '-------------------
        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
                '-----------
                ctr = 1
                '------------
                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.Name = "txtboxno" & ctr
                    '---------------------------------
                    txtBox.Location = pt
                    txtBox.Size = txtsiz
     
                    Me.Controls.Add(txtBox)
                    ctr += 1
                    cnt = cnt + (txtBox.Height + 15)
                    cnt1 = cnt1 + (labl.Height + 15)
                End While
 
            Else
                 '-----------
                ctr = 1
                '------------
                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.Name = "txtboxno" & ctr
                    '---------------------------------
                        txtBox.Location = pt
                        txtBox.Size = txtsiz
 
                        Me.Controls.Add(txtBox)
                        txtBox.Text = dr1(i)
                        ctr += 1
                        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
 
    End Sub

my thoughts:
i can write a function/procedure to update the table and call it at the button click event
since in the above code there can be anynumber of textboxes depending on the data in the table.so how can i check if the text has changed for each textbox and update it in the database?if you can give me ideas i would appreciate it.

[stanav]

Well, in that case, I would read the data from database to a dataset or datatable depending on what you need. Once you get your datasedt/datatable populated, you can always check the datatable for number of available rows/column and create your textboxes accordingly and use databind to bind the datatable to those textboxes. Now, when you want to update your database, you just need to check for the rowstate in the table and update only affected rows.
This is just an idea, I've actually never done anything like (binding datatable to dynamically created textboxes) this before... So don't yell at me if it does not work
Just currious though, why don't you use other control, like a datagrid to display the data? This would be much easier to handle in your situation.