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

Thread: IF Statement

  1. #1
    Join Date
    Feb 2009
    Posts
    192

    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..

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

    Re: IF Statement

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

  3. #3
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    any hints ?

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

    Re: IF Statement

    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...
    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. #5
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    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

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

    Re: IF Statement

    Quote Originally Posted by dr223 View Post
    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
    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. #7
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: IF Statement

    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.
    Last edited by DataMiser; June 8th, 2012 at 08:48 AM.
    Always use [code][/code] tags when posting code.

  8. #8
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    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
    Last edited by dr223; June 8th, 2012 at 09:24 AM.

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

    Re: IF Statement

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

  10. #10
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    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()
    Last edited by dr223; June 8th, 2012 at 10:41 AM.

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

    Re: IF Statement

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

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

    Re: IF Statement

    @Dr223 ..

    One quick question .. Do you have a Unique ID column in the table? If so what is it's name??
    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.

  13. #13
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    Prac_no - is a unique ID (PK)

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

    Re: IF Statement

    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
    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.

  15. #15
    Join Date
    Feb 2009
    Posts
    192

    Re: IF Statement

    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

Page 1 of 3 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