none
update records from datagridview column RRS feed

  • Question

  • i am working on "cash payment" module 

    and here i am entering 5 accounts which will be debit after i save it. now i am using below code that will update all accounts at ones but can you check what mistake im doing becz this code is not working it saving the record but not hitting the accounts .

    For Each row As DataGridViewRow In CashpaymentDataGridView.Rows
                    If Not row.IsNewRow Then
                        con = New SqlConnection(appx.myconnection)
                        con.Open()
                        Dim ctx As String = "select Account_Title from COA where Account_Title=@d1 and Account_Title=@d2"
                        cmd = New SqlCommand(ctx)
                        cmd.Connection = con
                        cmd.Parameters.AddWithValue("@d1", row.Cells(3).Value)
                        cmd.Parameters.AddWithValue("@d2", row.Cells(5).Value)
                        rdr = cmd.ExecuteReader()
                        If (rdr.Read()) Then
    
                            con = New SqlConnection(appx.myconnection)
                            con.Open()
                            Dim cb2 As String = "Update COA set opening_balance = opening_balance + " & row.Cells(3).Value & " where AccountID=@d1"
                            Dim cb3 As String = "Update COA set opening_balance = opening_balance - " & row.Cells(5).Value & " where AccountID=@d2"
                            cmd = New SqlCommand(cb2 And cb3)
                            cmd.Connection = con
                            cmd.Parameters.AddWithValue("@d1", (row.Cells(3).Value))
                            cmd.Parameters.AddWithValue("@d2", row.Cells(5).Value)
                            cmd.ExecuteReader()
                            con.Close()
                        End If
                    End If

    Friday, May 31, 2019 7:58 AM

Answers

All replies

  • Hi,
    try this. In this code the Cells(3) contains the id and the Cells(5) contains the value to be added.

                            Using cn As New SqlConnection(appx.myconnection)
                              cn.Open()
    			  Using com As New SqlCommand("", cn)
                                com.CommandText = "Update COA set opening_balance = opening_balance + @d1 where AccountID=@id"
                                com.Parameters.AddWithValue("@id", row.Cells(3).Value)
                                com.Parameters.AddWithValue("@d1", row.Cells(5).Value)
                                com.ExecuteNonQuery
    			  End Using
                            End Using


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Friday, May 31, 2019 10:07 AM
  • error 

    ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

    also in your code it will effect one account but in cash payment always 2 accounts get effected

    cash in hand will be credit 

    other account will be debit 

    so update codes will be 2 from datagridview 

    cell 3 and cell 5 are columns that has accounts ID numbers 


    • Edited by Omi4u Friday, May 31, 2019 10:39 AM
    Friday, May 31, 2019 10:22 AM
  • Hello,

    What Peter posted is syntactically correct in that ExecuteNonReader is for updates while ExecuteReader is for reading.

    If Not row.IsNewRow Then
    	con = New SqlConnection(appx.myconnection)
    	con.Open()
    	Dim ctx As String = "select Account_Title from COA where Account_Title=@d1 and Account_Title=@d2"
    	cmd = New SqlCommand(ctx)
    	cmd.Connection = con
    	cmd.Parameters.AddWithValue("@d1", row.Cells(3).Value)
    	cmd.Parameters.AddWithValue("@d2", row.Cells(5).Value)
    	rdr = cmd.ExecuteReader()
    	If (rdr.Read()) Then
    
    		con = New SqlConnection(appx.myconnection)
    		con.Open()
    		Dim cb2 As String = "Update COA set opening_balance = opening_balance + " & row.Cells(3).Value & " where AccountID=@d1"
    		Dim cb3 As String = "Update COA set opening_balance = opening_balance - " & row.Cells(5).Value & " where AccountID=@d2"
    		cmd = New SqlCommand(cb2 And cb3)
    		cmd.Connection = con
    		cmd.Parameters.AddWithValue("@d1", (row.Cells(3).Value))
    		cmd.Parameters.AddWithValue("@d2", row.Cells(5).Value)
    		cmd.ExecuteNonQuery()
    		con.Close()
    	End If
    End If
    
    

    A better method is to have the connection with the for inside of it, open the connection then do the for, this way the connection is live for all work and no need to close when working with a using statement such as

    Using cn As New SqlConnection With {.ConnectionString = appx.myconnection}


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, May 31, 2019 10:56 AM
    Moderator
  •   

    ok i fixed it please check this code is again not working .

    i want to update two account in COA

    cash account will be credit and other account will be debit

    For Each row As DataGridViewRow In CashpaymentDataGridView.Rows

                Using cn As New SqlConnection With {.ConnectionString = appx.myconnection}

                    cn.Open()

                    Using com1 As New SqlCommand("", con)
                        'cn.Open()

                        com1.CommandText = "Update COA set opening_balance = opening_balance + @d30 where AccountID=@id"

                        cmd1.Parameters.AddWithValue("@id", row.Cells(3).Value)
                        cmd1.Parameters.AddWithValue("@d30", row.Cells(5).Value)
                        cmd1.ExecuteNonQuery()


                        '    cn.Close()
                    End Using
                    con.Close()
                End Using

    Friday, May 31, 2019 11:14 AM
  • Hi,
    I correct the code (com instead of cmd).

    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Friday, May 31, 2019 11:48 AM
  • Hello,

    The following is an example i wrote that is a conceptual example meaning it is not plug and use.

    The idea to increment a value is to use the current value plus the value to increase the field by. Yes it's hard code and yes there is no DataGridView as this is an example to learn from and apply to your code.

    Dim ConnectionString = "Data Source=KARENS-PC;Initial Catalog=ForumExample;Integrated Security=True"
    Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
    	Dim insertStatement =
    		"UPDATE ForumExample.dbo.Items " &
    		"SET itemavailablestock = itemavailablestock + @NewValue " &
    		"WHERE ItemNumber = @ItemNumber;"
    
    	Using cmd As New SqlCommand With {.Connection = cn, .CommandText = insertStatement}
    		cmd.Parameters.AddWithValue("@ItemNumber", 101)
    		cmd.Parameters.AddWithValue("@NewValue", 1)
    		cn.Open()
    		cmd.ExecuteNonQuery()
    	End Using
    End Using

    If you want to check if the update worked where you would replace the Console.WriteLine with logic to know there was a failure.

    Dim result = cmd.ExecuteNonQuery()
    If result = 1 Then
        Console.WriteLine("Success")
    Else
        Console.WriteLine("Failed")
    End If


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, May 31, 2019 11:54 AM
    Moderator
  • Hi,
    try this:

    Using cn As New SqlConnection(appx.myconnection) cn.Open() Using com As New SqlCommand("", cn) com.CommandText = "Update COA set opening_balance = opening_balance + @debit - @credit where AccountID=@id" com.Parameters.AddWithValue("@id", ? where is the id?) com.Parameters.AddWithValue("@debit", row.Cells(3).Value) com.Parameters.AddWithValue("@credit", row.Cells(5).Value)
    com.ExecuteNonQuery End Using
    ' cn.Close is not required
    End Using



    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Friday, May 31, 2019 11:56 AM
  • dear peter thank you for your code but still my point is not cleared so i attach an screenshot of my table 

    now here COA FROM AND PAYMENT FROM are CASH IN ACCOUNT account which will be credit by total amount

    COA TO AND PAYMENT TO are debit accounts which will be debit by actual amount

    COA TO AND COA FROM are the COA ACCOUNT ID  and vb code will update both according to COA ACCOUNT ID NUMBER

    so when user press SAVE 

    all account ID under COA FROM which is default cash in hand will get credit by actual value

    and all account ID under COA To Will get debit to actual amount but not with total

    example entry

    coa id                      description                           dr             cr                total

    30141                        test account supplier            5000                

           30162                        cash in hand                              5000                  5000

    30141                        test account other            5000                

           30162                        cash in hand                              5000                  5000

    30141                        test new                              5000                

           30162                        cash in hand                              5000                  5000

    now total of all entries which will be credit to cash in hand is 15000 and with vb code 5000 of each account will be debited when user will press save and it will be done by using COA TO column in datagridview

    Friday, May 31, 2019 12:12 PM
  • Hi,
    try this:

                            Using cn As New SqlConnection(appx.myconnection)
                              cn.Open()
    			  Using com As New SqlCommand("", cn)
                                com.CommandText = "Update COA set opening_balance = opening_balance + @dr where AccountID=@id"
                                com.Parameters.AddWithValue("@id", row.Cells(3).Value)
                                com.Parameters.AddWithValue("@dr", row.Cells(7).Value)
                                com.ExecuteNonQuery
                                com.CommandText = "Update COA set opening_balance = opening_balance - @dr where AccountID=@id"
    			    com.Parameters("@id").Value = row.Cells(5).Value
    			    com.Parameters("@dr").Value = row.Cells(7).Value
                                com.ExecuteNonQuery
    			  End Using
    ' cn.Close is not required
                            End Using


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Friday, May 31, 2019 1:10 PM
  • no peter  not working nor showing any error nor effecting accounts
    Saturday, June 1, 2019 5:51 AM
  • i solved the issue bro thank you for your support 
    • Marked as answer by Omi4u Saturday, June 1, 2019 7:17 AM
    Saturday, June 1, 2019 7:17 AM