none
how to update record in datagridview?

    Question

  • Dim table As DataTable = dataSet.Tables("Customers") ' First process deletes. dataSet.Update(table.Select(Nothing, Nothing, _ DataViewRowState.Deleted)) ' Next process updates. adapter.Update(table.Select(Nothing, Nothing, _ DataViewRowState.ModifiedCurrent)) ' Finally, process inserts. dataAdapater.Update(table.Select(Nothing, Nothing, _ DataViewRowState.Added))

    can someone explain what is the dataset and adapter? is it the db dataset?

    i need to declare it first before using it?

    Wednesday, December 11, 2013 2:34 PM

All replies

  • Hello,

    You might consider reading the following page and this page.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Wednesday, December 11, 2013 3:11 PM
    Moderator
  • Hello,

    You might consider reading the following page and this page.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Hi,

    As Kevin suggested, please refer to these articles to learn what is Dataset and Adapter and how to use it:)

    The ADO.NET DataSet is a memory-resident representation of data that provides a consistent relational programming model independent of the data source. The DataSet represents a complete set of data that includes tables, constraints, and relationships among the tables. Because the DataSet is independent of the data source, a DataSet can include data local to the application, and data from multiple data sources. Interaction with existing data sources is controlled through the DataAdapter.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 12, 2013 9:01 AM
    Moderator
  • Hi,

    Imports System.Data.SqlClient
     
    Public Class Form1
        Dim da As SqlDataAdapter
        Dim ds As DataSet
     
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e AsSystem.EventArgs) Handles MyBase.Load
            Dim con As SqlConnection = New SqlConnection("Data Source=.;Integrated Security=True;AttachDbFilename=|DataDirectory|\SqlDatabase.mdf")
            Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM [TABLE]", con)
            con.Open()
            da = New SqlDataAdapter(cmd)
            'Automatically generates DeleteCommand, UpdateCommand and InsertCommand for 
    DataAdapter object
            Dim cmdBuil As SqlCommandBuilder = New SqlCommandBuilder(da)
     
            ds= New DataSet()
            da.Fill(myDataSet, "Table")
            DataGridView1.DataSource = myDataSet.Tables("Table").DefaultView
     
            con.Close()
            con = Nothing
        End Sub
       
        ' Save data from DataGridView into Database
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e AsSystem.EventArgs) Handles Button1.Click
            Me.Validate()
            Me.da.Update(Me.ds.Tables("Table"))
            Me.ds.AcceptChanges()
        End Sub
     
    End Class
    
    Cheers,

    Friday, December 13, 2013 6:27 AM
  • hi pilot,

    this is one code for insert and update(modify) records into datagridview:

    Imports System.Data.SqlClient

    Public Class unit1

        Const connectionstring As String = "Data Source='Accounting-pc';Initial Catalog='Rasol';Integrated Security='true';"

        Private SQL As String = "select unit_name from unit order by unit_id"

        Private con As New SqlConnection(connectionstring)

        Private dt As New DataTable

        Private adapter As New SqlDataAdapter(Sql, con)

        Private commandbuilder As New SqlCommandBuilder(adapter)

        Private Sub unit1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            Try

                adapter.Fill(dt)

                DataGridView1.DataSource = dt

            Catch ex As Exception

                MsgBox(ex.Message)

            End Try

        End Sub

        Private Sub DataGridView1_RowLeave(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowLeave

            Try

                adapter.Update(dt)

                DataGridView1.DataSource = dt

            Catch ex As Exception

                MsgBox(ex.Message)

            End Try

        End Sub

    best regards.


    • Proposed as answer by Cor LigthertMVP Friday, December 13, 2013 7:22 AM
    • Edited by rasol Friday, December 13, 2013 7:23 AM
    Friday, December 13, 2013 7:17 AM
  • I see no really wrong replies so far in this thread. Therefore some additions.

    A database and therefore a dataset is meant to be used in a multi user environment.

    Therefore do in those situations a clear of the table you've updated (or if it is the complete dataset a complete clear)

    And then do again a fill to get allowed changes done by other users.

    The DataAdapter does the following

    opens a connection if that is closed
    Does the action
    Does an acceptchanges of the used datatable
    Does a Close (if it is opened by the dataset) of the connection.

    Take care when for the update a real datatable copy is used.

    The DataAdapter is a class starting in framework 0.9 therefore it is in fact currently mainly a base class.

    Take a look at the TableAdapterManager which solves a lot of problems which do exist when you start really to use it.

     http://msdn.microsoft.com/en-us/library/vstudio/bb384426.aspx

    And as last but most important, take always a subset of the database using a where clause in the select. The cursive written text from Franklin often misunderstood that it is meant to take a complete database in memory.


    Success
    Cor




    Friday, December 13, 2013 7:32 AM
  • Hello Cor Lighthert,

    In your infinite wisdom could you explain me, why

    "Therefore do in those situations a clear of the table you've updated (or if it is the complete dataset a complete clear)

    And then do again a fill to get allowed changes done by other users."

    ?

    Couldn't you just add a select statement after your update/insert/delete statement ?

    Friday, December 13, 2013 7:47 AM
  • Hello Cor Lighthert,

    In your infinite wisdom could you explain me, why

    "Therefore do in those situations a clear of the table you've updated (or if it is the complete dataset a complete clear)

    And then do again a fill to get allowed changes done by other users."

    ?

    Couldn't you just add a select statement after your update/insert/delete statement ?

    A fill is executing a new select, while the update does only return a value which tells if the sql query is succesful done. 


    Success
    Cor


    Saturday, December 14, 2013 2:00 PM
  • i have try this.. but, still.. it did not update what i wanted to. :(

    ' to do coding renew thesis
        Private Sub renewBtn_Click(sender As Object, e As EventArgs) Handles renewBtn.Click
    
            Dim cmdupdate As OleDbCommand = New OleDbCommand
    
            If idsearchtxt.Text <> "" And namerenewl.Text <> "" And Lemail.Text <> "" And icNoRenew.Text <> "" And codeLabel.Text <> "" And renewTitleL.Text <> "" And authorRenew.Text <> "" And yearRenew.Text <> "" And renewDtp.Text <> "" And renewDue.Text <> "" Then
    
                cmdupdate.CommandText = "UPDATE Info SET [Stud Name] = '" & namerenewl.Text & "'," _
               & "[Email] = '" & Lemail.Text & "'," _
               & "[IC No] = '" & icNoRenew.Text & "'," _
               & "[Thesis Code] = '" & codeLabel.Text & "'," _
              & "[Title] = '" & renewTitleL.Text & "'," _
              & "[Author] = '" & authorRenew.Text & "'," _
              & "[Year] = '" & yearRenew.Text & "'," _
             & "[Date borrow] = '" & renewDtp.Text & "'," _
            & "[Due date] = '" & renewDue.Text & "'," _
           & "WHERE name = " & idsearchtxt.Text & ""
                con.Open()
                ' cmdupdate = New OleDbCommand(cmdupdate, con)
                cmdupdate.CommandType = CommandType.Text
                cmdupdate.Connection = con
                cmdupdate.ExecuteNonQuery()
    
                MsgBox(idsearchtxt.Text = "Record updated.")
                namerenewl.Text = ""
                Lemail.Text = ""
                icNoRenew.Text = ""
                codeLabel.Text = ""
                renewTitleL.Text = ""
                authorRenew.Text = ""
                yearRenew.Text = ""
                renewDtp.Text = ""
                renewDue.Text = ""
    
            End If
            cmdupdate.Dispose()
            con.Close()
    
            Try
                Me.BorrowThesisTableAdapter.Insert(idsearchtxt.Text, namerenewl.Text, Lemail.Text, icNoRenew.Text, codeLabel.Text, renewTitleL.Text, authorRenew.Text, yearRenew.Text, renewDtp.Text, renewDue.Text)
                Me.BorrowThesisTableAdapter.Fill(Me.ThesisDBDataSet.BorrowThesis)
                Me.BorrowThesisTableAdapter.Update(Me.ThesisDBDataSet.BorrowThesis)
    
    
            Catch ex As Exception
                MsgBox("Renew record saved.", MsgBoxStyle.Information)
    
            End Try
    
    
        End Sub

    Tuesday, December 24, 2013 8:00 AM