Preventing Duplication

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • darrel
    New Member
    • Nov 2006
    • 72

    #1

    Preventing Duplication

    hi vb master good day! i just wanted to ask for some help here.. i'am trying to prevent duplcation of records that will be save in my table...

    here my code:
    Code:
    Private Sub cmdVerify_Click()
    Dim rsShowRec As New ADODB.Recordset
    Dim cnn As New ADODB.Connection
    
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = _
     "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
    cnn.Open
    For x = 0 To 3
    
    On Error Resume Next
    rsShowRec.Open "Select * from RECORDS , cnn, adOpenStatic, adLockOptimistic"
    
    If rsShowRec.EOF <> True Then
    If Combo1(x).Text = rsShowRec.Fields("TIME START").Value And Combo4(x).Text = rsShowRec.Fields("TIME END").Value And Combo2(x).Text = rsShowRec.Fields("ROOM").Value And Combo3(x).Text = rsShowRec.Fields("DAYS").Value Then
        rsShowRec.Close
        cnn.Close
        cmdSave.Visible = False
    
    Else
        cmdSave.Visible = True
    End If
    End If
    Next
        MsgBox ("Records already existed"), vbCritical
    
    
    End Sub
    the scenario is this eventhough my table doesnt have any records on it, still my system goes popping a mesage that the records i want to verify or save is already existed, i know theres something with my code. I assme that i doesnt actually loking in my database like i want,, kidly take a look at my code and i hope somebody can figure out whats wrong with it!!! Thank you very much!
    Last edited by darrel; May 25 '07, 04:25 AM. Reason: Mispelling
  • Killer42
    Recognized Expert Expert
    • Oct 2006
    • 8429

    #2
    Originally posted by darrel
    rsShowRec.Open "Select * from RECORDS , cnn, adOpenStatic, adLockOptimisti c"
    You've got the quotes in the wrong place. You're including all the parameters (cnn, etc) in the SQL string.

    Comment

    • darrel
      New Member
      • Nov 2006
      • 72

      #3
      Thank you for entertaining mga thread, but i dont how to arrange it! can you give me ideas or hint on how my code will function as i want...

      Comment

      • Killer42
        Recognized Expert Expert
        • Oct 2006
        • 8429

        #4
        Originally posted by darrel
        Thank you for entertaining mga thread, but i dont how to arrange it! can you give me ideas or hint on how my code will function as i want...
        I haven't looked at the code in much detail as yet, but the line that I mentioned probably should be corrected like this...[CODE=vb]rsShowRec.Open "Select * from RECORDS" , cnn, adOpenStatic, adLockOptimisti c[/code]

        Comment

        • darrel
          New Member
          • Nov 2006
          • 72

          #5
          Thank very Mr. i will try to debug my program and keep you posted...thank you!

          Comment

          • darrel
            New Member
            • Nov 2006
            • 72

            #6
            Mr.Killer ive debug my code but still doesnt change, still it doesnt look for records in my fields:
            here's my code:

            Code:
            Dim rsShowRec As New ADODB.Recordset
            Dim cnn As New ADODB.Connection
            
            Set cnn = New ADODB.Connection
            cnn.ConnectionString = _
             "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
            cnn.Open
            
            For x = 0 To 15
            On Error Resume Next
            rsShowRec.Open "Select * from RECORDS", cnn, adOpenStatic, adLockOptimistic, adText
            Next
                 
                If Combo1(0).Text = rsShowRec.Fields("TIME START") And Combo2(0).Text = rsShowRec.Fields("ROOM") And Combo3(0).Text = rsShowRec.Fields("DAYS") And Combo4(0).Text = rsShowRec.Fields("TIME END") Then
                    MsgBox ("Record already existed"), vbCritical
                    rsShowRec.Close
                    cnn.Close
                    Exit Sub
            
            
                Else
                    MsgBox ("There's no conflict with the previous records, you may save it now!"), vbOKOnly
                    cmdSave.Enabled = True
                End If
            i think there's something with this code of mine:
            Code:
            If Combo1(0).Text = rsShowRec.Fields("TIME START") And Combo2(0).Text = rsShowRec.Fields("ROOM") And Combo3(0).Text = rsShowRec.Fields("DAYS") And Combo4(0).Text = rsShowRec.Fields("TIME END") Then
                    MsgBox ("Record already existed"), vbCritical
                    rsShowRec.Close
                    cnn.Close
                    Exit Sub
            I think it doesnt do anything at all, what my code must do is to test all the records in my fields and see if there's going to be a duplication with the data that will be save later on... Mr.Killer i hope you can figure out what ive been doing wrong or missing, am getting frustrated,,, i cant go forward to the other tasks of my system without finishing this test of records.. I hope you can help me.. Thank you very much and God bless you all for helping us newbies in vb programming, hail to you all!!

            Comment

            • Killer42
              Recognized Expert Expert
              • Oct 2006
              • 8429

              #7
              I don't understand the purpose of the For loop around the Open. You are trying to open rsShowRec 15 times? Why?

              I think what you need to do is either open rsShowRec as now (but only once) then use MoveNext in a loop to go through the records and check them, or use a WHERE clause in your SQL to find any record matching your values.

              Comment

              • darrel
                New Member
                • Nov 2006
                • 72

                #8
                thank you for that information: can u teach how to use WHERE clause in my code??? Thank you very much.

                i have modified again my code, but still nothing happen... it always prompt me that the records i have inputted inputted though in my records table it doesnt match anything i know theres somthing wrong with my comparison method, and i'am not sure with the connection of my database, is it the right way to code it,,

                here my code:

                Code:
                Private Sub cmdVerify_Click()
                Dim rsShowRec As New ADODB.Recordset
                Dim cnn As New ADODB.Connection
                
                Set cnn = New ADODB.Connection
                cnn.ConnectionString = _
                 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
                cnn.Open
                
                For y = 0 To 15
                If Combo1(y).Text = "Select" Or Combo2(y).Text = "Select" Or Combo3(y).Text = "Select" Or Combo4(y).Text = "Select" Then
                        On Error Resume Next
                        MsgBox ("Cannot Save blank entries"), vbCritical
                        On Error Resume Next
                        Exit Sub
                End If
                Next
                
                'AVOIDING DUPLICATION OF RECORDS
                        rsShowRec.Open "Select * from RECORDS", cnn, adOpenStatic, adLockOptimistic
                        
                    If rsShowRec.RecordCount > 0 Then
                        For x = 0 To 15
                            If rsShowRec.Fields("TIME START") = UCase(Combo1(x).Text) And rsShowRec.Fields("TIME END") = UCase(Combo4(x).Text) And rsShowRec.Fields("ROOM") = UCase(Combo2(x).Text) And rsShowRec.Fields("DAYS") = UCase(Combo3(x).Text) Then
                            rs.MoveNext
                            End If
                        
                        Next
                        
                        MsgBox ("Record already existed"), vbCritical
                        rsShowRec.Close
                        cnn.Close
                        End If
                     
                    
                End Sub
                thank you for patience mr. i hope we can slice whats wrong with this code... i just have to compare the data that i will be saving to the records that are been save in my RECORDS table... please...

                Comment

                • Killer42
                  Recognized Expert Expert
                  • Oct 2006
                  • 8429

                  #9
                  Ok, this is still going to require some work on your part. But the basic syntax to include a WHERE clause in your SQL string would be something like this...

                  [CODE=vb]Dim strSQL As String
                  strSQL = "Select * from RECORDS" _
                  & " WHERE [TIME START] = '" & Ucase(Combo1(x) .Text) & "'" _
                  & " AND [TIME END] = '" & UCase(Combo4(x) .Text) & "'" _
                  & " AND ROOM = '" & UCase(Combo2(x) .Text) & "'" _
                  & " AND DAYS = '" & UCase(Combo3(x) .Text) & "'"[/CODE]

                  Comment

                  • darrel
                    New Member
                    • Nov 2006
                    • 72

                    #10
                    Thank you very much... i will try to work it out now... ill keep you posted with the development of my program.

                    Comment

                    • darrel
                      New Member
                      • Nov 2006
                      • 72

                      #11
                      Hello, i have a news.. it still doesnt work,, there no error with the conection of the database and to the code it self but it doesnt meet up my needs... now the problem is every time i will verify the schedule that has been made, the program always prompt me the that theirs no error on the schedule though it has errors... i think there something wrong my with code concerning the testing on the records in my table and the schedule that is being created by the user...

                      heres my code now

                      Code:
                      Private Sub cmdVerify_Click()
                      Dim rs As New ADODB.Recordset
                      Dim cnn As New ADODB.Connection
                      Dim x As Integer
                      Dim strSQL As String
                      
                      Set cnn = New ADODB.Connection
                      cnn.ConnectionString = _
                       "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
                      cnn.Open
                      
                      For x = 0 To 15
                      On Error Resume Next
                      strSQL = "Select * from RECORDS" _
                      & "WHERE [TimeStart] = ' " & UCase(Combo1(x).Text) & " ' " _
                      & " AND [TimeEnd] = ' " & UCase(Combo4(x).Text) & " ' " _
                      & " AND [ROOM] = ' " & UCase(Combo2(x).Text) & " ' " _
                      & " AND [DAYS] = ' " & UCase(Combo3(x).Text) & " ' "
                      
                      
                      If Combo1(x).Text = "Select" Or Combo2(x).Text = "Select" Or Combo3(x).Text = "Select" Or Combo4(x).Text = "Select" Then
                              MsgBox ("Cannot Save blank entries"), vbCritical
                              rsShowRec.Close
                              cnn.Close
                              Exit Sub
                      End If
                      Next x
                             
                             If rs.EOF <> True Then
                             Else
                          For z = 0 To 15
                             If UCase(Combo1(z).Text) = rs.Fields("TimeStart") And UCase(Combo4(z).Text) = rs.Fields("TimeEnd") And UCase(Combo2(z).Text) = rs.Fields("ROOM") And UCase(Combo3(z).Text) = rs.Fields("DAYS") Then
                         
                              rs.Close
                              cnn.Close
                             End If
                          Next z
                              MsgBox ("Theres error on the schedule!"), vbCritical
                          End If
                          
                          For y = 0 To 15
                             If Not UCase(Combo1(y).Text) = rs.Fields("TimeStart") And UCase(Combo4(y).Text) = rs.Fields("TimeEnd") And UCase(Combo2(y).Text) = rs.Fields("ROOM") And UCase(Combo3(y).Text) = rs.Fields("DAYS") Then
                           End If
                          Next y
                              MsgBox ("Theres no error on the schedule!"), vbOKOnly
                              cmdSave.Enabled = True
                      End Sub
                      I think theres something wrong in this area

                      Code:
                      If rs.EOF <> True Then
                             Else
                          For z = 0 To 15
                             If UCase(Combo1(z).Text) = rs.Fields("TimeStart") And UCase(Combo4(z).Text) = rs.Fields("TimeEnd") And UCase(Combo2(z).Text) = rs.Fields("ROOM") And UCase(Combo3(z).Text) = rs.Fields("DAYS") Then
                         
                              rs.Close
                              cnn.Close
                             End If
                      i think this code doesnt do anything at all... I hope you can tell me whats wrong with this,,, I pressume that it doesnt actually looking in my fields.Thank you so much for patience... Hoping to hear from you soon...

                      Comment

                      • Killer42
                        Recognized Expert Expert
                        • Oct 2006
                        • 8429

                        #12
                        I don't have time right now to go over this in detail. But I have a couple of observations...
                        • You probably don't actually know whether there are any errors, because once you execute On Error Resume Next any error will simply cause you to continue with the next statement.
                        • Your indentation is a bit messed up, which makes it much harder to follow the logic.
                        • Why do you have an If test which does nothing...
                          If rs.EOF <> True Then
                          Else
                        • I think your logic is needlessly complex. It probably should be following a fairly simple path something like...
                          Code:
                          For x = 0 to 15
                            If anything not filled in Then
                              Cannot Save blank entries
                              Exit For
                            End If
                            Build SQL string to find any match for values(x)
                            Execute query
                            If anything found (check RecordCount, I suppose) Then
                              Theres error on the schedule!
                              Exit For
                            End If
                          Next

                        Comment

                        • darrel
                          New Member
                          • Nov 2006
                          • 72

                          #13
                          thank you for the advise, one more question i hope its ok! after i build a SQL string, how can i execute it, am having a hard to understand what you have posted "Execute query"..?? thank you

                          Comment

                          • Killer42
                            Recognized Expert Expert
                            • Oct 2006
                            • 8429

                            #14
                            Originally posted by darrel
                            thank you for the advise, one more question i hope its ok! after i build a SQL string, how can i execute it, am having a hard to understand what you have posted "Execute query"..?? thank you
                            That's the Open that you were using before. It opens, or executes, the query.

                            Comment

                            • darrel
                              New Member
                              • Nov 2006
                              • 72

                              #15
                              Thank you for that information Mr.Killer, but sad to say still my program doesnt work as i want, Mr. can you give me some example on how to compare database records or on how to prevent records using sql statement in visual just like wat i have been doing,even a simple example or code, for able for me to understand the concept and to understand the codes and scenarion needed.. thank you very very much... i hope you can site me some example... am really getting frustrated ive been working it out for about a week now, but still no changes...


                              Hope you can help me with this... Thank you!

                              Comment

                              Working...