使用 vb.net 更新 gridview 中的数据

Updating data in gridview using vb.net

提问by Muazzam

First I populated the datagridview by data of sqlserver 2008 database table, now i have muliple rows in datagridview containing data, i am trying to update any row but, in the database table it replaces other rows data by the row data that iam trying to update the code for update statement is given below

首先，我通过 sqlserver 2008 数据库表的数据填充了 datagridview，现在我在包含数据的 datagridview 中有多个行，我试图更新任何行，但是，在数据库表中，它用我试图更新的行数据替换其他行数据下面给出了更新语句的代码

Plz help me

请帮助我

Dim cmd As New SqlCommand("Update EmployeeDetail Set Salary = '" &      
dgvEmpDetail.Rows(0).Cells(1).Value & "', Experience ='" &  
dgvEmpDetail.Rows(0).Cells(2).Value & "', Skills='" &  
dgvEmpDetail.Rows(0).Cells(3).Value 
& "' where Emp_ID = '" & dgvEmpDetail.Rows(0).Cells(0).Value & "'", con)
con.Open()                                                         
cmd.ExecuteNonQuery()     
con.Close()

回答by Sam Makin

You've hard coded the row - dgvEmpDetail.Rows(0).

您已对该行进行了硬编码 - dgvEmpDetail.Rows(0)。

I imagine you are calling this in a loop. You should do something like:

我想你是在循环调用它。你应该做这样的事情：

    For i As Integer = 0 To dgvEmpDetail.Rows.Count - 1
        Dim cmd As New SqlCommand("Update EmployeeDetail Set Salary = '" & dgvEmpDetail.Rows(i).Cells(1).Value & "', Experience ='" & dgvEmpDetail.Rows(i).Cells(2).Value & "', Skills='" & dgvEmpDetail.Rows(i).Cells(3).Value()& "' where Emp_ID = '" & dgvEmpDetail.Rows(i).Cells(0).Value & "'", con)
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    Next

Your code is susceptible to SQL injection. You should put the update SQL in to a stored procedure - its faster and safer!

您的代码容易受到 SQL 注入的影响。您应该将更新 SQL 放入存储过程 - 它更快更安全！

回答by Vinu Vasahan

  Protected Sub Page_Load()

    If Not Page.IsPostBack Then
      ' Create a new table.
      Dim taskTable As New DataTable("TaskList")

      ' Create the columns.
      taskTable.Columns.Add("Id", GetType(Integer))
      taskTable.Columns.Add("Description", GetType(String))
      taskTable.Columns.Add("IsComplete", GetType(Boolean))

      'Add data to the new table.
      For i = 0 To 19
        Dim tableRow = taskTable.NewRow()
        tableRow("Id") = i
        tableRow("Description") = "Task " + i.ToString()
        tableRow("IsComplete") = False
        taskTable.Rows.Add(tableRow)
      Next

      'Persist the table in the Session object.
      Session("TaskTable") = taskTable

      'Bind data to the GridView control.
      BindData()
    End If

  End Sub

  Protected Sub TaskGridView_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    TaskGridView.PageIndex = e.NewPageIndex
    'Bind data to the GridView control.
    BindData()
  End Sub

  Protected Sub TaskGridView_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
    'Set the edit index.
    TaskGridView.EditIndex = e.NewEditIndex
    'Bind data to the GridView control.
    BindData()
  End Sub

  Protected Sub TaskGridView_RowCancelingEdit()
    'Reset the edit index.
    TaskGridView.EditIndex = -1
    'Bind data to the GridView control.
    BindData()
  End Sub

  Protected Sub TaskGridView_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    'Retrieve the table from the session object.
    Dim dt = CType(Session("TaskTable"), DataTable)

    'Update the values.
    Dim row = TaskGridView.Rows(e.RowIndex)
    dt.Rows(row.DataItemIndex)("Id") = (CType((row.Cells(1).Controls(0)), TextBox)).Text
    dt.Rows(row.DataItemIndex)("Description") = (CType((row.Cells(2).Controls(0)), TextBox)).Text
    dt.Rows(row.DataItemIndex)("IsComplete") = (CType((row.Cells(3).Controls(0)), CheckBox)).Checked

    'Reset the edit index.
    TaskGridView.EditIndex = -1

    'Bind data to the GridView control.
    BindData()
  End Sub

  Private Sub BindData()
    TaskGridView.DataSource = Session("TaskTable")
    TaskGridView.DataBind()
  End Sub

</script>

回答by Vinu Vasahan

I have a access connection with textbox as data feeder to database change it to SQL if u want. The code is:

我有一个与文本框的访问连接，作为数据馈送器到数据库，如果你愿意，可以将其更改为 SQL。代码是：

Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class Form2
    Dim conaccess As New OleDbConnection
    Dim conreader As OleDbDataReader
    Dim concmd As New OleDbCommand


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

        DataGridView1.EditMode = False
        conaccess.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;data source=d:\vijay.mdb"
        conaccess.Open()
        loadGrid()
    End Sub

    Private Sub loadGrid()
        Dim access As String
        access = "select * from vijay"
        Dim DataTab As New DataTable
        Dim DataAdap As New OleDbDataAdapter(access, conaccess)
        DataAdap.Fill(DataTab)
        DataGridView1.DataSource = DataTab
    End Sub

    Private Sub new_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles new_btn.Click

        Dim no As String
        no = "select Max(ID) from vijay"
        Dim concmd As New OleDbCommand(no, conaccess)
        conreader = concmd.ExecuteReader
        If (conreader.Read) Then
            If (IsDBNull(conreader(0))) Then
                id_txt.Text = "1"
            Else
                id_txt.Text = conreader(0) + 1
            End If
            name_txt.Clear()
            branch_txt.Clear()
            age_txt.Clear()
            class_txt.Clear()
            gen_txt.Clear()
        End If
    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Dim i As Integer

        i = DataGridView1.CurrentRow.Index
        Try
            id_txt.Text = DataGridView1.Item(0, i).Value
            name_txt.Text = DataGridView1.Item(1, i).Value
            class_txt.Text = DataGridView1.Item(2, i).Value
            gen_txt.Text = DataGridView1.Item(3, i).Value
            branch_txt.Text = DataGridView1.Item(4, i).Value
            age_txt.Text = DataGridView1.Item(5, i).Value
        Catch ex As Exception

        End Try

    End Sub


    Private Sub del_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles del_btn.Click
        Dim delcmd As New OleDbCommand("delete from vijay where id=" & id_txt.Text & " ", conaccess)
        delcmd.ExecuteNonQuery()
        MsgBox("Record is deleted")
        loadGrid()
        id_txt.Clear()
        name_txt.Clear()
        branch_txt.Clear()
        age_txt.Clear()
        class_txt.Clear()
        gen_txt.Clear()
    End Sub

    Private Sub save_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save_btn.Click
        Dim access As String = String.Format("INSERT INTO vijay (Name,Class,Branch,Gender,Age) VALUES('{0}','{1}','{2}','{3}','{4}')", name_txt.Text, class_txt.Text, branch_txt.Text, gen_txt.Text, age_txt.Text)
        concmd.Connection = conaccess
        concmd.CommandText = access
        concmd.ExecuteNonQuery()
        MsgBox("Record Successfully Saved")
        loadGrid()
        id_txt.Clear()
        name_txt.Clear()
        branch_txt.Clear()
        age_txt.Clear()
        class_txt.Clear()
        gen_txt.Clear()
    End Sub

    Private Sub up_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles up_btn.Click
        Dim access As String
        access = "UPDATE vijay SET Name = '" & name_txt.Text & "', Age = '" & age_txt.Text & "', Gender ='" & gen_txt.Text & "' , Branch ='" & branch_txt.Text & "' , Class = '" & class_txt.Text & "' where id=" & id_txt.Text & ""
        Dim cmd As New OleDbCommand(access, conaccess)
        cmd.ExecuteNonQuery()
        loadGrid()
        id_txt.Clear()
        name_txt.Clear()
        branch_txt.Clear()
        age_txt.Clear()
        class_txt.Clear()
        gen_txt.Clear()

    End Sub
End Class

回答by Rezza Prayogi

Use looping and parameter (to handle sql injection):

使用循环和参数（处理sql注入）：

 con.Open() 'Open connection to database

 'Looping throung dgv
 For i As Integer = 0 To dgvEmpDetail.Rows.Count - 1
    If IsDBNull(dgvEmpDetail.Rows(i).Cells("Emp_ID").Value) Then Exit For
    Dim cmd As New SqlCommand("Update EmployeeDetail Set [Salary] = ?, [Experience]=?, [Skills]=? WHERE [Emp_ID] =?", con)

    With cmd.Parameters
       .AddWithValue("@Salary", dgvEmpDetail.Rows(i).Cells("Salary").Value )
       .AddWithValue("@Experience", dgvEmpDetail.Rows(i).Cells("Experience").Value )
       .AddWithValue("@Skills", dgvEmpDetail.Rows(i).Cells("Skills").Value )
       .AddWithValue("@Emp_ID", dgvEmpDetail.Rows(i).Cells("Emp_ID").Value )
    End With
    cmd.ExecuteNonQuery()

Next i

con.Close() 'Close connection with Database