batch update in datagridview RRS feed

  • Question

  • Hi all,
    i m new in I have a table with following columns:-
    here i am binding this table to gridview in which close and sold are unbound columns which are filled during runtime. Here is the code:-

    Public Sub gridbind()

    Dim offset As Integer = -1

    Dim NewDate As String = DateAdd(DateInterval.Day, offset, Today)

    Dim sql As String = "select name,price,[open] from LotterySale where date = '" & NewDate & "' or date ='" & DateTime.Now.Date & "'"

    Dim adp As New SqlDataAdapter(sql, con)

    Dim ds As New DataSet

    adp.Fill(ds, "lotterysale")

    ds.Tables("Lotterysale").Columns.Add(New DataColumn("Close", GetType(Integer)))

    ds.Tables("Lotterysale").Columns.Add(New DataColumn("NoOfTickets", GetType(Integer)))

    ds.Tables("Lotterysale").Columns("NoOfTickets").Expression = "open - close"

    ds.Tables("Lotterysale").Columns.Add(New DataColumn("Sold", GetType(Integer), "(open - close) * price"))

    Me.LotteryGrid.AutoGenerateColumns = False

    Me.LotteryGrid.EditMode = DataGridViewEditMode.EditOnEnter

    LotteryGrid.DataSource = ds.Tables("LotterySale")

    Dim name As New DataGridViewTextBoxColumn

    With name

    .HeaderText = "Name"

    .Name = "Name"

    .DataPropertyName = "Name"

    End With

    Dim price As New DataGridViewTextBoxColumn

    With price

    .HeaderText = "Price"

    .Name = "price"

    .DataPropertyName = "price"

    End With

    Dim open As New DataGridViewTextBoxColumn

    With open

    .HeaderText = "Open"

    .Name = "Open"

    .DataPropertyName = "open"

    End With

    Dim close As New DataGridViewTextBoxColumn

    With close

    .HeaderText = "Close"

    .Name = "close"

    .DataPropertyName = "Close"

    End With

    Dim NoOfTickets As New DataGridViewTextBoxColumn

    With NoOfTickets

    .HeaderText = "No. Of Tickets"

    .Name = "NoOfTickets"

    .DataPropertyName = "NoOfTickets"

    End With

    Dim sold As New DataGridViewTextBoxColumn

    With sold

    .HeaderText = "Sold"

    .Name = "Sold"

    .DataPropertyName = "Sold"

    End With







    LotteryGrid.CurrentCell = LotteryGrid.Rows(0).Cells(3)

    End Sub

    Now i want to update my table with the values in datagridview. I dont know how to do this so please help me!

    Tuesday, December 16, 2008 8:17 PM

All replies

  • Hello,

    Because you bound the grid to the LotteySale table ( LotteryGrid.DataSource = ds.Tables( "LotterySale" ) ) the LotterySale table should have all the changes made to the grid. If you need to inspect the values you can do so navigating through ds.Tables("LotterySale").Rows Collection.

    Please let me know if this helps
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, October 2, 2009 9:32 PM
  • Because your DataGridView is bound to DataTable you can retrieved the rows that you worked with (Most Efficient way) you don't need to iterate thru your rows collection.
    The best and Efficient way to do this is:




    If ds.HasChanges() Then


    Using Conn As New SqlConnection(My.Settings.MyConnection)


    'Get the Newly added Rows ONLY


    Dim dt_AddedRows As DataTable = ds.Tables("LotterySale").GetChanges(DataRowState.Added)

    'Get the Modified Rows ONLY


    Dim dt_ModifiedRows As DataTable = ds.Tables("LotterySale").GetChanges(DataRowState.Modified)


    'Get the deleted Rows ONLY


    Dim dt_DeletedRows As DataTable = ds.Tables("LotterySale").GetChanges(DataRowState.Deleted)


    Using Adapter As New SqlDataAdapter()


    If Not (dt_AddedRows Is Nothing) Then

    Adapter.InsertCommand =

    New SqlCommand("spI_LotterySales", Conn)

    Adapter.InsertCommand.CommandType = CommandType.StoredProcedure


    Dim myParmAdded As SqlParameter = Adapter.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int, 0, "@@ROWCOUNT")

    myParmAdded.Direction = ParameterDirection.ReturnValue


    With Adapter.InsertCommand.Parameters


    "@MSDesc", SqlDbType.NVarChar, 100, "MSDesc")


    "@ModifiedBy", SqlDbType.Int, 10, "ModifiedBy")


    "@ModifiedDate", SqlDbType.DateTime, 22, "ModifiedDate")


    End With


    'Get the parameter outputvalue that would be the Row Count and the ID

    myParmAdded = Adapter.InsertCommand.Parameters.Add(

    "@Identity", SqlDbType.Int, 0, "MSID")

    myParmAdded.Direction = ParameterDirection.Output


    End If


    If Not (dt_ModifiedRows Is Nothing) Then

    Adapter.UpdateCommand =

    New SqlCommand("spU_LotterySales", Conn)

    Adapter.UpdateCommand.CommandType = CommandType.StoredProcedure


    Dim myParmModified As SqlParameter = Adapter.UpdateCommand.Parameters.Add("@RowCount", SqlDbType.Int, 0, "@@ROWCOUNT")

    myParmModified.Direction = ParameterDirection.ReturnValue


    With Adapter.UpdateCommand.Parameters


    "@MSID", SqlDbType.Int, 5, "MSID").SourceVersion = DataRowVersion.Original


    "@MSDesc", SqlDbType.NVarChar, 100, "MSDesc").SourceVersion = DataRowVersion.Current


    "@ModifiedBy", SqlDbType.Int, 10, "ModifiedBy").SourceVersion = DataRowVersion.Current


    "@ModifiedDate", SqlDbType.DateTime, 22, "ModifiedDate").SourceVersion = DataRowVersion.Current


    End With


    'Get the parmater outputvalue

    myParmModified = Adapter.UpdateCommand.Parameters.Add(

    "@Identity", SqlDbType.Int, 5, "MSID")

    myParmModified.Direction = ParameterDirection.Output


    End If


    If Not (dt_DeletedRows Is Nothing) Then

    Adapter.DeleteCommand =

    New SqlCommand("spD_LotterySales", Conn)

    Adapter.DeleteCommand.CommandType = CommandType.StoredProcedure


    'Get the parameter Return Value


    Dim myParmDeleted As SqlParameter = Adapter.DeleteCommand.Parameters.Add("@RowCount", SqlDbType.Int, 0, "@@ROWCOUNT")

    myParmDeleted.Direction = ParameterDirection.ReturnValue


    With Adapter.DeleteCommand.Parameters


    "@MSID", SqlDbType.Int, 5, "MSID")


    End With


    End If



    End Using 'Adapter Using Block


    End Using 'Connection Using Block


    End If


    Catch ex As Exception


    "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)


    End Try


    End Sub

    • Proposed as answer by Codernater Tuesday, November 3, 2009 2:14 AM
    Thursday, October 8, 2009 7:21 PM