CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 32

Thread: IF Statement

  1. #16
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: IF Statement

    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.
    Always use [code][/code] tags when posting code.

  2. #17
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: IF Statement

    Quote Originally Posted by DataMiser View Post
    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.. ..

    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"...
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  3. #18
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    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
    Last edited by dr223; June 12th, 2012 at 06:16 AM.

  4. #19
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: IF Statement

    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...
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  5. #20
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    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
    Last edited by dr223; June 12th, 2012 at 06:56 AM.

  6. #21
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: IF Statement

    Quote Originally Posted by dr223 View Post
    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 View Post
    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...
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  7. #22
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    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

  8. #23
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: IF Statement

    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..
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  9. #24
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    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

  10. #25
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: IF Statement

    Quote Originally Posted by dr223 View Post
    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 View Post
    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
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  11. #26
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    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

  12. #27
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: IF Statement

    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)
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  13. #28
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    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
    Last edited by dr223; June 14th, 2012 at 06:45 AM.

  14. #29
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    any help please

  15. #30
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: IF Statement

    Quote Originally Posted by GremlinSA View Post
    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!!!!!
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

Page 2 of 3 FirstFirst 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured