Link to home
Start Free TrialLog in
Avatar of wiswalld
wiswalldFlag for United States of America

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("tblofficer")
        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("tblofficer")
        Dim rows() As DataRow
        Dim AutoTextbox = Me.FlowLayoutPanel1.Controls.Item(AutoTextbox)
        Dim OfficerTextbox = Me.FlowLayoutPanel1.Controls.Item(OfficerTextbox)
        rows = dt1.Tables("tblofficer").Select("Auto = '" & 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
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

Open in new window

Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of wiswalld

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.
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.
You have to use plain ADO.NET objects to save data to the database. For example.

dim dbcon as new sqlconnection("connectionstring")
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
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("tblofficer")
        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.
>"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("tblofficer")
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("connectionstring")
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
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("Database1ConnectionString")
But I will need to add the name. I am only setting the number in the code (value)

I need to manually add value1
yes. And you need to provide the connectionstring as well instead of using Database1ConnectionString.
I declared

Public connectionString As String = My.Settings.Database1ConnectionString

and used

 Dim dbcon As New SqlConnection("connectionString")
        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("connectionString")
to
 Dim dbcon As New SqlConnection(connectionString)
or
 Dim dbcon As New SqlConnection(My.Settings.Database1ConnectionString
)
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 ')'.
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.
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.

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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial