Re: VB6 - ADODB - Strange behavior between IDE & EXE

In the article you provided, it mentions that RecordCount may return -1 for unsupported CursorType. OK, taking that I may not get correct value for the RecordCount, I can test for EOF. However, this CursorType usage had been working for years, particularly with Access and Sybase ASE, on Windows 2000 and XP. I had been copying the same code from other working programs.

But what about "Source"?. Why it is changing? It appears that the rs object itself is now reflecting some other object than that I set in the given LostFocus event. Do you mean to say that if I test for EOF the problem would be solved?

ThanQ...

"Richard Mueller [MVP]" <rlmueller-nospam@xxxxxxxxxxxxxxxxxxxx> wrote in message news:uxl0LgSCJHA.2272@xxxxxxxxxxxxxxxxxxxxxxx

"SME" <smelchuri@xxxxxxxxxxx> wrote in message news:OGV9rUSCJHA.1628@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

I am getting different behavior between running my code in VB6 IDE and after creating an EXE.

In the LostFocus code given below, I have two messages being displayed, namely, Msg-1 and Msg-2.

When I run it in IDE, I get same messages for Msg-1 and Msg-2.

But when I create an EXE and run it, Msg-1 shows RecordCount = 1 and .Source as what was set and Msg-2 shows RecordCount = 198 and .Source = SELECT ... FROM Client. Yes I do have table named Client with 198 rows in my database and I do use this SELECT ... FROM Client in a GotFocus event after finishing with the given LostFocus event But how can this happen?

The database I am using is Sybase ASE 15.0.2

I open the ADODB connection in the Form_Load as follows:

Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset

'make connection with DBMS
With oConn
.ConnectionString = "DSN=ASE15_MY_DB;UID=sa"
.CursorLocation = adUseClient
.Mode = adModeReadWrite
.Open

If .State = adStateOpen Then
.Execute "USE MY_DB", , adCmdText
End If
End With

Then in a LostFocus event, I have the following code:

With rs
If .State = adStateOpen Then
.Close
End If

.ActiveConnection = oConn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Source = "SELECT ... FROM Project WHERE ProjectCode = '" + txtProjectCode.Text + "'"
.Open

If .State = adStateOpen And .RecordCount > 0 Then
MsgBox "Msg-1: The RecodCount = " + Format$(.RecordCount, "#") + " .Source = " + .Source
iAns = MsgBox("The entered project code already exists." + vbNewLine + vbNewLine + "Do you want to add a NEW INSTANCE of the same project?", vbYesNo + vbQuestion, "Entering Project Code")
If iAns = vbYes Then
MsgBox "Msg-2: The RecodCount = " + Format$(.RecordCount, "#") + " .Source = " + .Source

--- some more code ---

EndIf 'end of iAns = vbYes
EndIf 'end of .State = adStateOpen And .RecordCount > 0

End With



Your cursorType does not support RecordCount. See this link:

http://support.microsoft.com/kb/194973

If you need to know if there are any records in the recordset, use the EOF property of the recordset. If you need the number of records, it might be more reliable to use the COUNT sql function. Otherwise, use a cursorType that supports RecordCount.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--


.

Relevant Pages

  • Re: rs.PageCount cannot function
    ... Before open, using stored procedure executed via dynamic sql, cursortype = 3 ... Before open, using stored procedure executed via Command object, cursortype ... So, even though it's giving us the "right" cursortype, the recordcount is ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • Re: Embedded SQL within VBA?
    ... If you can't MoveLast, then the provider you are using probably defaults to using an "adOpenForwardOnly" cursortype (you can use the CursorType parameter of the Open method to try to specify the CursorType, but the provider may change the type if it doesn't support the one you asked for. ... If you have an adOpenForwardONly cursor type, try changing it to any of the others - if you need to be able to update the recordset, use adOpenDynamic or adOpenKeyset, otherwise you may be able to use adOpenStatic. ... figure out why .RecordCount is returning -1 here? ...
    (microsoft.public.word.vba.general)
  • Re: RecordCount error
    ... Set cursorType to adOpenStatic and see if that helps. ... RecordCount returns -1 ... rsRecCount = rsPatient.RecordCount ...
    (microsoft.public.vb.database.ado)
  • Re: RecordCount error
    ... Set cursorType to adOpenStatic and see if that helps. ... RecordCount returns -1 ... Microsoft MVP Scripting and ADSI ... rsRecCount = rsPatient.RecordCount ...
    (microsoft.public.vb.database.ado)