[RESOLVED] RecordCount return -1
Hi all. Please take a look at code below. Just simple select the table but it return -1. What have to be fixed ? Please help...
### GLOBAL VARIABLE ###
Code:
Private db As ADODB.Connection
Const DBNAME = "\DSDW2.mdb;"
### Sync Down BUTTON ###
Code:
Private Sub cmdSyncdown_Click()
Dim ConnectionString As String
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & DBNAME
Set db = New ADODB.Connection
db.Open (ConnectionString)
db.BeginTrans
'CALL METHOD HERE THEN SET Success Flag
Dim tmpQueryStr As String
tmpQueryStr = "VALID SQL SELECT STATEMENT HERE"
Dim result As Integer
result = conn.Execute(tmpQueryStr).RecordCount '#### THIS LINE ... result = -1 ####
If result > 0 Then
tmpQueryStr = "MSACCESS TESTED SQL UPDATE STATEMENT HERE"
ExecuteDB(tmpQueryStr)
Else
tmpQueryStr = "MSACCESS TESTED SQL INSERT STATEMENT HERE"
ExecuteDB(tmpQueryStr)
End If
If Success Then
db.CommitTrans
Else
db.RollbackTrans
End If
db.Close
End Sub
### FUNCTION ###
Code:
Public Function ExecuteDB(ByVal QueryStr As String, ByRef db As ADODB.Connection) As ADODB.Recordset
On Error GoTo ErrorHandling:
Dim rs As New ADODB.Recordset
Set rs = db.Execute(QueryStr)
Set ExecuteDB = rs
Exit Function
ErrorHandling:
MsgBox QueryStr
Success = False
Set ExecuteDB = Nothing
End Function
### END OF CODES ###
Re: RecordCount return -1
it means that you have a server side cursor.... there are records, but ADO cannot determine the exact number of records. If all you care about is if there are records, then as long as it is not 0, there are records.
-tg
Re: RecordCount return -1
Quote:
Originally Posted by
techgnome
it means that you have a server side cursor.... there are records, but ADO cannot determine the exact number of records. If all you care about is if there are records, then as long as it is not 0, there are records.
-tg
Thank you for your reply.
Would you mind to tell me which part of code do I have to chage ?
Re: RecordCount return -1
Welcome to VBForums :wave:
Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)
In addition to what tg posted, if you use a Recordset object there is no reason for you to use .RecordCount at all - if you just want to check whether there are any records it is quicker (and uses less resources) to just check .EOF and .BOF, eg:
Code:
rs.Open "SELECT ...
If Not(rs.EOF And rs.BOF) Then
'there are records
Else
'no records
End If
I find it rather odd that you are running a Select statement but then ignore the actual data you get from it... it would almost certainly be more efficient (and cause fewer locking issues etc) to alter the Select statement slightly, and use a recordset.
Quote:
Originally Posted by ClOuD_Za
Would you mind to tell me which part of code do I have to chage ?
This part:
Re: RecordCount return -1
I changed some part of code into below
vb Code:
Dim result As New ADODB.Recordset
result.ActiveConnection = db
result.Open (tmpQueryStr)
If Not (result.EOF And rs.BOF) Then
tmpQueryStr = "MSACCESS TESTED SQL UPDATE STATEMENT HERE"
ExecuteDB(tmpQueryStr)
Else
tmpQueryStr = "MSACCESS TESTED SQL INSERT STATEMENT HERE"
ExecuteDB(tmpQueryStr)
End If
now ERROR : Object variable or With block variable not set
Re: RecordCount return -1
on what line? The only lines that aren't suspect for that error is 4, 8 & 11.... so which of the other 8 is the problem?
-tg
Re: RecordCount return -1
OOPs.
So Sorry TG. My mistake.
At line 5 , I forgot to change "rs" to "result"
I re-test again and came up with new error.
"The database engine could not lock table because it is already in use by another person or process" on LINE 10
Re: RecordCount return -1
Take a CLOSE look at this line and think about what you told me about changing RS to RESULT.... then look at the line part by part...
If Not (result.EOF And rs.BOF) Then
To help with the other problem of the lock, I'd need to see ExecuteDB .... I suspect it is opening it's own connection to the database, but you have one open already... you should open it once, and use that same connection as needed.
-tg
Re: RecordCount return -1
vb Code:
Dim rs As New ADODB.Recordset
Set rs = db.Execute(QueryStr)
db is opened and I thought rs is just receive recordset result from db.execute ...
I can't see any open new connection in the ExecuteDB Method.
Re: RecordCount return -1
Maybe this would help:
Code:
...
Dim tempRs as ADODB.Recordset
Set tempRs = conn.Execute(tmpQueryStr)
If Not (tempRs.EOF And tempRs.BOF) Then
tmpQueryStr = "MSACCESS TESTED SQL UPDATE STATEMENT HERE"
ExecuteDB(tmpQueryStr)
Else
tmpQueryStr = "MSACCESS TESTED SQL INSERT STATEMENT HERE"
ExecuteDB(tmpQueryStr)
End If
...
Re: RecordCount return -1
Quote:
Originally Posted by
ClOuD_Za
vb Code:
Dim rs As New ADODB.Recordset
Set rs = db.Execute(QueryStr)
db is opened and I thought rs is just receive recordset result from db.execute ...
I can't see any open new connection in the ExecuteDB Method.
On a side note, we should avoid New keyword when declaring objects in VB (VB6 and lower). This unnecessarily increases the overhead on the program as it checks for a valid instance of the variable on each use.
Instead we should do like this:
Code:
Dim rs As ADODB.Recordset
Set rs = db.Execute(QueryStr)
'or
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open QueryStr ...
Re: RecordCount return -1
Thank you , I dint know that.
Let me edit the code to be closer to real one that having problem. PLEASE USE BELOW CODE TO DISCUSS LATER
vb Code:
Dim result As ADODB.Recordset
Set result = db.Execute(tmpQueryStr)
If Not (result.EOF And result.BOF) Then
tmpQueryStr = "MSACCESS TESTED SQL UPDATE STATEMENT HERE"
ExecuteDB(tmpQueryStr)
Else
tmpQueryStr = "MSACCESS TESTED SQL INSERT STATEMENT HERE"
IncrementStr = "ALTER TABLE dbo_" & TableName & " ALTER COLUMN ID COUNTER(" & ID_No & ",1)"
ExecuteDB(IncrementStr)
ExecuteDB(tmpQueryStr)
End If
The error "The database engine could not lock table because it is already in use by another person or process" is actually occured at the line "ExecuteDB(IncrementStr)". ( LINE 11 )
The reason why I have to ALTER is I need to INSERT data ( which ID was set ) into the table which its ID is AUTO-INCREMENT.
Re: RecordCount return -1
Try this:
(not tested code)
Code:
Private Sub cmdSyncdown_Click()
Dim ConnectionString As String
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & DBNAME
Set Db = New Adodb.Connection
Db.Open (ConnectionString)
Db.BeginTrans
'CALL METHOD HERE THEN SET Success Flag
Dim tmpQueryStr As String
tmpQueryStr = "VALID SQL SELECT STATEMENT HERE"
Dim result As Boolean, tempRs As Adodb.Recordset
Set tempRs = conn.Execute(tmpQueryStr)
result = Not (tempRs.EOF And tempRs.BOF)
tempRs.Close
If result Then
tmpQueryStr = "MSACCESS TESTED SQL UPDATE STATEMENT HERE"
ExecuteDB (tmpQueryStr)
Else
tmpQueryStr = "MSACCESS TESTED SQL INSERT STATEMENT HERE"
ExecuteDB (tmpQueryStr)
End If
If Success Then
Db.CommitTrans
Else
Db.RollbackTrans
End If
Db.Close
End Sub
But this still seems like too heavy on the database.
Re: RecordCount return -1
"Heavy on Database" is ok because this database is used only in a PC.
Have you looked at my latest post ?
Re: RecordCount return -1
Quote:
The reason why I have to ALTER is I need to INSERT data ( which ID was set ) into the table which its ID is AUTO-INCREMENT.
Woah! No you don't. that's the point of the AutoIncrement... you don't have to do anything with it, for it or anything... it'll increment on it's own.
-tg
Re: RecordCount return -1
Quote:
Originally Posted by
techgnome
Woah! No you don't. that's the point of the AutoIncrement... you don't have to do anything with it, for it or anything... it'll increment on it's own.
-tg
Let me explain it.
1. I have a hundred of records in table ( ASSUMING THAT LATEST ID = 100 )
2. I have to download records from server ( somewhere ) and INSERT INTO this database which ID start from 1.
How could I do "2." If not delete records after that alter table to have ID = next record's ID then insert ?
Re: RecordCount return -1
if you already have records 1- 100.... why would you want to re-insert record ID 1? if it's a new record, it should get 101... which it will automatically. Otherwise, shouldn't it be an update?
If your database isnt' the source of the IDs in the first place, then the field shouldn't be an auto increment, but should simply be a number, at which point, you can then isnert it w/o needing to alter the table.
-tg
Re: RecordCount return -1
Quote:
Originally Posted by
techgnome
if you already have records 1- 100.... why would you want to re-insert record ID 1? if it's a new record, it should get 101... which it will automatically. Otherwise, shouldn't it be an update?
If your database isnt' the source of the IDs in the first place, then the field shouldn't be an auto increment, but should simply be a number, at which point, you can then isnert it w/o needing to alter the table.
-tg
reasons...
1.this table has to be linked with other table at runtime.
2.1 We have a lot of PCs running this APP and we have only 1 Server
2.2 Every PCs have to Sync its stored data to Server
2.3 After Sync Up then Sync Down all data from Server ( which also came from other PC ) to its database
3. This database is designed by my customer. They don't allow me to change it. So Sad
Re: RecordCount return -1
Except the line of ALTER. Now Code is working great.
Is there a way to mark a thread as SOLVED and mark a post as SOLUTION ?
sry , I'm new to here
Re: [RESOLVED] RecordCount return -1
You can mark the thread as Resolved by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).
However, it doesn't seem to me as if this is fully resolved, as there are things which could almost certainly be done in a better way... While you have replied to tg's post, you haven't actually given a valid justification for the method you are using - especially the desire to alter the table at all during the process.