[RESOLVED] RecordCount return -1

[ClOuD_Za]

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

Private db As ADODB.Connection
Const DBNAME = "\DSDW2.mdb;"

### Sync Down BUTTON ###

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

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

[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

[ClOuD_Za]

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 ?

[si_the_geek]

Welcome to VBForums

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:

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.

Would you mind to tell me which part of code do I have to chage ?

This part:

If result > 0 Then

[ClOuD_Za]

I changed some part of code into below

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

[techgnome]

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

[ClOuD_Za]

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

[techgnome]

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

[ClOuD_Za]

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.

[Pradeep1210]

Maybe this would help:

...

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

...

[Pradeep1210]

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:

Dim rs As ADODB.Recordset
Set rs = db.Execute(QueryStr) 

'or
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open QueryStr ...

[ClOuD_Za]

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

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.

[Pradeep1210]

Try this:
(not tested 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.

[ClOuD_Za]

"Heavy on Database" is ok because this database is used only in a PC.

Have you looked at my latest post ?

[techgnome]

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

[ClOuD_Za]

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 ?

[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

[ClOuD_Za]

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

[ClOuD_Za]

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

[si_the_geek]

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.