IF Statement

Hi,

I have the following function, which when exceuted doesn't do the following -


a) Update if the checkbox on the datagrid is changed (Cell(3)).

b) Insert data when the prac_no doesn't exist in the database..

Any help will be highly apprecaited

Code:

---

Private Sub UpdatePraclist()

        Dim conn As SqlConnection = GetDbConnection()
        Dim cmd As New SqlCommand
        Dim prac_no As Integer
        Dim prac_enabled As String = Nothing
        Dim irow As Integer
        Dim strFlag As String = "Insert"

        For irow = 0 To DgvPracExcl.Rows.Count - 1

            'Read the practice number and status from TblPracExclude
            Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude"), conn)
            Try
                Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
                    While Autoreader.Read()

                        prac_no = Autoreader.GetValue(0)
                        prac_enabled = Autoreader.GetValue(1)

                    End While
                End Using
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
            End Try


            If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
                If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
                    MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                    strFlag = "Ignore"
                    Exit For
                Else
                    MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                    strFlag = "Update"
                    Exit For
                End If
            End If

        Next irow

        If strFlag = "Insert" Then
            Call PracticeInsert()
        ElseIf strFlag = "Update" Then
            Call UpdatePracEnabled()
        End If

    End Sub

---

Code:

---

Private Sub UpdatePracEnabled()

        Dim conn As SqlConnection = GetDbConnection()
        Dim query As String
        Dim cmd As New SqlCommand

        Dim irow As Integer

        Try
            query = "UPDATE dbo.TblPracExclude  SET prac_enabled  ='" & _
                      DgvPracExcl.Rows(irow).Cells(3).Value & "' where Prac_No='" & _
                      DgvPracExcl.Rows(irow).Cells(0).Value & "'"
            cmd = New SqlCommand(query, conn)
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
        End Try

    End Sub

---

Code:

---

Private Sub PracticeInsert()

        Dim conn As SqlConnection = GetDbConnection()
        Dim query As String
        Dim cmd As New SqlCommand

        Dim irow As Integer

            Try
                query = "INSERT INTO dbo.TblPracExclude (prac_no, prac_name, prac_status, prac_enabled) VALUES (" & _
                                  DgvPracExcl.Rows(irow).Cells(0).Value & ", '" & _
                                  Replace(DgvPracExcl.Rows(irow).Cells(1).Value, "'", "''") & "', '" & _
                                  DgvPracExcl.Rows(irow).Cells(2).Value & "', '" & _
                                  DgvPracExcl.Rows(irow).Cells(3).Value & "')"

                cmd = New SqlCommand(query, conn)
                cmd.ExecuteNonQuery()


            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
            End Try

    End Sub

---

Instead - I keep receiving the message box;
No Practice need Update


Please could you help me out..

Code:

---

While Autoreader.Read()

                        prac_no = Autoreader.GetValue(0)
                        prac_enabled = Autoreader.GetValue(1)

                    End While

---

Note that even though you are going through all the records in the table you are not doing anything with them, Only the last one will have an impact. Since your grid was most likely loaded from the same table then the last item in the table which is what you will end up with here is always going to match an item in the datagrid because it was placed there from the database orginally.

I think you need to step back and think about what you are tying to accomplish here and modify the design.

any hints ?

Quick shot here...

See what happens when you rearrange the code like this

Code:

---

            'Read the practice number and status from TblPracExclude
            Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude"), conn)
            Try
                Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
                    While Autoreader.Read()

                        prac_no = Autoreader.GetValue(0)
                        prac_enabled = Autoreader.GetValue(1)

      For irow = 0 To DgvPracExcl.Rows.Count - 1


            If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
                If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
                    MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                    strFlag = "Ignore"
                    Exit For
                Else
                    MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                    strFlag = "Update"
                    Exit For
                End If
            End If

        Next irow

                    End While
                End Using
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
            End Try


---

and before anyone shouts... Yes the code is not 100% fixed, Just showing how Nested loops are used for this sorta problem...

Ok - I have tried this....

Code:

---

Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude"), conn)
        Try
            Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
                While Autoreader.Read()

                    prac_no = Autoreader.GetValue(0)
                    prac_enabled = Autoreader.GetValue(1)


                    For irow = 0 To DgvPracExcl.Rows.Count - 1

                        If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
                            If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
                                MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                                strFlag = "Ignore"
                                Exit For
                            Else
                                MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                                strFlag = "Update"
                                Exit For
                            End If
                        End If

                    Next irow

                End While
            End Using
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
        End Try

---

I receive the Message:

No Practice need Update.

When I click OK, I do receive another similar message and so on.

When I changed - DgvPracExcl.Rows(irow).Cells(3).Value from True to False.

I still received the same message - No Practice need Update..

My expectation is to show message - Practice need Update and call the UpdatePracEnabled() function..

Please help me to fix this..

Many thanks

Quote:

---

Originally Posted by dr223

I receive the Message:

No Practice need Update.

When I click OK, I do receive another similar message and so on.

---

So now you should be reciving one for Each item in the list... and one of them will return what your looking for (you might have missed it)..

So now lets change a few more lines...

Code:

---

                                strFlag = "Ignore"

                While Autoreader.Read()

                    prac_no = Autoreader.GetValue(0)
                    prac_enabled = Autoreader.GetValue(1)

                    For irow = 0 To DgvPracExcl.Rows.Count - 1

                        If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
                            If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then

                              ' MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")

                                Exit For
                            Else
                                MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                                strFlag = "Update"
                                Exit For
                            End If
                        End If

                    Next irow

                End While

---

When I initially suggested that you structure your loop that way I had assumed that prac_no and Prac_Enadbled actually held the values of the new data that was being saved. It wasn't until afterwards that you showed the code where those variable were being assigned from the DB which changes the approach needed.


In keeping with the code from your last post you would need to do something like

Code:

---

Try
            Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
                Dim RecordsAffected as Integer=0
                While Autoreader.Read()
       
                    prac_no = Autoreader.GetValue(0)
                    prac_enabled = Autoreader.GetValue(1)

                    Dim FlagVar as String="Insert"
                    For irow = 0 To DgvPracExcl.Rows.Count - 1

                        If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
                            If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
                                'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                                strFlag = "Ignore"
                                Exit For
                            Else
                                'MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                                strFlag = "Update"
                                Exit For
                            End If
                        End If

                    Next irow
                    If FlagVar="Insert" then
                        'Call Insert Rountine
                        RecordsAffected=RecordsAffected+1
                    ElseIf FlagVar="Update"
                          'Call Update Routine
                          RecordsAffected=RecordsAffected+1
                    End If
                End While
            If RecordsAffected>0 then
                Msgbox RecordsAffected & " Records inserted or updated"
            Else
                Msgbox "No Records need updating"
            End If

            End Using
                    Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
        End Try

---

Be aware that this should work but looping through all the data in the database is not the way to go. If the database has a lot of records it would be very slow and would be slower with more records to the point of being unacceptable.

Ok - Tried DataMiser Suggestion and had a code as;

Code:

---


Try
            Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()

                Dim RecordsAffected As Integer = 0

                While Autoreader.Read()

                    prac_no = Autoreader.GetValue(0)
                    prac_enabled = Autoreader.GetValue(1)

                    Dim FlagVar As String = "Insert"

                    For irow = 0 To DgvPracExcl.Rows.Count - 1

                        If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
                            If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
                                'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                                FlagVar = "Ignore"
                                Exit For
                            Else
                                'MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                                  FlagVar = "Update"
                                Exit For
                            End If
                        End If

                    Next irow

                    If FlagVar = "Insert" Then
                        'Call PracticeInsert()
                        RecordsAffected = RecordsAffected + 1
                    ElseIf FlagVar = "Update" Then
                        Call UpdatePracEnabled()
                        RecordsAffected = RecordsAffected + 1
                    End If
           
           

                    If RecordsAffected > 0 Then
                        MsgBox(RecordsAffected & " Records inserted or updated")
                    Else
                        MsgBox("No Records need updating")
                    End If

                End While
            End Using


        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
        End Try

---

N/B:

1) The highlighted code was inserted before

End While
End Using

Otherwise I receive the error message:

Name 'RecordsAffected' is not declared.

2)

Code:

---

If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
                                'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                                FlagVar = "Ignore"
                                Exit For
                            Else
                                'MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                                FlagVar = "Update"
                                Exit For

---

Changed StrVar = FlagVar therefore
instead of StrVar = "ignore" changed it to FlagVar = "ignore"

3) I just want to get the Update function to work properly then I will look into the INSERT

Result at run time:

When I changed record 1 and clicked SAVE;

Message

1 Records inserted or updated Clicked OK Message still remains - clicked Ok and mesaage remains and so on. No effect to the table and prac_enabled was not changed from True to False as expected.

Ok - Unchecked other 3 records and clicked saved at run time..

Messages:

1 records inserted or updated
2 Records inserted or updated
3 Records inserted or updated - and remains at this message until I terminate the application.

Checked the database - Record 1 was changed from True to False as expected..

What am doing wrong?

Thank you

The message is popping up because you moved it to the wrong place. It needs to be outside the loop.

I don't know what you tried when I first posted there was an error in the code which I went back and edited to correct.

First draft the block for testing RecordsAffected was outside the End Using when it should have been between the End While and End Using as it is now in the edited version of my post.

Sorry about the FlagVar strFlag I did not notice that I had used strFlag before. The intention was for them to be the same but I was in a hurry.

Ok Thank you very much DataMiser - nearly there..

Test 1: When I don't perform any Change and click Ok

Message: No records need Updating. (CORRECT)!!

Test 2: Changed the prac_enabled Record 1 from True to False

Click Save - Message: No records need Updating (INCORRECT!!)

Test 3: Changed 3 records state from True to False

Click Save - Message: 2 Records inserted or updated.

Checked Database: Record 1 state changed True to False the other 2 records remain as TRUE. (INCORRECT)

Test 4: Opened the form and clicked saved -

Message: 1 Records inserted or updated.
Checked Database: Record 1 state was changed from False to True (CORRECT)

Also I think my update query is not right?

Code:

---


 query = "UPDATE dbo.TblPracExclude  SET prac_enabled  ='" & _
                      DgvPracExcl.Rows(irow).Cells(3).Value & "' where Prac_No='" & _
                      DgvPracExcl.Rows(irow).Cells(0).Value & "'"
]
            cmd = New SqlCommand(query, conn)
            cmd.ExecuteNonQuery()

---

As I have mentioned before this is not the way you should be going about this. There are a few different ways you could do it, unfortunately I am not that up on ADO.Net as of yet as I am still doing most of my DB work in ADO and VB6.

Still you could be able to bind your grid and use an update method to update the data.
You could have the data stored in a datatable and loop through that rather than reading the whole table again.
You could do your updates 1 at a time as changes are made, rather than looping through the table you could use a query to see if the record is there and if then either update or insert
You could use the batch update method then create the update or insert as records are added/changed then use the updatebatch method when the save button is clicked.

Bottom line is that there are a bunch of ways to do what you are trying to do but looping through all the records and comparing them to all the records in the table is not the way you should choose as that is the slowest method available.

@Dr223 ..

One quick question .. Do you have a Unique ID column in the table? If so what is it's name??

Prac_no - is a unique ID (PK)

Right then .. Going back to code on OP..

Code:

---

      strFlag = "Ignore"
        For irow = 0 To DgvPracExcl.Rows.Count - 1

            'Read the practice number and status from TblPracExclude
            Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = '" & _
                              DgvPracExcl.Rows(irow).Cells(0).Value), conn)
            Try
                Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
                    While Autoreader.Read()

                        prac_no = Autoreader.GetValue(0)
                        prac_enabled = Autoreader.GetValue(1)

                    End While
                End Using
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
            End Try


          ' If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
                If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
                  'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                    'Exit For
                Else
                    MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                    strFlag = "Update"
                  Call UpdatePracEnabled()

                    'Exit For
                End If
          ' End If

        Next irow

        If strFlag = "Insert" Then
            Call PracticeInsert()
      ElseIf strFlag = "Update" Then
        End If

    End Sub

---

Tried amending the code as ;

Code:

---

strFlag = "Ignore"
        For irow = 0 To DgvPracExcl.Rows.Count - 1

            'Read the practice number and status from TblPracExclude
            Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = " & _
                              DgvPracExcl.Rows(irow).Cells(0).Value), conn)
            Try
                Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
                    While Autoreader.Read()

                        prac_no = Autoreader.GetValue(0)
                        prac_enabled = Autoreader.GetValue(1)

                    End While
                End Using
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
            End Try


          ' If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
                If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
                    'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                    'Exit For
                Else
                    MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                    strFlag = "Update"
                    Call UpdatePracEnabled()

                    'Exit For
                End If
          ' End If

        Next irow

        If strFlag = "Insert" Then
            Call PracticeInsert()
        ElseIf strFlag = "Update" Then
        End If

    End Sub

---

TESt

-For the first 3 records I changed the prac_enabled flag from True to False and click Save

Result:

Message - Practice need Update

In the database ONLY the first record was changed from True to False the other two remained at TRUE. Why?

Many thanks

The logic of the code is still off an insert will not take place should it be a new record.

Personally I think I would set up some kind of list that would hold the row numbers that had been changed since the data was last saved then go through that list and update the records which were changed. It is kinda silly to loop through all the items in a grid and do a select for every row. What if the user changed only 1 record but it was the last row and there were 10,000 rows? You would be doing 9,999 selects that do not need to be done before you get to the right one. Where if you had just stored the row number when the user changed it you could do the same task with a single select statement and it would be much much faster.

Quote:

---

Originally Posted by DataMiser

Personally I think I would set up some kind of list that would hold the row numbers that had been changed since the data was last saved then go through that list and update the records which were changed. It is kinda silly to loop through all the items in a grid and do a select for every row. What if the user changed only 1 record but it was the last row and there were 10,000 rows? You would be doing 9,999 selects that do not need to be done before you get to the right one. Where if you had just stored the row number when the user changed it you could do the same task with a single select statement and it would be much much faster.

---

I was getting there.. :D ..

what we going to look at now .. is the DGV's Row validating event....

Code:

---

        Private Sub DgvPracExcl_RowValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DgvPracExcl.RowValidating

        End Sub

---

Just note that this Event Fires the moment they move off the DGV cell that was changed..

Now we can tighten up the code to this..

Code:

---

        Private Sub DgvPracExcl_RowValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DgvPracExcl.RowValidating

            Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = '" & _
                              DgvPracExcl.Rows(e.RowIndex).Cells(0).Value), conn)
            Try
                Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
                    While Autoreader.Read()

                        prac_no = Autoreader.GetValue(0)
                        prac_enabled = Autoreader.GetValue(1)

                    End While
                End Using
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
            End Try

                If prac_enabled = DgvPracExcl.Rows(e.RowIndex).Cells(3).Value Then
                    'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                Else
                    MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                    Call UpdatePracEnabled()
                End If
        End Sub

---

Then My next Question is what is in the Sub "UpdatePracEnabled"...

In the above code shouldnt it also match the prac_no = DgvPracExcl.Rows(irow).Cells(0).Value to make sure it is comparing the right prac_no and prac_enabled?


UpdatePracEnabled is the functon that performs the update as shown below;

Code:

---


 Private Sub UpdatePracEnabled()

        Dim conn As SqlConnection = GetDbConnection()
        Dim query As String
        Dim cmd As New SqlCommand

        Dim irow As Integer

        Try
            query = "UPDATE dbo.TblPracExclude  SET prac_enabled  ='" & _
                      DgvPracExcl.Rows(irow).Cells(3).Value & "' where Prac_No='" & _
                      DgvPracExcl.Rows(irow).Cells(0).Value & "'"
            cmd = New SqlCommand(query, conn)
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
        End Try

    End Sub

---

Many thanks

Ahhh ... Now this explains it ALL ... Give the FULL picture and it becomes easier to fix...

this function needs to be called for EACH and EVERY Update, With the Correct parameter for each one too....

So modify the function as such..

Code:

---

Private Sub UpdatePracEnabled(Byval RowID as integer)

        Dim conn As SqlConnection = GetDbConnection()
        Dim query As String
        Dim cmd As New SqlCommand

        Dim irow As Integer

        Try
            query = "UPDATE dbo.TblPracExclude  SET prac_enabled  ='" & _
                      DgvPracExcl.Rows(RowID).Cells(3).Value & "' where Prac_No='" & _
                      DgvPracExcl.Rows(RowID).Cells(0).Value & "'"
            cmd = New SqlCommand(query, conn)
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
        End Try

    End Sub

---

and the last code i gave you modify as such

Code:

---

        Private Sub DgvPracExcl_RowValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DgvPracExcl.RowValidating

            Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = '" & _
                              DgvPracExcl.Rows(e.RowIndex).Cells(0).Value), conn)
            Try
                Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
                    While Autoreader.Read()

                        prac_no = Autoreader.GetValue(0)
                        prac_enabled = Autoreader.GetValue(1)

                    End While
                End Using
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
            End Try

                If prac_enabled = DgvPracExcl.Rows(e.RowIndex).Cells(3).Value Then
                    'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                Else
                    MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
                    Call UpdatePracEnabled(prac_no)
                End If
        End Sub

---

And it all should now work...

Ok - how should I call the function - DgvPracExcl_RowValidating as

Call DgvPracExcl_RowValidating ()

Error: Argument not specified for parameter 'e' of private sub DgvPracExcl_RowValidating....

Thanks

Quote:

---

Originally Posted by dr223

Ok - how should I call the function - DgvPracExcl_RowValidating as

Call DgvPracExcl_RowValidating ()

Error: Argument not specified for parameter 'e' of private sub DgvPracExcl_RowValidating....

Thanks

---

You don't call it....

Quote:

---

Originally Posted by GremlinSA

Just note that this Event Fires the moment they move off the DGV cell that was changed..

---

It's a DGV Event that is raised for a specified trigger...

Ok - this is what I have the functions

Code:

---

Private Sub DgvPracExcl_RowValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DgvPracExcl.RowValidating

        Dim conn As SqlConnection = GetDbConnection()
        Dim cmd As New SqlCommand
        Dim prac_no As Integer
        Dim prac_enabled As String = Nothing
        'Dim irow As Integer

        Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = " & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(0).Value), conn)
        Try
            Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
                While Autoreader.Read()

                    prac_no = Autoreader.GetValue(0)
                    prac_enabled = Autoreader.GetValue(1)

                End While
            End Using
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
        End Try

        If prac_enabled = DgvPracExcl.Rows(e.RowIndex).Cells(3).Value Then
            'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
        Else
            MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
            Call UpdatePracEnabled(prac_no)
        End If
    End Sub

---

Code:

---

Private Sub UpdatePracEnabled(ByVal RowID As Integer)

        Dim conn As SqlConnection = GetDbConnection()
        Dim query As String
        Dim cmd As New SqlCommand

        'Dim irow As Integer

        Try
            query = "UPDATE dbo.TblPracExclude  SET prac_enabled  ='" & _
                      DgvPracExcl.Rows(RowID).Cells(3).Value & "' where Prac_No='" & _
                      DgvPracExcl.Rows(RowID).Cells(0).Value & "'"
            cmd = New SqlCommand(query, conn)
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
        End Try

    End Sub

---

At runtime and when I change the value of prac_enabled from TRUE to FALSE and click another place I receive the message - Practice need update - I click OK ..

Tried it few times, and whenever I do the checkbox uncheck (i.e., change from TRUE to FALSE ) received the message.. and clicked OK..

When I check the database no chnage has been made.. Why?

Many thanks

Use Debug (F9 & F8) to check the functions....

Are you sure that no changes are been made...

I'm on the road for the next hour and so..

Hi,

I am getting the update now I updated code to;

[code]

For RowID = 0 To DgvPracExcl.Rows.Count - 1

Try
query = "UPDATE dbo.TblPracExclude SET prac_enabled ='" & _
DgvPracExcl.Rows(RowID).Cells(3).Value & "' where Prac_No='" & _
DgvPracExcl.Rows(RowID).Cells(0).Value & "'"
cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()

Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try

Next
[code]


Two issues,

1) when I check/uncheck the checkbox its not INSTANT it takes few seconds to take effect on the datagridview - the first check is quick but the second onwards are very slow. IS to do with the code I inserted.

2) Secondly, I have added highlighted code;

Code:

---

Dim RecordsAffected As Integer = 0

        Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = " & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(0).Value), conn)
        Try
            Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
                While Autoreader.Read()

                    prac_no = Autoreader.GetValue(0)
                    prac_enabled = Autoreader.GetValue(1)

                End While
            End Using
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
        End Try

        If prac_enabled = DgvPracExcl.Rows(e.RowIndex).Cells(3).Value Then
            'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
        Else
            'MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
            Call UpdatePracEnabled(prac_no)
            RecordsAffected = RecordsAffected + 1

            If RecordsAffected > 0 Then
                MsgBox(RecordsAffected & " Records inserted or updated")
            Else
                MsgBox("No Records need updating")
            End If

         
        End If

---

I receive after every update that 1 Records inserted or updated.

I want to do at the end of the update..

For example, I have done 3 changes - I receive the message at the end of all the update with 3 Records inserted or updated.

Thank you

Quote:

---

Originally Posted by dr223

Hi,
Two issues,

1) when I check/uncheck the checkbox its not INSTANT it takes few seconds to take effect on the datagridview - the first check is quick but the second onwards are very slow. IS to do with the code I inserted.

---

Code:

---

For RowID = 0 To DgvPracExcl.Rows.Count - 1

---

Your now Updating ALL THE RECORDS in the Database every time...

Quote:

---

Originally Posted by dr223

2) Secondly, I have added highlighted code;

Code:

---


Dim RecordsAffected As Integer = 0

....
        If prac_enabled = DgvPracExcl.Rows(e.RowIndex).Cells(3).Value Then
          .....
        Else
            ....
            Call UpdatePracEnabled(prac_no)
            RecordsAffected = RecordsAffected + 1

          If RecordsAffected > 0 Then
                MsgBox(RecordsAffected & " Records inserted or updated")
            Else
                MsgBox("No Records need updating")
            End If
       
        End If

---

I receive after every update that 1 Records inserted or updated.

---

Look how i highlighted it ....

You set it to 0 (zero) ..
You add one , AND Immediately ...
You check it

1)

Code:

---

Your now Updating ALL THE RECORDS in the Database every time...

---

How can I prevent that and ONLY update the record/records updated.

2) True, but this will trigger the message whenever a record is updated e.g., record 1 is changed it gives the message 1 Record inserted or updated.
I want to update all the records and have a final message saying like 10 records inserted or updated.. This kind of logic..

Many thanks

Don't use EXIT FOR everywhere. It should only execute when you are done executing ALL records in the loop, or if you need to exit due to emergency. Not after the current one works! (Which is what you've described)

Ok - I changed the Update code to

Code:

---


Dim conn As SqlConnection = GetDbConnection()
        Dim query As String
        Dim cmd As New SqlCommand

            Try
                query = "UPDATE dbo.TblPracExclude SET prac_enabled  ='" & _
                          DgvPracExcl.Rows(RowID).Cells(3).Value & "' where Prac_No='" & _
                          DgvPracExcl.Rows(RowID).Cells(0).Value & "'"
                cmd = New SqlCommand(query, conn)
                cmd.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
            End Try

---

With the above code, it doesn't do any updates at ALL.

Then when I change it to;

Code:

---

Dim conn As SqlConnection = GetDbConnection()
        Dim query As String
        Dim cmd As New SqlCommand

        For RowID = 0 To DgvPracExcl.Rows.Count - 1

            Try
                query = "UPDATE dbo.TblPracExclude SET prac_enabled  ='" & _
                          DgvPracExcl.Rows(RowID).Cells(3).Value & "' where Prac_No='" & _
                          DgvPracExcl.Rows(RowID).Cells(0).Value & "'"
                cmd = New SqlCommand(query, conn)
                cmd.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
            End Try

        Next

---

Updates each record even if no change has taken place and this is why as you mentioned it takes time before the second change becomes visible and takes effect.

So how can I update the code so that it updates each record rather than updating the whole table.
This should make it quicker to reflect the change.

Thanks

Thanks

any help please

Quote:

---

Originally Posted by GremlinSA

Use Debug (F9 & F8) to check the functions....

---

Have you checked the values with debug ?????

Have you checked that your passing the function the right RowID ????

With out your full project and Database we can't do the Debugging .. we can only offer sample codes and methods....

Get in and do some of the dirty work, it's the only way you will learn!!!!!

Ok Did some re-arrange and its working know as I wanted..

But I want ONLY one last thing - after every record update I receive the message;

1 Records inserted or updated...

I want this message to be the sum of all the records updated and should be prompted at the end when an exit button is called..

Code:

---

Dim RecordsAffected As Integer = 0

        Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = " & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(0).Value), conn)
        Try
            Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
                While Autoreader.Read()

                    prac_no = Autoreader.GetValue(0)
                    prac_enabled = Autoreader.GetValue(1)

                End While
            End Using
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
        End Try

        If prac_enabled = DgvPracExcl.Rows(e.RowIndex).Cells(3).Value Then
            'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
        Else
           
          Try
                query = "UPDATE dbo.TblPracExclude SET prac_enabled  ='" & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(3).Value & "' where Prac_No=" & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(0).Value & ""
                cmd = New SqlCommand(query, conn)
                cmd.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
            End Try

            RecordsAffected = RecordsAffected + 1

        End If

        If RecordsAffected > 0 Then
            MsgBox(RecordsAffected & " Records inserted or updated")
        Else
            MsgBox("No Records need updating")
        End If



    End Sub

---

Can I create a function out of the Rowvalidating() and call it to do the count and prompt a message of the number of records updated?

Thanks

He was not passing the rowid, was passing prac_no instead which was causing the problem.
His question was cross posted on VBForums