943,722 Members | Top Members by Rank

Software Development > Visual Basic 4 / 5 / 6 > SQL Query recordcount = -1 in VB, 32 in Query Analyzer
Ad:
Permalink
Oct 29th, 2008
0

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

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

VB Syntax (Toggle Plain Text)
  1.     strSQL = "SELECT CASE_IDENTIFIER" & vbCrLf _
  2.            & "      ,DOCUMENT_TYPE" & vbCrLf _
  3.            & "      ,DOCUMENT_NUMBER" & vbCrLf _
  4.            & "      ,CASE_LINE_NUMBER" & vbCrLf _
  5.            & "      ,DELETION_INDICATOR" & vbCrLf _
  6.            & "      ,MASL" & vbCrLf _
  7.            & "      ,MDE_CODE" & vbCrLf _
  8.            & "      ,GENERIC_CODE" & vbCrLf _
  9.            & "      ,CASE_LINE_ITEM_QUANTITY" & vbCrLf _
  10.            & "      ,TOTAL_LINE_VALUE_AMOUNT" & vbCrLf _
  11.            & "      ,CASE_LINE_ITEM_DESCRIPTION " & vbCrLf _
  12.            & "  FROM LINE_IMP_VIEW " & vbCrLf _
  13.            & " 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:

VB Syntax (Toggle Plain Text)
  1. strSQL = "SELECT CASE_IDENTIFIER" & vbCrLf _
  2.        & "      ,DOCUMENT_NUMBER" & vbCrLf _
  3.        & "      ,DOCUMENT_TYPE" & vbCrLf _
  4.        & "      ,MILESTONE" & vbCrLf _
  5.        & "      ,MILESTONE_DATE" & vbCrLf _
  6.        & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
  7.        & "      ,MILESTONE_REMARKS" & vbCrLf _
  8.        & "  FROM MILESTONE_IMP_VIEW" & vbCrLf
  9.  
  10. strSQLWhere = " WHERE CASE_IDENTIFIER = '"
  11.  
  12. strSQLCIin = "   AND CASE_IDENTIFIER IN" & vbCrLf _
  13.            & "    (SELECT CASE_IDENTIFIER" & vbCrLf _
  14.            & "       FROM CASE_MASTER_TEMP)"
  15.  
  16. strSQLandDN = "   AND DOCUMENT_NUMBER = '"
  17.  
  18. strSQLDNin = "   AND DOCUMENT_NUMBER IN" & vbCrLf _
  19.            & "    (SELECT DOCUMENT_NUMBER" & vbCrLf _
  20.            & "       FROM CASE_MASTER_TEMP)" & vbCrLf

And later in the procedure:

VB Syntax (Toggle Plain Text)
  1. strSQL = strSQL & "" _
  2.        & strSQLWhere & .masterVars(j).CASEIDENTIFIER & "'" & vbCrLf _
  3.        & strSQLCIin & vbCrLf _
  4.        & strSQLandDN & .mileVars(j).DOCNUM & "'" & vbCrLf _
  5.        & strSQLDNin

This gives the correct string:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. SELECT CASE_IDENTIFIER
  2.       ,DOCUMENT_NUMBER
  3.       ,DOCUMENT_TYPE
  4.       ,MILESTONE
  5.       ,MILESTONE_DATE
  6.       ,MILESTONE_DATE_TYPE
  7.       ,MILESTONE_REMARKS
  8.   FROM MILESTONE_IMP_VIEW
  9.  WHERE CASE_IDENTIFIER = 'A2-B-OBM'
  10.    AND CASE_IDENTIFIER IN
  11.     (SELECT CASE_IDENTIFIER
  12.        FROM CASE_MASTER_TEMP)
  13.    AND DOCUMENT_NUMBER = '000'
  14.    AND DOCUMENT_NUMBER IN
  15.     (SELECT DOCUMENT_NUMBER
  16.        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:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. SELECT CASE_IDENTIFIER AS CASE_IDENTIFIER
  2.       ,DOCUMENT_TYPE AS DOCUMENT_TYPE
  3.       ,DOCUMENT_NUMBER AS DOCUMENT_NUMBER
  4.       ,MILESTONE AS MILESTONE
  5.       ,MILESTONE_DATE_TYPE AS MILESTONE_DATE_TYPE
  6.       ,MILESTONE_DATE AS MILESTONE_DATE
  7.       ,MILESTONE_REMARKS AS MILESTONE_REMARKS
  8.   FROM CASE_MILESTONE_TEMP
  9.  WHERE CASE_IDENTIFIER IN
  10.  	(SELECT CASE_IDENTIFIER
  11. 	   FROM CASE_MASTER_TEMP
  12. 	  WHERE (CASE_MASTER_STATUS_CODE = 'N'
  13. 	     OR CASE_MASTER_STATUS_CODE = 'P')
  14. 	    AND IMPLEMENTING_AGENCY = 'B'
  15. 	    AND (DOCUMENT_TYPE = 'AMD'
  16.     	     OR DOCUMENT_TYPE = 'LOA')
  17. 	    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:

VB Syntax (Toggle Plain Text)
  1. Public cn As New ADODB.Connection
  2. Public cmd As New ADODB.Command
  3. Public rs As New ADODB.Recordset
  4.  
  5. cmd.CommandText = qry
  6. cmd.CommandType = adCmdText
  7. Set rs = cmd.Execute

Any help is GREATLY appreciated!
teresa
Reputation Points: 10
Solved Threads: 0
Newbie Poster
pixelmeow is offline Offline
9 posts
since Oct 2008
Permalink
Oct 29th, 2008
0

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

I found the solution:

http://www.experts-exchange.com/Web_..._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
VB Syntax (Toggle Plain Text)
  1. cmd.CommandText = qry
  2. cmd.CommandType = adCmdText
  3. Set rs = cmd.Execute

to this:
VB Syntax (Toggle Plain Text)
  1. Set rs = New ADODB.Recordset
  2. rs.CursorLocation = adUseClient
  3. rs.Open qry, cn, adOpenKeyset, adLockOptimistic

That was all.
teresa
Last edited by pixelmeow; Oct 29th, 2008 at 3:25 pm. Reason: how to see the answer on the web page
Reputation Points: 10
Solved Threads: 0
Newbie Poster
pixelmeow is offline Offline
9 posts
since Oct 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: How I Creat Shortcut of My Project On Desktop
Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Limiting the Decimal Places and point





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC