[RESOLVED] many data with few textboxs

[new1]

I want to save the data of 5 members of one family into database
like husband, wife, child1, 2, 3
with this data
name, age, job, phone, birthdate

but I want to make only 5 textbox for each filed for all the members
not 5 textbox for each filed for each member 5 x 5 = 25

and save all that one time into database is there away to do that

[jmcilhinney]

Are you saying that you want to enter the data for one person at a time into the TextBoxes or that you want to enter all the data for all the people at the same time? If it's the former then I can certainly suggest how to do that but, if it's the latter, I would recommend against using TextBoxes and use a DataGridView instead.

[new1]

I want to enter the data for one person at a time but save all the data of each person all at once

[jmcilhinney]

So then, you should start by creating a DataTable with the appropriate schema. You can build that schema yourself or you can call FileSchema on an appropriate data adapter, which will be the same data adapter that you use to save the data. You can then bind that DataTable to a BindingSource and bind that to your controls. Each time you want to add a new record you call AddNew on the BindingSource. I think that you'll need to call EndEdit to commit the previous record first and you'll definitely need to do so before saving the data to the database. You save the data by calling Update on the data adapter.

You can follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data to get examples of how to do the data access part. The data-binding will look something like this:

myBindingSource.DataSource = myDataTable
myTextBox.DataBindings.Add("Text", myBindingSource, "ColumnName")

[new1]

ok what I done so far is this

Dim adapter As New OleDb.OleDbDataAdapter("select Pname,Birthdate,Qualification,Job,Health,Tel from members", con)
        Dim insert As New OleDb.OleDbCommand("INSERT INTO members (Pname,Birthdate,Qualification,Job,Health,Tel) VALUES (@Pname,@Birthdate,@Qualification,@Job,@Health,@Tel)", con)
        insert.Parameters.AddWithValue("@Pname", Form3.TextBox4.Text.Trim)
        insert.Parameters.AddWithValue("@Birthdate", Form3.DateTimePicker2)
        insert.Parameters.AddWithValue("@Qualification", Form3.TextBox6.Text.Trim)
        insert.Parameters.AddWithValue("@Job", Form3.TextBox7.Text.Trim)
        insert.Parameters.AddWithValue("@Health", Form3.TextBox8.Text.Trim)
        insert.Parameters.AddWithValue("@Tel", Form3.TextBox10.Text.Trim)
 
        adapter.InsertCommand = insert
        Dim table As New DataTable
        adapter.FillSchema(table, SchemaType.Source)
        adapter.Update(table)

but how to do the binding for the datatable and the BindingSource also how can I insert that data for all the members into that table before I save it all at once to the database

[jmcilhinney]

It feels like you read part of my post and ignored the rest.

how to do the binding for the datatable and the BindingSource

Like I showed you in my previous post:

The data-binding will look something like this:

myBindingSource.DataSource = myDataTable
myTextBox.DataBindings.Add("Text", myBindingSource, "ColumnName")

how can I insert that data for all the members into that table before I save it all at once to the database

Like I told you in my previous post:

Each time you want to add a new record you call AddNew on the BindingSource. I think that you'll need to call EndEdit to commit the previous record first and you'll definitely need to do so before saving the data to the database.

Calling AddNew creates a new row and calling EndEdit adds it to the DataTable. If you call AddNew twice without calling EndEdit in between, I'm not sure whether the first row gets added to the DataTable automatically or discarded. You can test that easily enough for yourself. You definitely have to call EndEdit before saving or else the last row you created will not be added. The new row created by calling AddNew will automatically become the current row so it's fields will be automatically bound to the controls. You call AddNew, the user enters the field values and then you commit the row.

[new1]

dos that code make sense so far?

Dim adapter As New OleDb.OleDbDataAdapter("select ID,Pname,Birthdate,Qualification,Job,Health,Religion,Tel from members", con)
        Dim insert As New OleDb.OleDbCommand("INSERT INTO members (ID,Pname,Birthdate,Qualification,Job,Health,Religion,Tel) VALUES (@ID,@Pname,@Birthdate,@Qualification,@Job,@Health,@Religion,@Tel)", con)
        insert.Parameters.AddWithValue("@Pname", Form3.TextBox4.Text.Trim)
        insert.Parameters.AddWithValue("@Birthdate", Form3.DateTimePicker2)
        insert.Parameters.AddWithValue("@Qualification", Form3.TextBox6.Text.Trim)
        insert.Parameters.AddWithValue("@Job", Form3.TextBox7.Text.Trim)
        insert.Parameters.AddWithValue("@Health", Form3.TextBox8.Text.Trim)
        insert.Parameters.AddWithValue("@Religion", Form3.TextBox9.Text.Trim)
        insert.Parameters.AddWithValue("@Tel", Form3.TextBox10.Text.Trim)
        adapter.InsertCommand = insert
 
        Dim table As New DataTable
        adapter.FillSchema(table, SchemaType.Source)
        Form3.BindingSource1.DataSource = table
 
        Form3.TextBox4.DataBindings.Add(Form3.TextBox4.Text.Trim, Form3.BindingSource1, "Pname")
        Form3.DateTimePicker2.DataBindings.Add(Form3.DateTimePicker2.Value, Form3.BindingSource1, "Birthdate")
        Form3.TextBox6.DataBindings.Add(Form3.TextBox6.Text.Trim, Form3.BindingSource1, "Qualification")
        Form3.TextBox7.DataBindings.Add(Form3.TextBox7.Text.Trim, Form3.BindingSource1, "Job")
        Form3.TextBox8.DataBindings.Add(Form3.TextBox8.Text.Trim, Form3.BindingSource1, "Health")
        Form3.TextBox9.DataBindings.Add(Form3.TextBox9.Text.Trim, Form3.BindingSource1, "Religion")
        Form3.TextBox10.DataBindings.Add(Form3.TextBox10.Text.Trim, Form3.BindingSource1, "Tel")
 
        adapter.Update(table)

if not tell me what to modify

[jmcilhinney]

Firstly, you can't be calling FillSchema and Update in the same method. When is the user going to enter the data? You need to create the DataTable and bind it to your controls to begin with. The user is then going to enter the data, thus populating the DataTable. Finally, you'll save the data.

Secondly, you can't use AddWithValue to create the parameters. You're going to be inserting multiple records from the DataTable so that's where the data has to come from for the parameters.

Finally, where exactly is that code? If it's in Form3 then you should be using Me rather than Form3. If it's not in Form3 then you shouldn't be doing the binding there at all. Creating the DataTable and saving the data elsewhere is fine but in that case you should be passing the DataTable in to Form3 and then Form3 does its own binding.

For the first two points particularly, follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data. It includes two examples of using a data adapter to save data; one that uses a data adapter and one that doesn't. That will show you how to separate the creating of the DataTable and the saving of the data. They use Fill but you can replace that with FillSchema. Note that you also need to provide for the user creating a new row in the DataTable. I don't see any calls to AddNew or EndEdit in there.

[new1]

Secondly, you can't use AddWithValue to create the parameters. You're going to be inserting multiple records from the DataTable so that's where the data has to come from for the parameters.

so what to use

Dim adapter As New OleDb.OleDbDataAdapter("select Pname,Birthdate,Qualification,Job,Health,Religion,Tel from members", obj.con)
        Dim insert As New OleDb.OleDbCommand("INSERT INTO members (Pname,Birthdate,Qualification,Job,Health,Religion,Tel) VALUES (@ID,@Pname,@Birthdate,@Qualification,@Job,@Health,@Religion,@Tel)", obj.con)
        adapter.InsertCommand = insert
        insert.Parameters.Add("@Pname", OleDbType.VarChar, 100, "Pname")
        insert.Parameters.Add("@Birthdate", OleDbType.DBDate, 10, "Birthdate")
        insert.Parameters.Add("@Qualification", OleDbType.VarChar, 100, "Qualification")
        insert.Parameters.Add("@Job", OleDbType.VarChar, 100, "Job")
        insert.Parameters.Add("@Health", OleDbType.VarChar, 100, "Health")
        insert.Parameters.Add("@Religion", OleDbType.VarChar, 100, "Religion")
        insert.Parameters.Add("@Tel", OleDbType.VarChar, 100, "Religion")
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
        Dim table As New DataTable
        table.Columns.Add("Pname", GetType(String))
        table.Columns.Add("Birthdate", GetType(DateAndTime))
        table.Columns.Add("Qualification", GetType(String))
        table.Columns.Add("Job", GetType(String))
        table.Columns.Add("Health", GetType(String))
        table.Columns.Add("Religion", GetType(String))
        table.Columns.Add("Tel", GetType(Integer))
 
        adapter.FillSchema(table, SchemaType.Source)
 
        BindingSource1.DataSource = table
        TextBox4.DataBindings.Add(TextBox4.Text.Trim, BindingSource1, "Pname")
        DateTimePicker2.DataBindings.Add(DateTimePicker2.Value, BindingSource1, "Birthdate")
        TextBox6.DataBindings.Add(TextBox6.Text.Trim, BindingSource1, "Qualification")
        TextBox7.DataBindings.Add(TextBox7.Text.Trim, BindingSource1, "Job")
        TextBox8.DataBindings.Add(TextBox8.Text.Trim, BindingSource1, "Health")
        TextBox9.DataBindings.Add(TextBox9.Text.Trim, BindingSource1, "Religion")
        TextBox10.DataBindings.Add(TextBox10.Text.Trim, BindingSource1, "Tel")
 
        adapter.Update(table)

that's what I did so far but I don't know how to add new row with bindingsource since every row will continue the person name, birthday, Qualification, job, health, Religion, tel

can you show me how to do that with th ebindingsource

[jmcilhinney]

Your parameters look better now. You can only use AddWithValue if you want to add the parameter with a specific value. You obviously don't because you don't have any values at that point and each parameter is going to have multiple values because you want to insert multiple rows. You have to use Add to create the parameter and to tell it where in the DataTable to get the data from for each row that's inserted, as you have now done.

To balance that improvement in your code, you've also made it worse in another way. Why are you adding columns to your DataTable manually and then calling FillSchema?

Next, your bindings are an issue. Have you read the documentation to see what the parameters of the DataBindings.Add method mean? Your first parameter is very wrong.

Also, you're still calling Update in the same method that creates the DataTable. As I have already stated, those must be separated. Update is what saves the data to the database so calling it before you've actually added any data to the DataTable is obviously of no value. Step 1 is creating and binding the DataTable, step 2 is adding the data and step three is saving the data. You can't do step 1 and step 3 in a single method.

Finally, I've already told you several times that you add a new row by calling AddNew on the BindingSource. That will only help, of course, if the BindingSource is bound correctly.

[new1]

is the code right so far?

Dim adapter As New OleDb.OleDbDataAdapter("select Pname,Birthdate,Qualification,Job,Health,Religion,Tel from members", obj.con)
        Dim insert As New OleDb.OleDbCommand("INSERT INTO members (Pname,Birthdate,Qualification,Job,Health,Religion,Tel) VALUES (@ID,@Pname,@Birthdate,@Qualification,@Job,@Health,@Religion,@Tel)", obj.con)
        adapter.InsertCommand = insert
        insert.Parameters.Add("@Pname", OleDbType.VarChar, 100, "Pname")
        insert.Parameters.Add("@Birthdate", OleDbType.DBDate, 10, "Birthdate")
        insert.Parameters.Add("@Qualification", OleDbType.VarChar, 100, "Qualification")
        insert.Parameters.Add("@Job", OleDbType.VarChar, 100, "Job")
        insert.Parameters.Add("@Health", OleDbType.VarChar, 100, "Health")
        insert.Parameters.Add("@Religion", OleDbType.VarChar, 100, "Religion")
        insert.Parameters.Add("@Tel", OleDbType.VarChar, 100, "Religion")
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
        Dim table As New DataTable
        adapter.FillSchema(table, SchemaType.Source)
        BindingSource1.DataSource = table
 
        TextBox4.DataBindings.Add(TextBox4.Text.Trim, BindingSource1, "Pname")
        DateTimePicker2.DataBindings.Add(DateTimePicker2.Value, BindingSource1, "Birthdate")
        TextBox6.DataBindings.Add(TextBox6.Text.Trim, BindingSource1, "Qualification")
        TextBox7.DataBindings.Add(TextBox7.Text.Trim, BindingSource1, "Job")
        TextBox8.DataBindings.Add(TextBox8.Text.Trim, BindingSource1, "Health")
        TextBox9.DataBindings.Add(TextBox9.Text.Trim, BindingSource1, "Religion")
        TextBox10.DataBindings.Add(TextBox10.Text.Trim, BindingSource1, "Tel")

you said

Next, your bindings are an issue. Have you read the documentation to see what the parameters of the DataBindings.Add method mean? Your first parameter is very wrong.

but you mentioned before that DataBindings.Add is to add new column so how can it be wrong

[jmcilhinney]

is the code right so far?

Dim adapter As New OleDb.OleDbDataAdapter("select Pname,Birthdate,Qualification,Job,Health,Religion,Tel from members", obj.con)
        Dim insert As New OleDb.OleDbCommand("INSERT INTO members (Pname,Birthdate,Qualification,Job,Health,Religion,Tel) VALUES (@ID,@Pname,@Birthdate,@Qualification,@Job,@Health,@Religion,@Tel)", obj.con)
        adapter.InsertCommand = insert
        insert.Parameters.Add("@Pname", OleDbType.VarChar, 100, "Pname")
        insert.Parameters.Add("@Birthdate", OleDbType.DBDate, 10, "Birthdate")
        insert.Parameters.Add("@Qualification", OleDbType.VarChar, 100, "Qualification")
        insert.Parameters.Add("@Job", OleDbType.VarChar, 100, "Job")
        insert.Parameters.Add("@Health", OleDbType.VarChar, 100, "Health")
        insert.Parameters.Add("@Religion", OleDbType.VarChar, 100, "Religion")
        insert.Parameters.Add("@Tel", OleDbType.VarChar, 100, "Religion")
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
        Dim table As New DataTable
        adapter.FillSchema(table, SchemaType.Source)
        BindingSource1.DataSource = table

That part is correct, up to the data bindings.

you mentioned before that DataBindings.Add is to add new column so how can it be wrong

I never mentioned that because it's not the case. The DataBindings.Add method is to add a Binding, which binds a column/property of the data source to a property of the control. As I said, your first parameter is wrong. Again I ask, have you read the documentation for that method? If not, why not? If you have then you should know what that first parameter is for and why it's wrong.

[new1]

no I didn't read documentation for that method yet..hope you can provide me with a link for that method

[jmcilhinney]

no I didn't read documentation for that method yet..hope you can provide me with a link for that method

What's wrong with your VS Help menu?

[new1]

how about the code now?

Dim adapter As New OleDb.OleDbDataAdapter("select Pname,Birthdate,Qualification,Job,Health,Religion,Tel from members", obj.con)
        Dim insert As New OleDb.OleDbCommand("INSERT INTO members (Pname,Birthdate,Qualification,Job,Health,Religion,Tel) VALUES (@ID,@Pname,@Birthdate,@Qualification,@Job,@Health,@Religion,@Tel)", obj.con)
        adapter.InsertCommand = insert
        insert.Parameters.Add("@Pname", OleDbType.VarChar, 100, "Pname")
        insert.Parameters.Add("@Birthdate", OleDbType.DBDate, 10, "Birthdate")
        insert.Parameters.Add("@Qualification", OleDbType.VarChar, 100, "Qualification")
        insert.Parameters.Add("@Job", OleDbType.VarChar, 100, "Job")
        insert.Parameters.Add("@Health", OleDbType.VarChar, 100, "Health")
        insert.Parameters.Add("@Religion", OleDbType.VarChar, 100, "Religion")
        insert.Parameters.Add("@Tel", OleDbType.VarChar, 100, "Religion")
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
        Dim table As New DataTable
        adapter.FillSchema(table, SchemaType.Source)
        BindingSource1.DataSource = table
 
        TextBox4.DataBindings.Add("text", BindingSource1, "Pname")
        DateTimePicker2.DataBindings.Add("Value", BindingSource1, "Birthdate")
        TextBox6.DataBindings.Add("Text", BindingSource1, "Qualification")
        TextBox7.DataBindings.Add("text", BindingSource1, "Job")
        TextBox8.DataBindings.Add("text", BindingSource1, "Health")
        TextBox9.DataBindings.Add("text", BindingSource1, "Religion")
        TextBox10.DataBindings.Add("text", BindingSource1, "Tel")

[jmcilhinney]

That looks pretty good for the first part, i.e. creating the DataTable and binding it. Just note that you're going to have to use the data adapter again later to save the data, so you'll have to assign it to a member variable rather than a local variable.

Now, as I've said previously, you'll need to call AddNew on the BindingSource each time you want to add a new row. That will most likely be done in the Click event of a Button as the user will have to initiate it because only they know when they're done with the previous record. As I have also said, I'm not sure whether calling AddNew commits the previous edit or discards it, so you'll have to conform that. It's easy enough to work out because if you call AddNew a few times and end up with no rows in your DataTable then obviously nothing has been committed. In that case, you'll need an explicit call to the EndEdit method of the BindingSource before each call to AddNew.

Finally, as I have also already said, you'll need to call EndEdit to commit the last row and then Update on the data adapter to save the data. That will again most likely be done in a Button's Click event handler because only the user knows when they are ready to save the data.

[new1]

ok here's the code

Dim adapter As New OleDb.OleDbDataAdapter("select Pname,Birthdate,Qualification,Job,Health,Religion,Tel from members", obj.con)
        Dim insert As New OleDb.OleDbCommand("INSERT INTO members (Pname,Birthdate,Qualification,Job,Health,Religion,Tel) VALUES (@ID,@Pname,@Birthdate,@Qualification,@Job,@Health,@Religion,@Tel)", obj.con)
        adapter.InsertCommand = insert
        insert.Parameters.Add("@Pname", OleDbType.VarChar, 100, "Pname")
        insert.Parameters.Add("@Birthdate", OleDbType.DBDate, 10, "Birthdate")
        insert.Parameters.Add("@Qualification", OleDbType.VarChar, 100, "Qualification")
        insert.Parameters.Add("@Job", OleDbType.VarChar, 100, "Job")
        insert.Parameters.Add("@Health", OleDbType.VarChar, 100, "Health")
        insert.Parameters.Add("@Religion", OleDbType.VarChar, 100, "Religion")
        insert.Parameters.Add("@Tel", OleDbType.VarChar, 100, "Religion")
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
        Dim table As New DataTable
        adapter.FillSchema(table, SchemaType.Source)
        BindingSource1.DataSource = table
 
        TextBox4.DataBindings.Add("text", BindingSource1, "Pname")
        DateTimePicker2.DataBindings.Add("Value", BindingSource1, "Birthdate")
        TextBox6.DataBindings.Add("Text", BindingSource1, "Qualification")
        TextBox7.DataBindings.Add("text", BindingSource1, "Job")
        TextBox8.DataBindings.Add("text", BindingSource1, "Health")
        TextBox9.DataBindings.Add("text", BindingSource1, "Religion")
        TextBox10.DataBindings.Add("text", BindingSource1, "Tel")

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        BindControls()
        BindingSource1.AddNew()
        BindingSource1.EndEdit()
        DataGridView1.DataSource = BindingSource1
    End Sub

but the DGV only show empty row when I fill the textboxes with data and click the button the DGV show empty row why?

[KGComputers]

but the DGV only show empty row when I fill the textboxes with data and click the button the DGV show empty row why?

Make sure that the BindingSource.DataSource has records and that your DataGridView control has columns.
MSDN DataGridView.AutoGenerateColumns Property

KGC

[new1]

any one have an idea for how to get this done

[jmcilhinney]

Your second code snippet in post #17 doesn't really make sense. You call a method named "BindControls" and then a couple of lines later you bind a DataGridView. Why isn't that grid being bound with the other controls? What is Button1 actually for? Just like everything else in that code, it has a meaningless name. Is it being clicked once or every time the user adds a new row? If it's the latter then why would you be binding controls every time? Why are you calling EndEdit immediately after AddNew? How many times can I say the same thing and it be ignored?

you'll need an explicit call to the EndEdit method of the BindingSource before each call to AddNew.

Why is this the first we're hearing of a DataGridView too? What's the deal here - are you expecting the user to enter the field values in the TextBoxes and then they appear in the grid? That's probably information that should have been provided before now.

[new1]

well what I do is fill the textboxs with the data then call BindControls() to fill the table with those data so I made the DGV to see if the data is stored in the table or not and it just show me empty rows which mean no data from the textboxes have been saved to the table

the DGV is just for the test as well as the button and I click it once to save the data from the textboxs to the table but it didn't work

and I don't understand what you quite mean by that

you'll need an explicit call to the EndEdit method of the BindingSource before each call to AddNew.

isn't that what I did by calling BindingSource1.EndEdit()

[new1]

ok I fix the code it work fine now although there's one last thing

DateTimePicker2.DataBindings.Add("Value", BindingSource1, "Birthdate")

it doesn't add new record because of that line
but when I change it to

DateTimePicker2.DataBindings.Add("text", BindingSource1, "Birthdate")

it work fine..
so will that effect on saving it to the DB since it's Date & time Data type in the DB table
and there away to fix that

[jmcilhinney]

I don't understand what you quite mean by that


isn't that what I did by calling BindingSource1.EndEdit()

Do you not understand the difference between "before" and "after"? I said, over and over, to call EndEdit before calling AddNew. You're calling EndEdit after calling AddNew. That's a fairly fundamental difference. Calling EndEdit ends the current edit. The point of it is to end the editing of the previous row before adding a new one. You're ending the edit immediately after adding a row, so the row is obviously going to be empty.

[new1]

ok I fix the code it work fine now although there's one last thing

DateTimePicker2.DataBindings.Add("Value", BindingSource1, "Birthdate")

it doesn't add new record because of that line
but when I change it to

DateTimePicker2.DataBindings.Add("text", BindingSource1, "Birthdate")

it work fine..
so will that effect on saving it to the DB since it's Date & time Data type in the DB table
and there away to fix that

[jmcilhinney]

What data type is that Birthdate column?

[new1]

it's Date/Time in the database, but I'm not saving it yet
it just doesn't add those data to the bindingsource or to the table

DateTimePicker2.DataBindings.Add("Value", BindingSource1, "Birthdate")
 
19.        TextBox6.DataBindings.Add("Text", BindingSource1, "Qualification")
 
20.        TextBox7.DataBindings.Add("text", BindingSource1, "Job")
 
21.        TextBox8.DataBindings.Add("text", BindingSource1, "Health")
 
22.        TextBox9.DataBindings.Add("text", BindingSource1, "Religion")
 
23.        TextBox10.DataBindings.Add("text", BindingSource1, "Tel")

unless I change it from value to text

[vprog]

you will be needing only 5 text boxes for name, age, job, birthdate only. then add all the members to textboxes one-by-one.

[new1]

you will be needing only 5 text boxes for name, age, job, birthdate only. then add all the members to textboxes one-by-one.

that's is what I'm trying to do