Visual Basic 4 / 5 / 6 RSS Visual Basic 4 / 5 / 6 RSS

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

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site
Thread Solved

•
•
Join Date: Oct 2008
Posts: 9
Reputation: pixelmeow is an unknown quantity at this point 
Solved Threads: 0
pixelmeow pixelmeow is offline Offline
Newbie Poster

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

 
0
  #1
Oct 29th, 2008
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Oct 2008
Posts: 9
Reputation: pixelmeow is an unknown quantity at this point 
Solved Threads: 0
pixelmeow pixelmeow is offline Offline
Newbie Poster

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

 
0
  #2
Oct 29th, 2008
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
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the Visual Basic 4 / 5 / 6 Forum
Thread Tools Search this Thread



* 6 429 2007 access activex add age append application basic beginner birth bmp calculator cd cells.find click client code college column component connection connectionproblemusingvb6usingoledb copy creat ctrl+f data database datareport date delete dissertations dissertationthesis dissertationtopic edit error excel excelmacro file filename form hardware header iamthwee image inboxinvb internetfiledownload keypress label listbox listview liveperson login looping machine microsoft movingranges number objectinsert open oracle password prime program prompt range-objects readfile reading record refresh remotesqlserverdatabase report retrieve save search sendbyte sites sort sql sql2008 sqlserver subroutine table tags textbox time timer urldownloadtofile vb vb6 vb6.0 vba visual visualbasic visualbasic6 web window windows
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC