none
batch update in datagridview RRS feed

  • Question

  • Hi all,
    i m new in vb.net. I have a table with following columns:-
    date,name,price,open,close,sold.
    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.Columns.Add(name)

    LotteryGrid.Columns.Add(price)

    LotteryGrid.Columns.Add(open)

    LotteryGrid.Columns.Add(close)

    LotteryGrid.Columns.Add(NoOfTickets)

    LotteryGrid.Columns.Add(sold)

    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
    Moderator
  • 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:

     

    Try

     

    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

    .Add(

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

    .Add(

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

    .Add(

    "@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

    .Add(

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

    .Add(

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

    .Add(

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

    .Add(

    "@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

    .Add(

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

     

    End With

     

    End If

    Adapter.Update(dtMARKETSEGMENTATION)

     

    End Using 'Adapter Using Block

     

    End Using 'Connection Using Block

     

    End If

     

    Catch ex As Exception

    MessageBox.Show(ex.Message,

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

     

    End Try

     

    End Sub




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