SQL Query recordcount = -1 in VB, 32 in Query Analyzer

[pixelmeow]

I have SQL code in my VB6 project for select, insert, and truncate. For example:

strSQL = "SELECT CASE_IDENTIFIER" & vbCrLf _
           & "      ,DOCUMENT_TYPE" & vbCrLf _
           & "      ,DOCUMENT_NUMBER" & vbCrLf _
           & "      ,CASE_LINE_NUMBER" & vbCrLf _
           & "      ,DELETION_INDICATOR" & vbCrLf _
           & "      ,MASL" & vbCrLf _
           & "      ,MDE_CODE" & vbCrLf _
           & "      ,GENERIC_CODE" & vbCrLf _
           & "      ,CASE_LINE_ITEM_QUANTITY" & vbCrLf _
           & "      ,TOTAL_LINE_VALUE_AMOUNT" & vbCrLf _
           & "      ,CASE_LINE_ITEM_DESCRIPTION " & vbCrLf _
           & "  FROM LINE_IMP_VIEW " & vbCrLf _
           & " ORDER BY CASE_IDENTIFIER"

LINE_IMP_VIEW is a view over a table. You could use the same SQL statement above on the table itself. This statement works fine. The TRUNCATE TABLE and INSERT INTO statements work fine as well, "TRUNCATE TABLE LINE_IMP" and "INSERT INTO LINE_IMP (fields) VALUES (list)".

This is the SQL I'm having problems with:

strSQL = "SELECT CASE_IDENTIFIER" & vbCrLf _
       & "      ,DOCUMENT_NUMBER" & vbCrLf _
       & "      ,DOCUMENT_TYPE" & vbCrLf _
       & "      ,MILESTONE" & vbCrLf _
       & "      ,MILESTONE_DATE" & vbCrLf _
       & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
       & "      ,MILESTONE_REMARKS" & vbCrLf _
       & "  FROM MILESTONE_IMP_VIEW" & vbCrLf

strSQLWhere = " WHERE CASE_IDENTIFIER = '"
            
strSQLCIin = "   AND CASE_IDENTIFIER IN" & vbCrLf _
           & "    (SELECT CASE_IDENTIFIER" & vbCrLf _
           & "       FROM CASE_MASTER_TEMP)"
       
strSQLandDN = "   AND DOCUMENT_NUMBER = '"
       
strSQLDNin = "   AND DOCUMENT_NUMBER IN" & vbCrLf _
           & "    (SELECT DOCUMENT_NUMBER" & vbCrLf _
           & "       FROM CASE_MASTER_TEMP)" & vbCrLf

And later in the procedure:

strSQL = strSQL & "" _
       & strSQLWhere & .masterVars(j).CASEIDENTIFIER & "'" & vbCrLf _
       & strSQLCIin & vbCrLf _
       & strSQLandDN & .mileVars(j).DOCNUM & "'" & vbCrLf _
       & strSQLDNin

This gives the correct string:

SELECT CASE_IDENTIFIER
      ,DOCUMENT_NUMBER
      ,DOCUMENT_TYPE
      ,MILESTONE
      ,MILESTONE_DATE
      ,MILESTONE_DATE_TYPE
      ,MILESTONE_REMARKS
  FROM MILESTONE_IMP_VIEW
 WHERE CASE_IDENTIFIER = 'A2-B-OBM'
   AND CASE_IDENTIFIER IN
    (SELECT CASE_IDENTIFIER
       FROM CASE_MASTER_TEMP)
   AND DOCUMENT_NUMBER = '000'
   AND DOCUMENT_NUMBER IN
    (SELECT DOCUMENT_NUMBER
       FROM CASE_MASTER_TEMP)

I created the query above in SQL Analyzer. It gives me the proper results there, 32 records. This is MILESTONE_IMP_VIEW:

SELECT CASE_IDENTIFIER AS CASE_IDENTIFIER
      ,DOCUMENT_TYPE AS DOCUMENT_TYPE
      ,DOCUMENT_NUMBER AS DOCUMENT_NUMBER
      ,MILESTONE AS MILESTONE
      ,MILESTONE_DATE_TYPE AS MILESTONE_DATE_TYPE
      ,MILESTONE_DATE AS MILESTONE_DATE
      ,MILESTONE_REMARKS AS MILESTONE_REMARKS
  FROM CASE_MILESTONE_TEMP
 WHERE CASE_IDENTIFIER IN
 	(SELECT CASE_IDENTIFIER
	   FROM CASE_MASTER_TEMP
	  WHERE (CASE_MASTER_STATUS_CODE = 'N'
	     OR CASE_MASTER_STATUS_CODE = 'P')
	    AND IMPLEMENTING_AGENCY = 'B'
	    AND (DOCUMENT_TYPE = 'AMD'
    	     OR DOCUMENT_TYPE = 'LOA')
	    AND CASE_VERSION_STATUS_CODE = 'I')

When I use the query with the view in VB, the recordcount is coming back -1. Is this just too complicated for ADO to handle?

I'm using this:

Public cn As New ADODB.Connection
Public cmd As New ADODB.Command
Public rs As New ADODB.Recordset

cmd.CommandText = qry
cmd.CommandType = adCmdText
Set rs = cmd.Execute

Any help is GREATLY appreciated!
teresa

[pixelmeow]

I found the solution:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_20809772.html

All you have to do is scroll all the way to the bottom of that page to see the answers.

I changed this

cmd.CommandText = qry
cmd.CommandType = adCmdText
Set rs = cmd.Execute

to this:

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open qry, cn, adOpenKeyset, adLockOptimistic

That was all.
teresa