[RESOLVED] SQL recordcount=-1 when there are 3 records to match the search criteria-VBForums
Results 1 to 5 of 5

Thread: [RESOLVED] SQL recordcount=-1 when there are 3 records to match the search criteria

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2007
    Location
    West Yorkshire, UK
    Posts
    786

    Resolved [RESOLVED] SQL recordcount=-1 when there are 3 records to match the search criteria

    Hi, I found code here to prevent a record being written to the database if it already exists. I modified it and it doesn't seem to work and I have no idea why!
    The user enters info about a page on a resource book. The primary key is Autonumber. The other fields can all be duplicated (the values are mainly picked from comboboxes). What I want to do is prevent duplication only if ALL the fields are the same. The code I have is here. When it runs, there are 3 records to match the criteria but it gives oRS.RecordCount of -1!!!! I would be grateful if someone could tell me where I'm going wrong.
    Thanks.
    Code:
    Private Function DuplicateEntryDetected() As Boolean
    Dim Conn As New ADODB.Connection
    Dim oRS As ADODB.Recordset  'Load the data
    Dim strSQL As String
    'When a record is added, we need to check it is not already there
        strSQL = "SELECT count(*) FROM Enquiry WHERE enqBook = '" & cboBook.Text
        strSQL = strSQL & "' AND enqPage = '" & txtPage.Text & "' AND enqLevel = '" & cboLevel1.Text
        strSQL = strSQL & "' AND enqGrammar = '" & cboGrammar.Text & "' AND enqLexis = '" & cboLexis.Text
        strSQL = strSQL & "' AND enqActivity = '" & cboActivity.Text & "' AND enqSkill = '" & cboSkill.Text
        strSQL = strSQL & "'"
    'Now set the connection for Book
        Set Conn = New Connection
        Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath & "Book Access.mdb;Persist Security Info=False"
        Conn.ConnectionTimeout = 600
        Conn.Open
    'here we set the connection to the recordset and update the database
        Set oRS = New ADODB.Recordset
        oRS.Open strSQL, Conn, adOpenForwardOnly, adLockOptimistic, adCmdText
        If oRS.RecordCount > 0 Then
            DuplicateEntryDetected = True
            Beep
        Else
            DuplicateEntryDetected = False
        End If
        oRS.Close               'Tidy up
        Conn.Close
        Set oRS = Nothing
        Set Conn = Nothing
    End Function

  2. #2
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: SQL recordcount=-1 when there are 3 records to match the search criteria

    A RecordCount of -1 means at least 1 record was returned by the query but ADO cannot determine exactly how many records will be returned. A -1 also means the provider, cursor location/cursor type combination does not support the RecordCount property.

    Either change the cursor type to adOpenKeySet (in this case) or change the code to

    If oRS.RecordCount <> 0 Then
    or
    If Not oRS.EOF Then

    Setting Recordset.CursorLocation = adUseClient will always return a valid RecordCount.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2007
    Location
    West Yorkshire, UK
    Posts
    786

    Re: SQL recordcount=-1 when there are 3 records to match the search criteria

    Hi Bruce, thank you for that, it does seem to return a valid record count. I left one copy and tried to duplicate it and it said there was one count so that works fine. The thing is now, I changed one field and tried to add that and it still said there was one record, even though there wasn't.
    This is the new code:
    Code:
    Private Function DuplicateEntryDetected() As Boolean
    Dim Conn As New ADODB.Connection
    Dim oRS As ADODB.Recordset  'Load the data
    Dim strSQL As String
    'When a record is added, we need to check it is not already there
        strSQL = "SELECT count(*) FROM Enquiry WHERE enqBook = '" & cboBook.Text
        strSQL = strSQL & "' AND enqPage = '" & txtPage.Text & "' AND enqLevel = '" & cboLevel1.Text
        strSQL = strSQL & "' AND enqGrammar = '" & cboGrammar.Text & "' AND enqLexis = '" & cboLexis.Text
        strSQL = strSQL & "' AND enqActivity = '" & cboActivity.Text & "' AND enqSkill = '" & cboSkill.Text
        strSQL = strSQL & "'"
    'Now set the connection for Book
        Set Conn = New Connection
        Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath & "Book Access.mdb;Persist Security Info=False"
        Conn.ConnectionTimeout = 600
        Conn.Open
    'here we set the connection to the recordset and update the database
        Set oRS = New ADODB.Recordset
        oRS.CursorLocation = adUseClient
        oRS.Open strSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText
        If oRS.RecordCount > 0 Then
            DuplicateEntryDetected = True
            Beep
        Else
            DuplicateEntryDetected = False
        End If
        oRS.Close               'Tidy up
        Conn.Close
        Set oRS = Nothing
        Set Conn = Nothing
    End Function
    and this is the SQL string (first and second attempts) the first IS duplicated and is refused correctly. The second is NOT duplicated but is still refused saying that the record count is 1.

    SELECT count(*) FROM Enquiry WHERE enqBook = 'Movers 1' AND enqPage = '4 test 1 listening part 1' AND enqLevel = 'Flyers' AND enqGrammar = 'Adjective' AND enqLexis = '' AND enqActivity = 'Game' AND enqSkill = ''

    SELECT count(*) FROM Enquiry WHERE enqBook = 'Movers 1' AND enqPage = '4 test 1 listening part 1' AND enqLevel = 'Flyers' AND enqGrammar = 'Adjective' AND enqLexis = '' AND enqActivity = 'Handout' AND enqSkill = ''

    Last edited by Espaņolita; May 23rd, 2009 at 12:49 PM.

  4. #4
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: SQL recordcount=-1 when there are 3 records to match the search criteria

    Using Select Count(*) From .... will always return 1 row/record in the recordset. You need to check the value of the recordset field to determine the actual count (ie how many database records matched the where clause).

    Debug.Print oRS.Fields(0).Value


    Now you could use a Having clause in your SQL statement. When you add a Having clause RecordCount will either be 0 or 1.

    So with a Having clause your original If statement will work properly and there is no reason to check the Recordset.Field value (because the addition of the Having clause determines if there were duplicates).

    SELECT count(*) FROM Enquiry WHERE ... HAVING Count(*) > 1


    Note: Using the EOF property is usually the recommended way to determine if the query returned any rows.
    Last edited by brucevde; May 23rd, 2009 at 01:17 PM.

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2007
    Location
    West Yorkshire, UK
    Posts
    786

    Re: SQL recordcount=-1 when there are 3 records to match the search criteria

    It's sorted.
    The EOF property didn't work.
    I wasn't sure about the HAVING clause in the SQL because it will only ever have ONE duplicate in the DB. Or does it mean that it returns a 1 for no matches, 2 for one match etc?
    I used the Ors.Fields(0).Value option. It works perfectly.
    Thank you very much

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.