how to update data into the database from dynamically created controls
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.
Re: how to update data into the database from dynamically created controls
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:
VB 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,
Re: how to update data into the database from dynamically created controls
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
VB Code:
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
VB Code:
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
Re: how to update data into the database from dynamically created controls
hi,
thanks for the suggestion .so this is what i did:
VB 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
'--------------
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.
Re: how to update data into the database from dynamically created controls
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.
