-
May 23rd, 2009, 03:30 AM
#1
Thread Starter
Fanatic Member
[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
-
May 23rd, 2009, 10:30 AM
#2
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.
-
May 23rd, 2009, 11:39 AM
#3
Thread Starter
Fanatic Member
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 11:49 AM.
-
May 23rd, 2009, 12:12 PM
#4
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 12:17 PM.
-
May 23rd, 2009, 12:29 PM
#5
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|