none
Updating issued in DGV RRS feed

  • Question

  • hii,

    I am trying to update my sql database in datagridview when i update any value in column it reflects other cells to. though i only want to update selected row only here is my code

    Private Sub btnupdate_Click(sender As Object, e As EventArgs) Handles btnupdate.Click
            Dim cmd As SqlCommand
            Try
                If Me.DataGridView1.Rows(0).Cells(0).Value = "" Then
                    MessageBox.Show("There Is No Data To Add !! Please Input Relevent Data !!", "team 5", MessageBoxButtons.OK, MessageBoxIcon.Error)
                ElseIf Me.txtinvoce.Text = "" Then
                    MessageBox.Show("Please Enter Invoice No.", "team 5", MessageBoxButtons.OK, MessageBoxIcon.Error)
    
                Else
    
    
                    con.Open()
                    Dim i As Integer = Me.DataGridView1.CurrentRow.Index
                    cmd = New SqlCommand("UPDATE MASTERPURCHASE set code=@id,vendorname=@vn,vendorgstn=@vg,date=@dt,productname=@pn,mrp=@mrp,disc=@dsc,netprice=@np,qty=@qty,nettotal=@nt,taxrate=@tr,cgst=@c,sgst=@s,igst=@i,gtotal=@gt WHERE invoiceno=@inv", con)
                    cmd.Parameters.Add("@id", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(0).Value.ToString()
                    cmd.Parameters.Add("@vn", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(1).Value.ToString()
                    cmd.Parameters.Add("@vg", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(2).Value.ToString()
                    cmd.Parameters.Add("@dt", SqlDbType.Date).Value = DataGridView1.Rows(i).Cells(3).Value.ToString()
                    cmd.Parameters.Add("@inv", SqlDbType.NVarChar).Value = txtinvoce.Text
                    cmd.Parameters.Add("@pn", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(5).Value.ToString()
                    cmd.Parameters.Add("@mrp", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(6).Value.ToString()
                    cmd.Parameters.Add("@dsc", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(7).Value.ToString()
                    cmd.Parameters.Add("@np", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(8).Value.ToString()
                    cmd.Parameters.Add("@qty", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(9).Value.ToString()
                    cmd.Parameters.Add("@nt", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(10).Value.ToString()
                    cmd.Parameters.Add("@tr", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(11).Value.ToString()
                    cmd.Parameters.Add("@c", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(12).Value.ToString()
                    cmd.Parameters.Add("@s", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(13).Value.ToString()
                    cmd.Parameters.Add("@i", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(14).Value.ToString()
                    cmd.Parameters.Add("@gt", SqlDbType.NVarChar).Value = DataGridView1.Rows(i).Cells(15).Value.ToString()
    
                    cmd.ExecuteNonQuery()
                    ' Next
                    'Next
                    con.Close()
                    MessageBox.Show("Data updated")
                End If
            Catch ex As Exception
                MessageBox.Show("failed")
            End Try
    
    
    

    Monday, September 24, 2018 10:23 AM

All replies

  • Looks to me like your WHERE condition is on Invoice number and each row has the same invoice number which sound to me like why other rows are being updated (if that is the case).

    To properly do this you need a primary key so that each row has a different key thus provides the ability to only update one row.

    That is what's needed unless I misinterpreted your issue.

    Lastly, since your data is coming from a database you should load the DataGridView via a DataTable coupled with a BindingSource.

    To get the current underlying data row with a BindingSource

    Dim Row As DataRow = CType(SomeBindingSource.Current,DataRowView).Row

    Get at a field

    Row.Field(Of Integer)("Qty")


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, September 24, 2018 11:07 AM
    Moderator
  • Hi,

    You can try my method:

    Modify  'Name' Column

    Imports System.Data.SqlClient
    Public Class Form1
        Dim adp As SqlDataAdapter
        Dim CommandeSQLSelect As String
        Private BS As New BindingSource
        Dim DT As DataTable = New DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
            Dim ConnectionSQL As SqlConnection = New SqlConnection(constr)
            CommandeSQLSelect = "Select * From Student"
            adp = New SqlDataAdapter(CommandeSQLSelect, ConnectionSQL)
            Dim SQLCommandBuild As SqlCommandBuilder = New SqlCommandBuilder(adp)
            adp.Fill(DT)
            BS.DataSource = DT
            DataGridView1.DataSource = DT
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            DT.Rows(DataGridView1.CurrentRow.Index).Item("Name") = TextBox1.Text.ToString
            adp.Update(DT)
        End Sub
    End Class
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 25, 2018 8:50 AM