wiswalld
asked on
Add and save with user control
This is a follow-up question to an earlier question. I have a user control on a form that I populate one for each record. Works great. https://www.experts-exchange.com/questions/24110069/Create-custom-user-control.html?anchorAnswerId=23553906#a23553906
How do I add a new one and save changes?
I got this in a crude attempt.
Add:
Dim value As String
Dim value1 As String
Dim dt As DataTable = Database1DataSet.Tables("t blofficer" )
value = dt.Rows.Count + 1
dt.Rows.Add()
FlowLayoutPanel1.Controls. Add(New UserControl1(value, value1))
Save:
Dim dt1 As DataSet
Dim dt As DataTable = Database1DataSet.Tables("t blofficer" )
Dim rows() As DataRow
Dim AutoTextbox = Me.FlowLayoutPanel1.Contro ls.Item(Au toTextbox)
Dim OfficerTextbox = Me.FlowLayoutPanel1.Contro ls.Item(Of ficerTextb ox)
rows = dt1.Tables("tblofficer").S elect("Aut o = '" & AutoTextbox.Text & "' And Officer = '" & Officertextbox.Text & "'")
If rows.Length = 0 Then
Dim row1 As DataRow = dt.NewRow()
row1.Item(0) = AutoTextbox
row1.Item(1) = Officertextbox
End If
Attached is the original code from the first post that works
How do I add a new one and save changes?
I got this in a crude attempt.
Add:
Dim value As String
Dim value1 As String
Dim dt As DataTable = Database1DataSet.Tables("t
value = dt.Rows.Count + 1
dt.Rows.Add()
FlowLayoutPanel1.Controls.
Save:
Dim dt1 As DataSet
Dim dt As DataTable = Database1DataSet.Tables("t
Dim rows() As DataRow
Dim AutoTextbox = Me.FlowLayoutPanel1.Contro
Dim OfficerTextbox = Me.FlowLayoutPanel1.Contro
rows = dt1.Tables("tblofficer").S
If rows.Length = 0 Then
Dim row1 As DataRow = dt.NewRow()
row1.Item(0) = AutoTextbox
row1.Item(1) = Officertextbox
End If
Attached is the original code from the first post that works
Public Class UserControl1
Public Sub New(ByVal value As String, ByVal value1 As String)
' This call is required by the Windows Form Designer.
InitializeComponent()
AutoTextBox.Text = value
OfficerTextBox.Text = value1
End Sub
End Class
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim value As String
Dim value1 As String
Dim dt As DataTable = Database1DataSet.Tables("tblofficer")
For Each dRow As DataRow In dt.Rows
value = dRow(0).ToString
value1 = dRow(1).ToString
FlowLayoutPanel1.Controls.Add(New UserControl1(value, value1))
Next
End Sub
What is the scenario? What is the outcome of the code you have given? Why are you supplying an empty string (Value1) to the usercontrol? Why are you adding empty rows(dt.Rows.Add()) to the table? (value = dt.Rows.Count + 1) should be (value = dt.Rows.Count) in my opinion because rows index starts at 0. Please describe the problem.
ASKER
I think the problem is I need an event handler for each instance of the user control. When I open the form I get the flowlayout panel populated with 6 records (sample data). I click button 2 to add a new user control (where I add 1 to the last). I want to save that newly created record.
Where does the sample data come from? Please explain the full detail so that i may be able to help.
ASKER
Sample data comes from an MDF File
What I have with some help in the earlier posting is a form that has a flowlayout panel on it. When the form opens it populates the flowpayout panel with user controls for each record in the database. Right now the data is just an autonumber and name for test purposes. So on my form I want to add a new usercontrol without closing the form, populate it with my data (new record) and save so the next time this opens it shows 7 records instead of 6.
What I have with some help in the earlier posting is a form that has a flowlayout panel on it. When the form opens it populates the flowpayout panel with user controls for each record in the database. Right now the data is just an autonumber and name for test purposes. So on my form I want to add a new usercontrol without closing the form, populate it with my data (new record) and save so the next time this opens it shows 7 records instead of 6.
You have to use plain ADO.NET objects to save data to the database. For example.
dim dbcon as new sqlconnection("connections tring")
dbcon.open
dim dbcmd as new sqlcommand
dbcmd.connection=dbcon
dbcmd.commandtext="Insert Into [Table]([ColumnName]) Values ('" & txtname.text & "')"
dbcmd.executenonquery()
dbcmd.dispose
dbcon.close
dbcon.dispose
dim dbcon as new sqlconnection("connections
dbcon.open
dim dbcmd as new sqlcommand
dbcmd.connection=dbcon
dbcmd.commandtext="Insert Into [Table]([ColumnName]) Values ('" & txtname.text & "')"
dbcmd.executenonquery()
dbcmd.dispose
dbcon.close
dbcon.dispose
ASKER
The problem I see is how do I know which record to save.
Example data
1
Smith
2
Jones
3
Williams
So I want to add:
4
Paul
The first 3 are already save I just need to save number 4.
I add number 4 by using
Dim value As String
Dim value1 As String
Dim dt As DataTable = Database1DataSet.Tables("t blofficer" )
value = dt.Rows.Count + 1
dt.Rows.Add()
FlowLayoutPanel1.Controls. Add(New UserControl1(value, value1))
Thats why I was thinking an event handler. No idea how to create one though.
Example data
1
Smith
2
Jones
3
Williams
So I want to add:
4
Paul
The first 3 are already save I just need to save number 4.
I add number 4 by using
Dim value As String
Dim value1 As String
Dim dt As DataTable = Database1DataSet.Tables("t
value = dt.Rows.Count + 1
dt.Rows.Add()
FlowLayoutPanel1.Controls.
Thats why I was thinking an event handler. No idea how to create one though.
>"So I want to add:"
So you know which one you are adding!!!
You have written this code, change it to the following
Dim value As String
Dim value1 As String
Dim dt As DataTable = Database1DataSet.Tables("t blofficer" )
value = dt.Rows.Count + 1
dim dRow as DataRow=dt.NewRow()
dRow(0)=Value
dRow(1)=Value1
dt.Rows.Add(dRow)
FlowLayoutPanel1.Controls. Add(New UserControl1(value, value1))
Just add the following code after this
dim dbcon as new sqlconnection("connections tring")
dbcon.open
dim dbcmd as new sqlcommand
dbcmd.connection=dbcon
dbcmd.commandtext="Insert Into [tblOfficer]([Id], [Name]) Values (" & value & ", '" & value1 & "')"
dbcmd.executenonquery()
dbcmd.dispose
dbcon.close
dbcon.dispose
So you know which one you are adding!!!
You have written this code, change it to the following
Dim value As String
Dim value1 As String
Dim dt As DataTable = Database1DataSet.Tables("t
value = dt.Rows.Count + 1
dim dRow as DataRow=dt.NewRow()
dRow(0)=Value
dRow(1)=Value1
dt.Rows.Add(dRow)
FlowLayoutPanel1.Controls.
Just add the following code after this
dim dbcon as new sqlconnection("connections
dbcon.open
dim dbcmd as new sqlcommand
dbcmd.connection=dbcon
dbcmd.commandtext="Insert Into [tblOfficer]([Id], [Name]) Values (" & value & ", '" & value1 & "')"
dbcmd.executenonquery()
dbcmd.dispose
dbcon.close
dbcon.dispose
ASKER
What is this
Format of the initialization string does not conform to specification starting at index 0.
on this line
Dim dbcon As New SqlConnection("Database1Co nnectionSt ring")
Format of the initialization string does not conform to specification starting at index 0.
on this line
Dim dbcon As New SqlConnection("Database1Co
ASKER
But I will need to add the name. I am only setting the number in the code (value)
I need to manually add value1
I need to manually add value1
yes. And you need to provide the connectionstring as well instead of using Database1ConnectionString.
ASKER
I declared
Public connectionString As String = My.Settings.Database1Conne ctionStrin g
and used
Dim dbcon As New SqlConnection("connectionS tring")
dbcon.Open()
Dim dbcmd As New SqlCommand
dbcmd.Connection = dbcon
dbcmd.CommandText = "Insert Into [tblOfficer]([Id], [Name]) Values (" & value & ", '" & value1 & "')"
dbcmd.ExecuteNonQuery()
dbcmd.Dispose()
dbcon.Close()
dbcon.Dispose()
Will this work?
Public connectionString As String = My.Settings.Database1Conne
and used
Dim dbcon As New SqlConnection("connectionS
dbcon.Open()
Dim dbcmd As New SqlCommand
dbcmd.Connection = dbcon
dbcmd.CommandText = "Insert Into [tblOfficer]([Id], [Name]) Values (" & value & ", '" & value1 & "')"
dbcmd.ExecuteNonQuery()
dbcmd.Dispose()
dbcon.Close()
dbcon.Dispose()
Will this work?
variables are not enclosed in quotation marks
change this line
Dim dbcon As New SqlConnection("connectionS tring")
to
Dim dbcon As New SqlConnection(connectionSt ring)
or
Dim dbcon As New SqlConnection(My.Settings. Database1C onnectionS tring
)
change this line
Dim dbcon As New SqlConnection("connectionS
to
Dim dbcon As New SqlConnection(connectionSt
or
Dim dbcon As New SqlConnection(My.Settings.
)
ASKER
Just a couple of thoughts. This code so far is in the create new button. So I create the new user control and save. But I will need to save after I add the name manually. Also the Auto is a unique identifier so I don't believe I need to save the value back it should create one for me (I think). So I shortened the update line to
dbcmd.CommandText = "Insert Into [tblOfficer]([Auto], [Officer]) Values (" & value1 & ")"
I get an error on this line
dbcmd.ExecuteNonQuery()
Incorrect syntax near ')'.
dbcmd.CommandText = "Insert Into [tblOfficer]([Auto], [Officer]) Values (" & value1 & ")"
I get an error on this line
dbcmd.ExecuteNonQuery()
Incorrect syntax near ')'.
Change the line to this
dbcmd.CommandText = "Insert Into [tblOfficer]([Officer]) Values ('" & value1 & "')"
Notice that the quotes after and before the ( are ' then " and " then ' . All the string values going into database need to be enclosed in single quotes for example
Insert Into tblOffice(name) Values('CodeCruiser')
If you need to take the name and save it then you would first add just the user control and when user enters the name then save it. You have multiple choices for that. Either use the dataadapter.update(dt) or keep track of new controls and save manually.
dbcmd.CommandText = "Insert Into [tblOfficer]([Officer]) Values ('" & value1 & "')"
Notice that the quotes after and before the ( are ' then " and " then ' . All the string values going into database need to be enclosed in single quotes for example
Insert Into tblOffice(name) Values('CodeCruiser')
If you need to take the name and save it then you would first add just the user control and when user enters the name then save it. You have multiple choices for that. Either use the dataadapter.update(dt) or keep track of new controls and save manually.
ASKER
Can you explain this a little further or an example
If you need to take the name and save it then you would first add just the user control and when user enters the name then save it. You have multiple choices for that. Either use the dataadapter.update(dt) or keep track of new controls and save manually.
The first part works great. Creates a new user control with the Auto filled in. Just need to manually enter a name and save.
If you need to take the name and save it then you would first add just the user control and when user enters the name then save it. You have multiple choices for that. Either use the dataadapter.update(dt) or keep track of new controls and save manually.
The first part works great. Creates a new user control with the Auto filled in. Just need to manually enter a name and save.
ASKER
Thanks so much for the help.
Why dont you use a text box next to the button where user can enter the name and press the button. On button press, save the name to database and create the user account.
ASKER
The whole point behind this is to create a scheduling program. I want to be able to add a new name (user control). I just thought this way would be more user friendly. I like the layout.
That way would work or even with a pop up form I could add the name then save.
That way would work or even with a pop up form I could add the name then save.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.