Datagridview Addition/deletion/modification

Some weeks ago, I posted a question and instead of help received a rather toxic scree (that is the word I want to use and is appropriate to the action) from a alleged authority, instead of any help or useful suggestions.

I spent quite a bit of time on this and eventually figured out what was required. It was nothing complex, or particularly tricky, and could have been addressed by anyone who has any knowledgeable advice. I was able to finally resolve the issue myself.

The original issue was that I had a DGV bound to a table and I wanted to add/delete and modify records in the dgv and have those changes reflected in the table (ACCESS). I had looked at numerous methods and found two that were promising.

The first was a commonly used method based on a routine requiring that the whole dgv be processed in a loop containing a query, and using

Code:

---

Command.ExecuteNonQuery()

---

. I was able to make this method work, but not well.

The second method was based on the adapter.Update command and, at the time, I was completely unable to make this method work at all.


Attachment 185069


What was required was to set the correct properties and use the code below.

Code:

---

    Private Sub fileScan_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
        LoadGrid()
        SetState()
    End Sub

---

Code:

---

    Private Sub LoadGrid()
        SetQuery()
        If RecordCount > 0 Then
            dgvList.DataSource = FileMaster.ListDataSet.Tables(0)
            dgvList.Rows(0).Selected = True


            FileMaster.ListAdapter.UpdateCommand = New OleDbCommandBuilder(FileMaster.ListAdapter).GetUpdateCommand
        End If
    End Sub

---

Code:

---

    Private Sub SetQuery()
#Region "Establish Connection and execute query"
        FileMaster.ScanMasterQuery("SELECT colScan,colScanPath FROM lkpScan")
#End Region
    End Sub

---

The key to the whole process is the line:

Code:

---

FileMaster.ListAdapter.UpdateCommand = New OleDbCommandBuilder(FileMaster.ListAdapter).GetUpdateCommand

---

found in LoadGrid().

In my previous post my intelligence was questioned for even using this line. It turns out that one can do just about anything wanted to a dgv using this method.

At the end of the day, it is really good that there is someone that is willing to assist those of us who might not be experts. Most of the experts here do indeed provide that wonderful service. However, there are some alleged experts who often have nothing to offer but to question my intelligence for even asking a question, and then offer NO useful information, or gibberish instead of useful information.

well I don't know which person you talked/written to, but here a sample with the DataReader which you might find useful

also I would like to add that I have never seen an "Expert" mock a "Beginner" that is just starting .Net

just a "LookUp and Search" if a Employee is in the Database

Code:

---

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim sDB As String = "E:\Adressen.mdb"
        Dim EmployeeIdToFind As Integer = TextBox1.Text
        Dim sCon As New OleDb.OleDbConnection()
        Dim Con As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=" & sDB & ";"
        sCon.ConnectionString = con

        Dim oCmd As New OleDb.OleDbCommand("SELECT * FROM Employees WHERE EmployeeID =" & TextBox1.Text, sCon)
        sCon.Open()
        Dim dr As OleDb.OleDbDataReader = oCmd.ExecuteReader()

        If dr.HasRows Then
            Do While dr.Read()
                Label1.Text = dr(0)
                Label2.Text = dr(1)
                Label3.Text = dr(2)
                Label4.Text = dr(3)
            Loop
        Else
            MessageBox.Show("Nothing found!")
        End If
        dr.Close()
    End Sub

---

maybe it has something to do with your presentation. your attitude suggests you're a bit of a Kevin and nobody likes a kevin...

That doesn't alter the fact vb. As for presentation... well, my presentation is better than some received from those I previously referenced.

Love, Kevin


Chris,

The method you offer actually looks pretty similar to one of the two DGV methods I have been working with. That was very similar with how I tried to move the data with the method using the Command.ExecuteNonQuery() method.
I used For/Next instead of Do While. I never could get the method to work correctly and still am not clear why.

The method I ended up using was the FileMaster.ListAdapter.UpdateCommand = NewOleDbCommandBuilder(FileMaster.ListAdapter).GetUpdateCommand method.

What I was originally looking for was reasonable method for using a create/edit/delete data in a datagridview then put the data back into the table from whence it came. The first method I am still unable to make work correctly, but the second works very well.

Quote:

---

The key to the whole process is the line

---

The actual key is creating the command builder. Calling GetUpdateCommand and assigning the result is pointless, as you've already been told multiple times. If that were necessary then my own multiple tests performed over the last two decades would not have worked.

Quote:

---

In my previous post my intelligence was questioned for even using this line.

---

No. In your previous thread you were told that doing so was pointless and why. If your intelligence was questioned, it was because you refused to accept that fact that doing so was pointless, despite being told so multiple times. Rather than admit that you were wrong, you've chosen to double down on that wrongness in order to protect your own ego. It's the refusal to admit that you could have done the wrong thing that makes you foolish, not doing the wrong thing in the first place.

What the hell. It's a long weekend here and I've got half an hour to spare. Here's a demo project that uses an Access database with an OleDbDataAdapter and an OleDbCommandBuilder. It displays data in a DataGridView and allows you to add, edit and delete records. What do you know? It works exactly how I said it would in the first place. You simply create the command builder and call Update on the data adapter and it works. Nary a call to GetUpdateCommand in sight. I've created the solution in VS 2019, which is the oldest version I have installed.

That's really cool!

Yes, it does eliminate the need for the GetUpdateCommand. So what are the disadvantages associated with the GetUpdateCommand, and why not offer your tool up a long time ago?

Quote:

---

Originally Posted by gwboolean

Yes, it does eliminate the need for the GetUpdateCommand.

---

No it doesn't. There was never a need for that in the first place. That's the point.

Quote:

---

Originally Posted by gwboolean

So what are the disadvantages associated with the GetUpdateCommand

---

As I have already explained multiple times, it is pointless if you're not going to modify the command it returns. That's why it exists. If you create a command builder for a data adapter and then call Update, the command builder will provide the action commands if you haven't already set them. If you call getUpdateCommand and assign the result to the UpdateCommand property then it won't hurt but it won't achieve anything useful because the same command would be provided automatically. You could have found this information for yourself by reading the relevant documentation - the same documentation I read to learn about it.

Quote:

---

Originally Posted by gwboolean

why not offer your tool up a long time ago?

---

I didn't provide a demo previously because there was no need. This demo is just an implementation of exactly what I had already told you to do but you refused to listen. I didn't provide this demo to help you - I'd already provided help and you rejected it. I provided the demo to show that you were wrong to reject the help in the first place and wrong to proclaim that as a good thing here. When I got pissed off in your other thread, it wasn't because you're a beginner and it wasn't because you didn't know what to do. It was because I was telling you exactly what you should do and you were refusing to accept it and then were complaining that I wasn't helping. Maybe next time you'll listen to advice when it's provided. Then again, maybe not. Only time will tell.

Sure thing. You do like your games and I always get the aura of deja moo from your screes.

Quote:

---

Originally Posted by gwboolean

The key to the whole process is the line:

Code:

---

FileMaster.ListAdapter.UpdateCommand = New OleDbCommandBuilder(FileMaster.ListAdapter).GetUpdateCommand

---

---

You were actually right about that, but for the wrong reason. As I said repeatedly, all you need to do is create a command builder but calling GetUpdateCommand on it is pointless. You're calling GetUpdateCommand, which is pointless, but in order to be able to do that you must have created a command builder first. You're doing that here:

Code:

---

FileMaster.ListAdapter.UpdateCommand = New OleDbCommandBuilder(FileMaster.ListAdapter).GetUpdateCommand

---

and that is what's actually achieving something useful. The rest is... what's the word... pointless.

Let's leave it there.