Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

vb.net dataadapter update and datagridview

Posted on 2006-10-31
15
Medium Priority
?
1,008 Views
Last Modified: 2008-01-09
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(myDataSet)
    End Sub

   This fills my datagrid:

    Private Sub refreshDataGRid()

        dataAdapter.SelectCommand = New OleDbCommand("select * from users", myConnection)

        Dim CB As OleDbCommandBuilder
        CB = New OleDbCommandBuilder(dataAdapter)

        dataAdapter.UpdateCommand = CB.GetUpdateCommand()

        dataAdapter.Fill(myDataSet)

        myDataSet = getDataSet("select * from users")
        myDataSet.Tables(0).TableName = "users"
        myDataTable = myDataSet.Tables("users")
        DataGridView1.DataSource = myDataTable

        DataGridView1.Columns("userName").HeaderText = "User Name"
        DataGridView1.Columns("pass").HeaderText = "Password"

        If Not DataGridView1.Columns.Contains("Delete") Then
            Dim buttons As New DataGridViewButtonColumn()
            With buttons
                .HeaderText = "Delete"
                .Text = "Delete"
                .Name = "Delete"
                .UseColumnTextForButtonValue = True
                .AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
                .FlatStyle = FlatStyle.Standard
                .DisplayIndex = 0
            End With

            DataGridView1.Columns.Add(buttons)
        End If

    End Sub
0
Comment
Question by:jackjohnson44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 13

Expert Comment

by:newyuppie
ID: 17846222
1) there is no need for this line
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).TableName = "users"
        myDataTable = myDataSet.Tables("users")
either.

replace that with

dim MyDataTable as New DataTable("users")
myDataSet.Tables.Add(MyDataTable )
dataAdapter.Fill(MyDataTable )
DataGridView1.DataSource = MyDataTable

directly


how about now, does this work with these changes? hope it helps somehting
NY
0
 
LVL 6

Expert Comment

by:manch
ID: 17847763
Use
myDataSet .AcceptChanges()
'after calling the adapter update command for commiting it

Try It
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17847941
manch, the dataadapter by default calls acceptchanges on update, unless he changed it manually there's no need to do that.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:jackjohnson44
ID: 17852372
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(myDataSet) 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.

0
 

Author Comment

by:jackjohnson44
ID: 17852502
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(dataAdapter)

        dataAdapter.UpdateCommand = CB.GetUpdateCommand()

        dataAdapter.Fill(myDataSet)

        'myDataSet = getDataSet("select * from users")
        'myDataSet.Tables(0).TableName = "users"
        'myDataTable = myDataSet.Tables("users")
        'DataGridView1.DataSource = myDataTable

        myDataTable = New DataTable("users")
        myDataSet.Tables.Add(MyDataTable)
        dataAdapter.Fill(MyDataTable)
        DataGridView1.DataSource = MyDataTable



        DataGridView1.Columns("userName").HeaderText = "User Name"
        DataGridView1.Columns("pass").HeaderText = "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(myDataSet)
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17852802
this is what you should have:

Private Sub refreshDataGRid()

        dataAdapter.SelectCommand = New OleDbCommand("select * from users", myConnection)

        Dim CB As OleDbCommandBuilder
        CB = New OleDbCommandBuilder(dataAdapter)

        myDataTable = New DataTable("users")
        myDataSet.Tables.Add(MyDataTable)
        dataAdapter.Fill(MyDataTable)
        DataGridView1.DataSource = MyDataTable

        DataGridView1.Columns("userName").HeaderText = "User Name"
        DataGridView1.Columns("pass").HeaderText = "Password"
end sub

private sub ButtonSave_Click(sender as object, e as eventargs) handles ButtonSave.Click
dataAdapter.Update(MyDataSet)
End SUb


you will need to add a button called ButtonSave, so that it will save when you press it.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17859901
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
0
 

Author Comment

by:jackjohnson44
ID: 17868283
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(MyDataSet)

Should they both be set to datasets?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17868560
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(MyDataSet, "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
0
 

Author Comment

by:jackjohnson44
ID: 17868813
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(MyDataSet, "Table"), or show me how to make the update line work with that function?

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17868862
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
0
 

Author Comment

by:jackjohnson44
ID: 17868934
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.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17868979
1) Try this:

Private Sub refreshDataGRid()

   Dim dataAdapter As New OleDbDataAdapter("Select * From Users", myConnectionString)
 
   Dim CB As OleDbCommandBuilder As New OleDbCommandBuilder(dataAdapter)
   dataAdapter.UpdateCommand = CB.GetUpdateCommand()

   Dim myTable As New DataTable
   dataAdapter.Fill(myTable)

   DataGridView1.AutoGenerateColumns = 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("userName").HeaderText = "User Name"
    DataGridView1.Columns("pass").HeaderText = "Password"

3) Also, if you are defining the columns, add the <Delete> button column there also.

Bob
0
 

Author Comment

by:jackjohnson44
ID: 17869024
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(dataAdapter)
   dataAdapter.UpdateCommand = CB.GetUpdateCommand()

   Dim myTable As New DataTable
   dataAdapter.Fill(myTable)

   DataGridView1.AutoGenerateColumns = False

   DataGridView1.DataSource = myTable

End Sub
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 1000 total points
ID: 17877531
Is myTable.Rows.Count > 0?

Bob
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question