PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VS 2015 Input values into a DataGridView in a form and have them update to my Database.-VBForums
Results 1 to 17 of 17

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

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2016
    Posts
    28

    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:

    Name:  Untitled-1.jpg
Views: 166
Size:  35.9 KB


    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?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,607

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

    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.

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,932

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

    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.
    My usual boring signature: Nothing

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Dec 2016
    Posts
    28

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

    Quote Originally Posted by Shaggy Hiker View Post
    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.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,932

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

    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.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Dec 2016
    Posts
    28

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

    Quote Originally Posted by Shaggy Hiker View Post
    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.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Dec 2016
    Posts
    28

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

    Quote Originally Posted by Shaggy Hiker View Post
    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.
    Last edited by Koviic; Dec 29th, 2016 at 05:00 PM.

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Dec 2016
    Posts
    28

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

    Quote Originally Posted by jmcilhinney View Post
    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?

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Dec 2016
    Posts
    28

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

    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?

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,607

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

    Please don't post unformatted code snippets as they are quite hard to read.
    vb.net Code:
    1. Private Sub Blood_Sugar_Diary_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    2.         Dim con As New SqlConnection(My.Settings.DiabetesServerConStr)
    3.         Dim commandstring As String = "SELECT * FROM BloodSugarDiary"
    4.         Dim Adapter As New SqlDataAdapter(commandstring, con)
    5.         BloodSugarDiary.DataSource = NewTable
    6.         Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    7.  
    8.         Try
    9.             con.Open()
    10.             Using Table As New DataTable
    11.                 Adapter.Fill(Table)
    12.                 Table.Locale = Globalization.CultureInfo.InvariantCulture
    13.                 BloodSugarDiary.DataSource = Table
    14.             End Using
    15.             con.Close()
    16.         Catch ex As Exception
    17.             MsgBox(ex.Message)
    18.         End Try
    19.         con.Dispose()
    20.     End Sub
    21.  
    22.     Private Sub UpdateButton_Click(sender As Object, e As EventArgs) Handles UpdateButton.Click
    23.         Dim Table As New DataTable
    24.         Dim Con As New SqlConnection(My.Settings.DiabetesServerConStr)
    25.         Dim DataAdapter As New SqlDataAdapter("SELECT UserID, Date, BreakfastLevels, LunchLevels, DinnerLevels, BedLevels, BreakfastCarbs, LunchCarbs, DinnerCarbs, BreakfastInsulin, LunchInsulin, DinnerInsulin", Con)
    26.         Dim builder As New SqlCommandBuilder(DataAdapter)
    27.         '
    28.         DataAdapter.Update(NewTable)
    29.     End Sub
    30. End Class

  11. #11
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,607

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

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

  12. #12
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,607

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

    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.

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Dec 2016
    Posts
    28

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

    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.
    Last edited by Koviic; Dec 30th, 2016 at 11:07 AM.

  14. #14
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,932

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

    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.
    My usual boring signature: Nothing

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Dec 2016
    Posts
    28

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

    Quote Originally Posted by Shaggy Hiker View Post
    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!

  16. #16
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,932

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

    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.
    My usual boring signature: Nothing

  17. #17

    Thread Starter
    Junior Member
    Join Date
    Dec 2016
    Posts
    28

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

    Quote Originally Posted by Shaggy Hiker View Post
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width