[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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.  http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_24110069.html#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

0
wiswalld
Asked:
wiswalld
  • 10
  • 9
1 Solution
 
CodeCruiserCommented:
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.
0
 
wiswalldAuthor Commented:
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.
0
 
CodeCruiserCommented:
Where does the sample data come from? Please explain the full detail so that i may be able to help.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wiswalldAuthor Commented:
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.
0
 
CodeCruiserCommented:
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
0
 
wiswalldAuthor Commented:
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.
0
 
CodeCruiserCommented:
>"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
0
 
wiswalldAuthor Commented:
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")
0
 
wiswalldAuthor Commented:
But I will need to add the name. I am only setting the number in the code (value)

I need to manually add value1
0
 
CodeCruiserCommented:
yes. And you need to provide the connectionstring as well instead of using Database1ConnectionString.
0
 
wiswalldAuthor Commented:
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?
0
 
CodeCruiserCommented:
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
)
0
 
wiswalldAuthor Commented:
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 ')'.
0
 
CodeCruiserCommented:
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.
0
 
wiswalldAuthor Commented:
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.

0
 
wiswalldAuthor Commented:
Thanks so much for the help.
0
 
CodeCruiserCommented:
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.
0
 
wiswalldAuthor Commented:
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.
0
 
CodeCruiserCommented:
Then the option is to use the tableadapter.update(dt) format. Keep adding the user controls bound to the datatable and when user clicks on save, use the update method to perform all the insertion automatically. Can you post a screenshot?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now