Input values into a DataGridView in a form and have them update to my Database.

Essentially I know how to do it for one row, however I can't get it to do it for the next row if I input data there.

Attached below is what the form looks like:

Attachment 143693


So the value is added in the datagridview from the form, the update button is clicked and the inserts them into the table itself in the database, the datagridview is used as the input method.

My code is outlined below:

Dim con As New SqlConnection(My.Settings.DiabetesServerConStr)
Dim insertNewValues As New SqlCommand()
insertNewValues.Connection = con
With insertNewValues.Parameters
.Add("@UserID", SqlDbType.Int).Value = BloodSugarDiary.Rows(0).Cells(0).Value
.Add("@Date", SqlDbType.Date).Value = Date.Today()
.Add("@BreakfastLevels", SqlDbType.Float).Value = BloodSugarDiary.Rows(0).Cells(2).Value
.Add("@LunchLevels", SqlDbType.Float).Value = BloodSugarDiary.Rows(0).Cells(3).Value
.Add("@DinnerLevels", SqlDbType.Float).Value = BloodSugarDiary.Rows(0).Cells(4).Value
.Add("@BedLevels", SqlDbType.Float).Value = BloodSugarDiary.Rows(0).Cells(5).Value
.Add("@BreakfastCarbs", SqlDbType.Float).Value = BloodSugarDiary.Rows(0).Cells(6).Value
.Add("@LunchCarbs", SqlDbType.Float).Value = BloodSugarDiary.Rows(0).Cells(7).Value
.Add("@DinnerCarbs", SqlDbType.Float).Value = BloodSugarDiary.Rows(0).Cells(8).Value
.Add("@BreakfastInsulin", SqlDbType.Float).Value = BloodSugarDiary.Rows(0).Cells(9).Value
.Add("@LunchInsulin", SqlDbType.Float).Value = BloodSugarDiary.Rows(0).Cells(10).Value
.Add("@DinnerInsulin", SqlDbType.Float).Value = BloodSugarDiary.Rows(0).Cells(11).Value
End With
Dim parcomstr As String = "INSERT INTO BloodSugarDiary (UserID, Date, BreakfastLevels, LunchLevels, DinnerLevels, BedLevels, BreakfastCarbs, LunchCarbs, DinnerCarbs, BreakfastInsulin, LunchInsulin, DinnerInsulin)VALUES (@UserID, @Date, @BreakfastLevels, @LunchLevels, @DinnerLevels, @BedLevels, @BreakfastCarbs, @LunchCarbs, @DinnerCarbs, @BreakfastInsulin, @LunchInsulin, @DinnerInsulin)"
insertNewValues.CommandText = parcomstr
Try
con.Open()
insertNewValues.ExecuteNonQuery()
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
con.Dispose()
End Sub

Whilst I can get the first row to save into the database, how can I do it for multiple rows?

I must have answered basically this same question four times in the last two days. DO NOT use ExecuteNonQuery. Create a DataTable and bind it to the grid, then save all the data in one go with a single call to Update on a data adapter. You can construct the DataTable manually or by calling FillSchema on the same data adapter. Follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data for examples.

JMC may well have answered this question. I certainly did, but I was answering it when I didn't realize the data was coming from a DGV. Also, in a different thread, it sounded like you already have a datatable. If you have a datatable, how did you create it? How did you bind it to the datagridview?

If you did those two steps, then show us the code you used there and we can show you the steps from there.

Quote:

---

Originally Posted by Shaggy Hiker

JMC may well have answered this question. I certainly did, but I was answering it when I didn't realize the data was coming from a DGV. Also, in a different thread, it sounded like you already have a datatable. If you have a datatable, how did you create it? How did you bind it to the datagridview?

If you did those two steps, then show us the code you used there and we can show you the steps from there.

---

I'm just gonna do the steps above here now. Nope I hadn't got a datatable set up.

You should. Binding a datatable to the DGV takes a single line. Updating a datatable back to the database will generally take four lines. Compare that to all that you were trying to do with that INSERT, and consider that the INSERT would only work the first time you entered the data. If you wanted to edit the data and save it again, you'd also need an UPDATE, which would be somewhat different from the INSERT. Meanwhile, with a datatable, the code that handled the inserting would also handle updating and deleting, as well.

The basic rule I would suggest would be this: If you are using a datagridview there should be a datatable behind it.

It's hard to think of an exception to this. It could be argued that you wouldn't need a datatable if you weren't storing/retrieving from a database, but even then you'd almost certainly be better off with a datatable.

Quote:

---

Originally Posted by Shaggy Hiker

You should. Binding a datatable to the DGV takes a single line. Updating a datatable back to the database will generally take four lines. Compare that to all that you were trying to do with that INSERT, and consider that the INSERT would only work the first time you entered the data. If you wanted to edit the data and save it again, you'd also need an UPDATE, which would be somewhat different from the INSERT. Meanwhile, with a datatable, the code that handled the inserting would also handle updating and deleting, as well.

The basic rule I would suggest would be this: If you are using a datagridview there should be a datatable behind it.

It's hard to think of an exception to this. It could be argued that you wouldn't need a datatable if you weren't storing/retrieving from a database, but even then you'd almost certainly be better off with a datatable.

---

Okay that makes sense I will get on it now, thank you.

Quote:

---

Originally Posted by Shaggy Hiker

You should. Binding a datatable to the DGV takes a single line. Updating a datatable back to the database will generally take four lines. Compare that to all that you were trying to do with that INSERT, and consider that the INSERT would only work the first time you entered the data. If you wanted to edit the data and save it again, you'd also need an UPDATE, which would be somewhat different from the INSERT. Meanwhile, with a datatable, the code that handled the inserting would also handle updating and deleting, as well.

The basic rule I would suggest would be this: If you are using a datagridview there should be a datatable behind it.

It's hard to think of an exception to this. It could be argued that you wouldn't need a datatable if you weren't storing/retrieving from a database, but even then you'd almost certainly be better off with a datatable.

---

This may take sometime.

Quote:

---

Originally Posted by jmcilhinney

I must have answered basically this same question four times in the last two days. DO NOT use ExecuteNonQuery. Create a DataTable and bind it to the grid, then save all the data in one go with a single call to Update on a data adapter. You can construct the DataTable manually or by calling FillSchema on the same data adapter. Follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data for examples.

---

Should all that code go under the update button?

Private Sub Blood_Sugar_Diary_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim con As New SqlConnection(My.Settings.DiabetesServerConStr)
Dim commandstring As String = "SELECT * FROM BloodSugarDiary"
Dim Adapter As New SqlDataAdapter(commandstring, con)
BloodSugarDiary.DataSource = NewTable
Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

Try
con.Open()
Using Table As New DataTable
Adapter.Fill(Table)
Table.Locale = Globalization.CultureInfo.InvariantCulture
BloodSugarDiary.DataSource = Table
End Using
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
con.Dispose()
End Sub

Private Sub UpdateButton_Click(sender As Object, e As EventArgs) Handles UpdateButton.Click
Dim Table As New DataTable
Dim Con As New SqlConnection(My.Settings.DiabetesServerConStr)
Dim DataAdapter As New SqlDataAdapter("SELECT UserID, Date, BreakfastLevels, LunchLevels, DinnerLevels, BedLevels, BreakfastCarbs, LunchCarbs, DinnerCarbs, BreakfastInsulin, LunchInsulin, DinnerInsulin", Con)
Dim builder As New SqlCommandBuilder(DataAdapter)
'
DataAdapter.Update(NewTable)
End Sub
End Class

Is this the code I should have?

Please don't post unformatted code snippets as they are quite hard to read.

vb.net Code:

Private Sub Blood_Sugar_Diary_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim con As New SqlConnection(My.Settings.DiabetesServerConStr)
        Dim commandstring As String = "SELECT * FROM BloodSugarDiary"
        Dim Adapter As New SqlDataAdapter(commandstring, con)
        BloodSugarDiary.DataSource = NewTable
        Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
        Try
            con.Open()
            Using Table As New DataTable
                Adapter.Fill(Table)
                Table.Locale = Globalization.CultureInfo.InvariantCulture
                BloodSugarDiary.DataSource = Table
            End Using
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        con.Dispose()
    End Sub
 
    Private Sub UpdateButton_Click(sender As Object, e As EventArgs) Handles UpdateButton.Click
        Dim Table As New DataTable
        Dim Con As New SqlConnection(My.Settings.DiabetesServerConStr)
        Dim DataAdapter As New SqlDataAdapter("SELECT UserID, Date, BreakfastLevels, LunchLevels, DinnerLevels, BedLevels, BreakfastCarbs, LunchCarbs, DinnerCarbs, BreakfastInsulin, LunchInsulin, DinnerInsulin", Con)
        Dim builder As New SqlCommandBuilder(DataAdapter)
        '
        DataAdapter.Update(NewTable)
    End Sub
End Class

Firstly, did that code work? If not, how did what actually happened differ from your expectation?

One thing I would say is that that code violates the DRY principle. I'd probably tend to create a single data adapter and reuse it but, if you are going to create multiple, I'd suggest a method does the creation and returns it. That way, you only have to write the SQL code once.

Well the code didn't crash, however it didn't update or add in any of the new values I put into the DataGridView in the form. Afterwards I realised the code was most likely horribly wrong and I'm definitely making myself look like an idiot.

So I tried something else and this is as far as I've got.

Code:

---

Public Class Blood_Sugar_Diary
    Private con As New SqlConnection(My.Settings.DiabetesServerConStr)
    Dim DataTable As DataTable
Private Sub Blood_Sugar_Diary_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim con As New SqlConnection(My.Settings.DiabetesServerConStr)
        Dim commandstring As String = "SELECT * FROM BloodSugarDiary"
        Dim Adapter As New SqlDataAdapter(commandstring, con)
        Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
        Try
            con.Open()
            Using Table As New DataTable
                Adapter.Fill(Table)
                Table.Locale = Globalization.CultureInfo.InvariantCulture
                BloodSugarDiary.DataSource = Table
            End Using
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        con.Dispose()
    End Sub
    Sub DataTableWithBinding()

        BloodSugarDiary.DataSource = DataTable
        '
    End Sub
    Private adapter As New SqlDataAdapter("SELECT UserID, Date, BreakfastLevels, LunchLevels, DinnerLevels, BedLevels, BreakfastCarbs, LunchCarbs, DinnerCarbs, BreakfastInsulin, LunchInsulin, DinnerInsulin FROM BloodSugarDiary", con)
    Private Sub InitialiseDataAdapter()
        Dim insert As New SqlCommand("INSERT INTO StockItem (UserID, Date, BreakfastLevels, LunchLevels, DinnerLevels, BedLevels, BreakfastCarbs, LunchCarbs, DinnerCarbs, BreakfastInsulin, LunchInsulin, DinnerInsulin) VALUES (@UserID, @Date, @BreakfastLevels, @LunchLevels, @DinnerLevels, @BedLevels, @BreakfastCarbs, @LunchCarbs, @DinnerCarbs, @BreakfastInsulin, @LunchInsulin, @DinnerInsulin)", con)
        Dim update As New SqlCommand("UPDATE BloodSugarDiary SET UserID = @UserID, Date = @Date, BreakfastLevels = @BreakfastLevels, LunchLevels = @LunchLevels, DinnerLevels = @DinnerLevels, BedLevels = @BedLevels, BreakfastCarbs = @BreakfastCarbs, LunchCarbs = @LunchCarbs, DinnerCarbs = @DinnerCarbs, BreakfastInsulin = @BreakfastInsulin, LunchInsulin = @LunchInsulin, DinnerInsulin = @DinnerInsulin  = @Unit WHERE UserID = @UserID", con)
        With insert.Parameters
            .Add("@UserID", SqlDbType.Int, 5, "UserID")
            .Add("@Date", SqlDbType.Date, "Date")
            .Add("@BreakfastLevels", SqlDbType.Decimal, "BreakfastLevels")
            .Add("@LunchLevels", SqlDbType.Decimal, "LunchLevels")
            .Add("@DinnerLevels", SqlDbType.Decimal, "DinnerLevels")
            .Add("@BedLevels", SqlDbType.Decimal, "BedLevels")
            .Add("@BreakfastCarbs", SqlDbType.Decimal, "BreakfastCarbs")
            .Add("@LunchCarbs", SqlDbType.Decimal, "LunchCarbs")
            .Add("@DinnerCarbs", SqlDbType.Decimal, "DinnerCarbs")
            .Add("@BreakfastInsulin", SqlDbType.Decimal, "BreakfastInsulin")
            .Add("@LunchInsulin", SqlDbType.Decimal, "LunchInsulin")
            .Add("@DinnerInsulin", SqlDbType.Decimal, "DinnerInsulin")
        End With
        With update.Parameters
            .Add("@UserID", SqlDbType.Int, 5, "UserID")
            .Add("@Date", SqlDbType.Date, "Date")
            .Add("@BreakfastLevels", SqlDbType.Decimal, "BreakfastLevels")
            .Add("@LunchLevels", SqlDbType.Decimal, "LunchLevels")
            .Add("@DinnerLevels", SqlDbType.Decimal, "DinnerLevels")
            .Add("@BedLevels", SqlDbType.Decimal, "BedLevels")
            .Add("@BreakfastCarbs", SqlDbType.Decimal, "BreakfastCarbs")
            .Add("@LunchCarbs", SqlDbType.Decimal, "LunchCarbs")
            .Add("@DinnerCarbs", SqlDbType.Decimal, "DinnerCarbs")
            .Add("@BreakfastInsulin", SqlDbType.Decimal, "BreakfastInsulin")
            .Add("@LunchInsulin", SqlDbType.Decimal, "LunchInsulin")
            .Add("@DinnerInsulin", SqlDbType.Decimal, "DinnerInsulin")
        End With
        Me.adapter.InsertCommand = insert
        Me.adapter.UpdateCommand = update
        Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    End Sub
    Private Sub GetData()
        Me.adapter.Fill(Me.DataTable)
    End Sub
    Private Sub SaveData()
        'Save the changes.
        Me.adapter.Update(Me.DataTable)
    End Sub
    Private Sub UpdateButton_Click(sender As Object, e As EventArgs) Handles UpdateButton.Click
        Try
            DataTableWithBinding()
            '
            GetData()
            '
            InitialiseDataAdapter()
            '
            SaveData()
            MessageBox.Show("Update Successful")
        Catch exception As SqlException
            MessageBox.Show(exception.Message)
        End Try
    End Sub

---

I get an error message in this sub:

Code:

---

Private Sub GetData()
        Me.adapter.Fill(Me.DataTable)
    End Sub

---

Saying - An unhandled exception of type 'System.ArgumentNullException' occurred in System.Data.dll

Additional information: Value cannot be null.

I'm getting lost in the code to be honest and need all the help I can to arrange it right and get it working.

Right off I will caution you about doing too much in Form Load. There's a weird thing, which some might reasonably call a bug, though it technically isn't, such that any exception thrown in the Load event (and ONLY the load event), which isn't also handled in the load event, gets quietly swallowed. So, if you have an exception outside of the code that you have in a Try...Catch...it can behave REALLY weird, because it might get to a point and then just stop. This will only be true on systems running a 64-bit version of Windows, but those are increasingly common. For this reason, I like to do things in the form constructor (Sub New), and be cautious with anything in Load.

Second, I like the fact that you were making use of the Using construct. I didn't like the fact that you used if for the datatable. The whole point of Using is that the object will be cleaned up when you are done with it....which is the End Using. However, you don't want the datatable to go away, you want to set it as the datasource for the table. You should be using the Using construct for the connection and the dataadapter, but not for the datatable. You want THAT particular object to persist for quite a bit longer than just the Using block.

Third, you have a poorly named Datatable variable called DataTable at form scope. The names a poor choice, because...well, datatable is the type name, so that will just cause confusion. You don't totally NEED the variable at form scope, but it might be useful, so go ahead, just give it a different name. However, you are loading a different table in the Load event. You should be loading the one datatable at form scope rather than that local datatable that you are loading in the Using block.

Fourth, a similar point could be made about the form scope dataadapter versus the local scope one created in the Load event. The SQL in the form scope version is better than the SQL in the local one, because writing out the field names is going to always have slightly better performance over using SELECT *, so if you are willing to write out the field names, then do so. Also, if you are making the one at form scope, then you don't need the local one.

Finally (at least for now), you don't need to write those INSERT and UPDATE statements. You can get that automatically in many cases, and this is one of those cases. You create the dataadapter with the SELECT statement, as you did, then you do something like this:

Code:

---

Dim cb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(adapter)

---

Then you just call adapter.Update. What the commandBuilder does is takes your SELECT statement, which you already have to the adapter when you created it, and generates UPDATE, INSERT, and DELETE sql based on that. The CommandBuilder does have some limitations, such as it won't generate the SQL if your SELECT statement has JOINS in it, but for a single table SELECT statement, as you have, then CommandBuilder takes care of the rest of the stuff, so you don't need to write any of that.

By the way, it may just be the poor choice of a name for DataTable that is causing the error you are getting, but the other items need to be addressed, too.

Quote:

---

Originally Posted by Shaggy Hiker

Right off I will caution you about doing too much in Form Load. There's a weird thing, which some might reasonably call a bug, though it technically isn't, such that any exception thrown in the Load event (and ONLY the load event), which isn't also handled in the load event, gets quietly swallowed. So, if you have an exception outside of the code that you have in a Try...Catch...it can behave REALLY weird, because it might get to a point and then just stop. This will only be true on systems running a 64-bit version of Windows, but those are increasingly common. For this reason, I like to do things in the form constructor (Sub New), and be cautious with anything in Load.

Second, I like the fact that you were making use of the Using construct. I didn't like the fact that you used if for the datatable. The whole point of Using is that the object will be cleaned up when you are done with it....which is the End Using. However, you don't want the datatable to go away, you want to set it as the datasource for the table. You should be using the Using construct for the connection and the dataadapter, but not for the datatable. You want THAT particular object to persist for quite a bit longer than just the Using block.

Third, you have a poorly named Datatable variable called DataTable at form scope. The names a poor choice, because...well, datatable is the type name, so that will just cause confusion. You don't totally NEED the variable at form scope, but it might be useful, so go ahead, just give it a different name. However, you are loading a different table in the Load event. You should be loading the one datatable at form scope rather than that local datatable that you are loading in the Using block.

Fourth, a similar point could be made about the form scope dataadapter versus the local scope one created in the Load event. The SQL in the form scope version is better than the SQL in the local one, because writing out the field names is going to always have slightly better performance over using SELECT *, so if you are willing to write out the field names, then do so. Also, if you are making the one at form scope, then you don't need the local one.

Finally (at least for now), you don't need to write those INSERT and UPDATE statements. You can get that automatically in many cases, and this is one of those cases. You create the dataadapter with the SELECT statement, as you did, then you do something like this:

Code:

---

Dim cb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(adapter)

---

Then you just call adapter.Update. What the commandBuilder does is takes your SELECT statement, which you already have to the adapter when you created it, and generates UPDATE, INSERT, and DELETE sql based on that. The CommandBuilder does have some limitations, such as it won't generate the SQL if your SELECT statement has JOINS in it, but for a single table SELECT statement, as you have, then CommandBuilder takes care of the rest of the stuff, so you don't need to write any of that.

By the way, it may just be the poor choice of a name for DataTable that is causing the error you are getting, but the other items need to be addressed, too.

---

I GOT IT!!!!

Code:

---

Dim con As New SqlConnection(My.Settings.DiabetesServerConStr)
    Dim bloodsugartable As New DataTable
    Dim bloodsugaradapter As New SqlDataAdapter("SELECT UserID, Date, BreakfastLevels, LunchLevels, DinnerLevels, BedLevels, BreakfastCarbs, LunchCarbs, DinnerCarbs, BreakfastInsulin, LunchInsulin, DinnerInsulin FROM BloodSugarDiary", con)
    Private Sub Blood_Sugar_Diary_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim con As New SqlConnection(My.Settings.DiabetesServerConStr)
        bloodsugaradapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
        Try
            con.Open()
            Using bloodsugartable
                GetData()
                bloodsugartable.Locale = Globalization.CultureInfo.InvariantCulture
                BloodSugarDiary.DataSource = bloodsugartable
            End Using
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        con.Dispose()
    End Sub
    Sub DataTableWithBinding()
        BloodSugarDiary.DataSource = bloodsugartable
    End Sub
    Private Sub InitialiseDataAdapter()
        Dim cb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(bloodsugaradapter)
    End Sub
    Private Sub GetData()
        Me.bloodsugaradapter.Fill(Me.bloodsugartable)
    End Sub
    Private Sub SaveData()
        'Save the changes.
        Me.bloodsugaradapter.Update(Me.bloodsugartable)
    End Sub
    Private Sub UpdateButton_Click(sender As Object, e As EventArgs) Handles UpdateButton.Click
        Try
            DataTableWithBinding()
            '
            GetData()
            '
            InitialiseDataAdapter()
            '
            SaveData()
            MessageBox.Show("Update Successful")
        Catch exception As SqlException
            MessageBox.Show(exception.Message)
        End Try
    End Sub
End Class

---

Thank you so damn much for the help you are a life saver!

You're still misusing the Using statement. That's about disposing of an object once you are done with it, but you are using it here:

Using bloodsugartable

That gives you no advantage, and I'm kind of thinking that it does nothing at all. Normally, you would create the object in the Using statement, and it would be destroyed in the End Using. In this case, you don't create it in the Using statement, and if it really were being disposed in the End Using....that would be BAD, because you want it to stick around as the datasource of the DGV.

So, get rid of the Using statement. It isn't doing anything useful, and may be doing something you don't want.

Quote:

---

Originally Posted by Shaggy Hiker

You're still misusing the Using statement. That's about disposing of an object once you are done with it, but you are using it here:

Using bloodsugartable

That gives you no advantage, and I'm kind of thinking that it does nothing at all. Normally, you would create the object in the Using statement, and it would be destroyed in the End Using. In this case, you don't create it in the Using statement, and if it really were being disposed in the End Using....that would be BAD, because you want it to stick around as the datasource of the DGV.

So, get rid of the Using statement. It isn't doing anything useful, and may be doing something you don't want.

---

Got rid of it there and everything still works perfectly :)