jackjohnson44
asked on
vb.net dataadapter update and datagridview
I am populating a datagridview with a db, then I type in a new row, then try to update.
How do I get this to work?
This does not work:
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
dataAdapter.Update(myDataS et)
End Sub
This fills my datagrid:
Private Sub refreshDataGRid()
dataAdapter.SelectCommand = New OleDbCommand("select * from users", myConnection)
Dim CB As OleDbCommandBuilder
CB = New OleDbCommandBuilder(dataAd apter)
dataAdapter.UpdateCommand = CB.GetUpdateCommand()
dataAdapter.Fill(myDataSet )
myDataSet = getDataSet("select * from users")
myDataSet.Tables(0).TableN ame = "users"
myDataTable = myDataSet.Tables("users")
DataGridView1.DataSource = myDataTable
DataGridView1.Columns("use rName").He aderText = "User Name"
DataGridView1.Columns("pas s").Header Text = "Password"
If Not DataGridView1.Columns.Cont ains("Dele te") Then
Dim buttons As New DataGridViewButtonColumn()
With buttons
.HeaderText = "Delete"
.Text = "Delete"
.Name = "Delete"
.UseColumnTextForButtonVal ue = True
.AutoSizeMode = DataGridViewAutoSizeColumn Mode.Displ ayedCells
.FlatStyle = FlatStyle.Standard
.DisplayIndex = 0
End With
DataGridView1.Columns.Add( buttons)
End If
End Sub
How do I get this to work?
This does not work:
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
dataAdapter.Update(myDataS
End Sub
This fills my datagrid:
Private Sub refreshDataGRid()
dataAdapter.SelectCommand = New OleDbCommand("select * from users", myConnection)
Dim CB As OleDbCommandBuilder
CB = New OleDbCommandBuilder(dataAd
dataAdapter.UpdateCommand = CB.GetUpdateCommand()
dataAdapter.Fill(myDataSet
myDataSet = getDataSet("select * from users")
myDataSet.Tables(0).TableN
myDataTable = myDataSet.Tables("users")
DataGridView1.DataSource = myDataTable
DataGridView1.Columns("use
DataGridView1.Columns("pas
If Not DataGridView1.Columns.Cont
Dim buttons As New DataGridViewButtonColumn()
With buttons
.HeaderText = "Delete"
.Text = "Delete"
.Name = "Delete"
.UseColumnTextForButtonVal
.AutoSizeMode = DataGridViewAutoSizeColumn
.FlatStyle = FlatStyle.Standard
.DisplayIndex = 0
End With
DataGridView1.Columns.Add(
End If
End Sub
Use
myDataSet .AcceptChanges()
'after calling the adapter update command for commiting it
Try It
myDataSet .AcceptChanges()
'after calling the adapter update command for commiting it
Try It
manch, the dataadapter by default calls acceptchanges on update, unless he changed it manually there's no need to do that.
ASKER
newyuppie, can you please explain what you are doing?
1. are you creating a new table with nothing in it, and the dataadapter knows how to fill it?
2. What is wrong with the way that I did it? Can you please explain why this is wrong?
3. I am not sure what is going on, but I tried to do this, type something into the grid, then call dataAdapter.Update(myDataS et) and it does not fail. Then I close the app, reopen it and fill the grid and it does not have the new row. Do I have to have an insert id? I tried leaving it blank and also filling in the next number and nothing happened.
1. are you creating a new table with nothing in it, and the dataadapter knows how to fill it?
2. What is wrong with the way that I did it? Can you please explain why this is wrong?
3. I am not sure what is going on, but I tried to do this, type something into the grid, then call dataAdapter.Update(myDataS
ASKER
here is what I have so far
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<users>
<ID>52</ID>
<userName>admin</userName>
<pass>admin</pass>
<level>3</level>
<active>true</active>
</users>
<users>
<ID>55</ID>
<userName>test</userName>
<pass>test</pass>
<level>1</level>
<active>true</active>
</users>
</NewDataSet>
Private Sub refreshDataGRid()
dataAdapter.SelectCommand = New OleDbCommand("select * from users", myConnection)
Dim CB As OleDbCommandBuilder
CB = New OleDbCommandBuilder(dataAd apter)
dataAdapter.UpdateCommand = CB.GetUpdateCommand()
dataAdapter.Fill(myDataSet )
'myDataSet = getDataSet("select * from users")
'myDataSet.Tables(0).Table Name = "users"
'myDataTable = myDataSet.Tables("users")
'DataGridView1.DataSource = myDataTable
myDataTable = New DataTable("users")
myDataSet.Tables.Add(MyDat aTable)
dataAdapter.Fill(MyDataTab le)
DataGridView1.DataSource = MyDataTable
DataGridView1.Columns("use rName").He aderText = "User Name"
DataGridView1.Columns("pas s").Header Text = "Password"
end sub
this does nothing, (I have tried all of these, also leaving them out, and different combinations)
DataGridView1.Update()
myDataSet.AcceptChanges()
dataAdapter.Update(myDataS et)
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<users>
<ID>52</ID>
<userName>admin</userName>
<pass>admin</pass>
<level>3</level>
<active>true</active>
</users>
<users>
<ID>55</ID>
<userName>test</userName>
<pass>test</pass>
<level>1</level>
<active>true</active>
</users>
</NewDataSet>
Private Sub refreshDataGRid()
dataAdapter.SelectCommand = New OleDbCommand("select * from users", myConnection)
Dim CB As OleDbCommandBuilder
CB = New OleDbCommandBuilder(dataAd
dataAdapter.UpdateCommand = CB.GetUpdateCommand()
dataAdapter.Fill(myDataSet
'myDataSet = getDataSet("select * from users")
'myDataSet.Tables(0).Table
'myDataTable = myDataSet.Tables("users")
'DataGridView1.DataSource = myDataTable
myDataTable = New DataTable("users")
myDataSet.Tables.Add(MyDat
dataAdapter.Fill(MyDataTab
DataGridView1.DataSource = MyDataTable
DataGridView1.Columns("use
DataGridView1.Columns("pas
end sub
this does nothing, (I have tried all of these, also leaving them out, and different combinations)
DataGridView1.Update()
myDataSet.AcceptChanges()
dataAdapter.Update(myDataS
this is what you should have:
Private Sub refreshDataGRid()
dataAdapter.SelectCommand = New OleDbCommand("select * from users", myConnection)
Dim CB As OleDbCommandBuilder
CB = New OleDbCommandBuilder(dataAd apter)
myDataTable = New DataTable("users")
myDataSet.Tables.Add(MyDat aTable)
dataAdapter.Fill(MyDataTab le)
DataGridView1.DataSource = MyDataTable
DataGridView1.Columns("use rName").He aderText = "User Name"
DataGridView1.Columns("pas s").Header Text = "Password"
end sub
private sub ButtonSave_Click(sender as object, e as eventargs) handles ButtonSave.Click
dataAdapter.Update(MyDataS et)
End SUb
you will need to add a button called ButtonSave, so that it will save when you press it.
Private Sub refreshDataGRid()
dataAdapter.SelectCommand = New OleDbCommand("select * from users", myConnection)
Dim CB As OleDbCommandBuilder
CB = New OleDbCommandBuilder(dataAd
myDataTable = New DataTable("users")
myDataSet.Tables.Add(MyDat
dataAdapter.Fill(MyDataTab
DataGridView1.DataSource = MyDataTable
DataGridView1.Columns("use
DataGridView1.Columns("pas
end sub
private sub ButtonSave_Click(sender as object, e as eventargs) handles ButtonSave.Click
dataAdapter.Update(MyDataS
End SUb
you will need to add a button called ButtonSave, so that it will save when you press it.
You need to be very careful when calling AcceptChanges, or it will change the row status of the rows to Unmodified, and nothing will happen if you call it before OleDbDataAdapter.Update.
Bob
Bob
ASKER
newyuppie, that did not work.
I am getting this error: "Update unable to find TableMapping['Table'] or DataTable 'Table'."
Is the problem that you are binding the datagridview to a table, and then trying to update the dataset?
DataGridView1.DataSource = MyDataTable
dataAdapter.Update(MyDataS et)
Should they both be set to datasets?
I am getting this error: "Update unable to find TableMapping['Table'] or DataTable 'Table'."
Is the problem that you are binding the datagridview to a table, and then trying to update the dataset?
DataGridView1.DataSource = MyDataTable
dataAdapter.Update(MyDataS
Should they both be set to datasets?
If you call the Fill method, and don't specify a table alias, it will be called Table1:
dataAdapter.Fill(MyDataSet , "Table")
and
dataAdapter.Update(MyDataS et, "Table")
If you only have 1 DataTable in the DataSet, then it is more efficient to fill a DataTable, then have an entire DataSet.
Bob
dataAdapter.Fill(MyDataSet
and
dataAdapter.Update(MyDataS
If you only have 1 DataTable in the DataSet, then it is more efficient to fill a DataTable, then have an entire DataSet.
Bob
ASKER
Thanks, but I am not sure what you are saying.
In the example from newyuppie, it says fill the dataadapter with a table , the fill the DataGridView1.DataSource with the datatable.
You are saying fill it with a dataset.
Which one do I use?
Your answer does not work with the function refreshDataGRid in the example from newyuppie.
Also, can you show me what you mean by using the name of my table "users" instead of "Table".
Can you either show me how to make refreshDataGRid work with dataAdapter.Update(MyDataS et, "Table"), or show me how to make the update line work with that function?
In the example from newyuppie, it says fill the dataadapter with a table , the fill the DataGridView1.DataSource with the datatable.
You are saying fill it with a dataset.
Which one do I use?
Your answer does not work with the function refreshDataGRid in the example from newyuppie.
Also, can you show me what you mean by using the name of my table "users" instead of "Table".
Can you either show me how to make refreshDataGRid work with dataAdapter.Update(MyDataS
This is what I am saying:
1) Add an alias with a DataSet:
Dim ds As New DataSet
dataAdapter.Fill(ds, "users" )
2) Create a DataTable
Dim dt As New DataTable
dataAdapter.Fill(dt)
If you only have one table, then I feel that it is best to use the DataTable approach, since it has less overhead and is more efficient on processor and memory usage.
Bob
1) Add an alias with a DataSet:
Dim ds As New DataSet
dataAdapter.Fill(ds, "users" )
2) Create a DataTable
Dim dt As New DataTable
dataAdapter.Fill(dt)
If you only have one table, then I feel that it is best to use the DataTable approach, since it has less overhead and is more efficient on processor and memory usage.
Bob
ASKER
Thanks for your help, but I am still completely confused.
I am new to datasets and can't figure it out.
I think there are some steps missing since plugging in either of these 2 line chunks won't even fill the datagridview.
1. How would you get either #1, or #2 to fill a datagridview? Again I am working with the function above.
2. After that function is working with method #1, or method #2, how is the update called?
I am no closer to a solution than when I first started.
Since there isn't much to change, can you please re-write the function above, and show me the update line?
I would think it would be changing one or two lines.
I am new to datasets and can't figure it out.
I think there are some steps missing since plugging in either of these 2 line chunks won't even fill the datagridview.
1. How would you get either #1, or #2 to fill a datagridview? Again I am working with the function above.
2. After that function is working with method #1, or method #2, how is the update called?
I am no closer to a solution than when I first started.
Since there isn't much to change, can you please re-write the function above, and show me the update line?
I would think it would be changing one or two lines.
1) Try this:
Private Sub refreshDataGRid()
Dim dataAdapter As New OleDbDataAdapter("Select * From Users", myConnectionString)
Dim CB As OleDbCommandBuilder As New OleDbCommandBuilder(dataAd apter)
dataAdapter.UpdateCommand = CB.GetUpdateCommand()
Dim myTable As New DataTable
dataAdapter.Fill(myTable)
DataGridView1.AutoGenerate Columns = False
DataGridView1.DataSource = myTable
End Sub
2) If you are defining the columns through the property designer, then define this there, instead of in code:
DataGridView1.Columns("use rName").He aderText = "User Name"
DataGridView1.Columns("pas s").Header Text = "Password"
3) Also, if you are defining the columns, add the <Delete> button column there also.
Bob
Private Sub refreshDataGRid()
Dim dataAdapter As New OleDbDataAdapter("Select * From Users", myConnectionString)
Dim CB As OleDbCommandBuilder As New OleDbCommandBuilder(dataAd
dataAdapter.UpdateCommand = CB.GetUpdateCommand()
Dim myTable As New DataTable
dataAdapter.Fill(myTable)
DataGridView1.AutoGenerate
DataGridView1.DataSource = myTable
End Sub
2) If you are defining the columns through the property designer, then define this there, instead of in code:
DataGridView1.Columns("use
DataGridView1.Columns("pas
3) Also, if you are defining the columns, add the <Delete> button column there also.
Bob
ASKER
This function does not work. My grid is blank.
Private Sub refreshDataGRid()
Dim dataAdapter As New OleDbDataAdapter("Select * From Users", myConnectionString)
Dim CB As OleDbCommandBuilder As New OleDbCommandBuilder(dataAd apter)
dataAdapter.UpdateCommand = CB.GetUpdateCommand()
Dim myTable As New DataTable
dataAdapter.Fill(myTable)
DataGridView1.AutoGenerate Columns = False
DataGridView1.DataSource = myTable
End Sub
Private Sub refreshDataGRid()
Dim dataAdapter As New OleDbDataAdapter("Select * From Users", myConnectionString)
Dim CB As OleDbCommandBuilder As New OleDbCommandBuilder(dataAd
dataAdapter.UpdateCommand = CB.GetUpdateCommand()
Dim myTable As New DataTable
dataAdapter.Fill(myTable)
DataGridView1.AutoGenerate
DataGridView1.DataSource = myTable
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dataAdapter.UpdateCommand = CB.GetUpdateCommand()
since you already set the CommandBuilder to the dataadapter it will be set automatically
2) there is no need for
dataAdapter.Fill(myDataSet
myDataSet = getDataSet("select * from users")
myDataSet.Tables(0).TableN
myDataTable = myDataSet.Tables("users")
either.
replace that with
dim MyDataTable as New DataTable("users")
myDataSet.Tables.Add(MyDat
dataAdapter.Fill(MyDataTab
DataGridView1.DataSource = MyDataTable
directly
how about now, does this work with these changes? hope it helps somehting
NY