Adding, Editing and Deleting data from Datagridview

Question

I'm trying to insert data from a datagridview control into my SQL database. The code is as follows:

Private Sub MCmdSoftwareSave_Click(sender As Object, e As EventArgs) Handles MCmdSoftwareSave.Click
    Dim ID As Integer
    Dim Name, Edition, Version, SubVersion As String
    Try
        SQLcmd.Connection = SQLconn
        SQLconn.Open()
        For row As Integer = 0 To Me.DgdSoftware.Rows.Count - 1
            ID = Me.DgdSoftware.Rows(row).Cells(0).Value
            Name = Me.DgdSoftware.Rows(row).Cells(1).Value
            Edition = Me.DgdSoftware.Rows(row).Cells(2).Value
            Version = Me.DgdSoftware.Rows(row).Cells(3).Value
            SubVersion = Me.DgdSoftware.Rows(row).Cells(4).Value
            SQLcmd.CommandText = "INSERT INTO Software(ID,Name,Edition,Version,SubVersion) VALUES('" & ID & "','" & Name & "','" & Edition & "','" & Version & "','" & SubVersion & "')"
            SQLcmd.ExecuteNonQuery()
        Next
        MsgBox("The infomration was successfully added to the Software database.", MsgBoxStyle.Information, "Successful Operation")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        SQLconn.Close()
    End Try
End Sub

This works when the table is empty, but it also adds another record where the "ID" (Primary key) is 0 and the rest of the fields in that record are null, before adding the desired data.

If I then try to add more data, I get a message saying I cannot add data to the database where the ID of the current record being saved matches that of an ID in the database (i.e. it's trying to re-save the existing data into the table).

What I want to be able to do is load up a form where all the data in the table is loaded into the datagridview control. I then want to be able to add to that table using the same datagridview control by putting in new records, without the code trying to save the existing data. I also want to be able to edit and delete the data in the datagridview control.

Is there any way to do this effectively?

Many thanks,

James

Never, ever concat strings to make SQL - use SQL parameters. It avoids many problems can be avoided....and it is much simpler. None of that is needed if you use a datasource - the NET objects will update the DB for you — Ňɏssa Pøngjǣrdenlarp, Jul 04 '17 at 22:20
I have the data source set to my table (SoftwareBindingSource) but it does not automatically update. — James, Jul 04 '17 at 22:25
Its trying to - thats why you get the error. When you make changes to the data it automatically updates the underlying table. Then use the DataAdapter to send all the changes (1 to 1,000) to the DB No new SQL required - the NET objects are *smart* — Ňɏssa Pøngjǣrdenlarp, Jul 04 '17 at 22:37
@Plutonix ah fair enough, I didn't know that. How would I use the data adapter to send the data, I presume some code triggered by a button? — James, Jul 04 '17 at 22:45
Please see [Searching values via a datagridview](http://stackoverflow.com/a/33702351/1070452) after you read [ask] and take the [tour] — Ňɏssa Pøngjǣrdenlarp, Jul 04 '17 at 22:49
@Plutonix I will do, thank you very much for your help! Greatly appreciated! — James, Jul 04 '17 at 22:59

obl · Accepted Answer · 2017-07-04T22:16:53.290

4

The easiest way I can think of is to have another column in your datagridview which you can think of as an indicator to whether or not the row has already been inserted into your database. Default it to 0, and do a check in the for loop to only insert rows that have a value of 0 for that column and update it to 1 afterwards.

You should also look into using parameterized SQL queries

If you have AllowUserToAddRows set to True, there will be a blank row at the bottom of the datagridview. You can get around this by changing your for loop to go from 0 to Me.DgdSoftware.Rows.Count - 2.

edited Jul 04 '17 at 22:16

answered Jul 04 '17 at 22:10

obl

1,799
12
38

Thanks, I'll check it out – James Jul 04 '17 at 22:29
You can also use `DataTable.Rows(n).RowState` – SSS Jul 04 '17 at 23:32

score 2 · Answer 2 · answered Jul 04 '17 at 22:28

2

Complementing the answer of @obl, read the datagridview events documentation.

You can use the CellEndEdit event to mark a row and know if it needs to be deleted or updated when you click your save button.

answered Jul 04 '17 at 22:28

Victor Aparicio

71
3

Adding, Editing and Deleting data from Datagridview

2 Answers2