Advertisement

09.05.2008 at 12:49AM PDT, ID: 23705391
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.2

ADODB.Recordset.RecordCount = -1 but Do Until ADODB.Recordset.EOF c= c+1 Loop MsgBox(c) =  3000+ rows...hmmm

Asked by smartwebagent in VB Database Programming, MS SQL Server, SQL Server 2005

Tags: , , , ,

Using the below code, the ADODB.Recordset object is maintaining a constant state where its RecordCount is -1.  I am receiving Invalid Recordset error messages.  If I ask whether the returned recordset is NULL, the recordset is NULL, but yet if I loop through the recordset until the end of file marker the INT counter variable counts up a variable number of rows based on the criteria input to the stored procedure.  Which brings up the issue of what the stored procedure is doing.  It is accepting optional parameters as all of its parameters are set to NULL.  I tried both with and without the OUTPUT variable seeing if I could get a look at whether the ADODB.Recordset could even retrieve a simple variable's value much less the recordset needed to be displayed.  The result of trying to retrieve the OUTPUT variable's value is again an error stating that the ordinal or index doesn't exist for the Parameter("strSQL") which clearly was added with an emtpy value.  Even the empty value portion gives rise to questions as the difference in VB6 between Nothing, Null, vbNull, vbNullString, and what the VB6 Watch or Immediate Window will call Empty when a declared variable is not yet instantiated or set to a value which could even be "" or an empty string for a String or VARCHAR or NVARCHAR variable.  

Thanks for your time and knowledge.

Respectfully,

John Flaherty
smartwebagent.com
Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:

T-SQL (SQL Server 2005)
-----------------------
USE [DatabaseName]
GO
--PRINT USER
--GO
/****** Object:  StoredProcedure [dbo].[MSSQLServer2005TSQLStoredProcedureName]    Script Date: 09/05/2008 00:28:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[MSSQLServer2005TSQLStoredProcedureName] 
(    
    @DateFieldName DATETIME = NULL,
    @IntFieldName INT = NULL,
    @NVarCharFieldName NVARCHAR(100) = NULL,
    @strSQL NVARCHAR(1024) = NULL OUTPUT
)
AS
/*
  Declared intentionally out of scope of Try
  so that these variables would be available in Catch
  below marker, TheEnd.
*/
DECLARE @intErrorID INT
DECLARE @strErrorMessage NVARCHAR(4000)
 
BEGIN TRY
 
    DECLARE @blnDateFieldNameExists BIT;
    DECLARE @blnIntFieldNameExists BIT;
    DECLARE @blnNVarCharFieldNameExists BIT;
    DECLARE @strSQLWhere NVARCHAR(512);
 
    SET @intErrorID = 0;
    SET @strErrorMessage = '';
    SET @blnDateFieldNameExists = 0;
    SET @blnIntFieldNameExists = 0;
    SET @blnNVarCharFieldNameExists = 0;
    SET @strSQL = '';
    SET @strSQLWhere = '';
 
    IF (@DateFieldName IS NOT NULL)
    BEGIN
       IF (IsDate(@DateFieldName) <> 1)
       BEGIN
          SET @intErrorID = -1;
          SET @strErrorMessage = 'Invalid Date field name.';
          GOTO TheEnd;
       END
       SET @blnDateFieldNameExists = 1;
    END
 
    IF (@IntFieldName IS NOT NULL)
    BEGIN
       IF (@IntFieldName < 1)
       BEGIN
         SET @intErrorID = -2;
         SET @strErrorMessage = 'Invalid Int field name.';
         GOTO TheEnd;
       END
       SET @blnIntFieldNameExists = 1;
    END
 
    IF (@NVarCharFieldName IS NOT NULL)
    BEGIN
       IF (LEN(@NVarCharFieldName) <= 0)
       BEGIN
          SET @intErrorID = -3;
          SET @strErrorMessage = 'Invalid NVarChar field name.';
          GOTO TheEnd;
       END
       SET @blnNVarCharFieldNameExists = 1;
    END
 
    SET @strSQL = 'SELECT * FROM EMPLOYEES'
 
    IF (@blnDateFieldNameExists = 1)
    BEGIN
       IF (LEN(@strSQLWhere) > 0)
       BEGIN
          SET @strSQLWhere = @strSQLWhere + ' AND HireDate = @DateFieldName';
       END              
       IF (LEN(@strSQLWhere) = 0)
       BEGIN
          SET @strSQLWhere = ' WHERE HireDate = @DateFieldName';
       END
    END
 
    IF (@blnIntFieldNameExists = 1)
    BEGIN
       IF (LEN(@strSQLWhere) > 0)
       BEGIN
          SET @strSQLWhere = @strSQLWhere + ' AND DepartmentID = @IntFieldName';
       END              
       IF (LEN(@strSQLWhere) = 0)
       BEGIN
          SET @strSQLWhere = ' WHERE DepartmentID = @IntFieldName';
       END
    END
 
    IF (@blnNVarCharFieldNameExists = 1)
    BEGIN
       IF (LEN(@strSQLWhere) > 0)
       BEGIN
          SET @strSQLWhere = @strSQLWhere + ' AND LastName = @NVarCharFieldName';
       END              
       IF (LEN(@strSQLWhere) = 0)
       BEGIN
          SET @strSQLWhere = ' WHERE LastName = @NVarCharFieldName';
       END
    END
 
    IF (LEN(@strSQLWhere) = 0)
    BEGIN
       SET @strSQL = 'SELECT * FROM Employees';
    END
    EXECUTE sp_executesql @strSQL,
                          N'@DateFieldName DATETIME,
                            @IntFieldName INT,
                            @NVarCharFieldName NVARCHAR(100)',
                          @DateFieldName,
                          @IntFieldName,
                          @NVarCharFieldName
END TRY
BEGIN CATCH
    DECLARE @intTSQLErrorNumber INT;
    DECLARE @intTSQLErrorSeverity INT;
    DECLARE @intTSQLErrorState INT;
    DECLARE @strTSQLErrorMessage NVARCHAR(2048);
    DECLARE @strTSQLErrorProcedure NVARCHAR(126);
    DECLARE @intTSQLErrorLine INT;
 
    SELECT   @intTSQLErrorNumber    = ERROR_NUMBER(),
             @intTSQLErrorSeverity  = ERROR_SEVERITY(),
             @intTSQLErrorState     = ERROR_STATE(),
             @strTSQLErrorMessage   = ERROR_MESSAGE(),
             @strTSQLErrorProcedure = ERROR_PROCEDURE(),
             @intTSQLErrorLine      = ERROR_LINE();
 
    SET @intErrorID = 5;
 
    SET @strErrorMessage = 'Error Severity: ' +
                           RTRIM(STR(@intTSQLErrorSeverity)) + '**' +
                           'Error State: ' +
                           RTRIM(STR(@intTSQLErrorState)) + '**' +
                           'Error Number: ' +
                           RTRIM(STR(@intTSQLErrorNumber)) + '**' + 
                           'Error Message: ' +
                           @strTSQLErrorMessage + '**' +
                           'Error Procedure: ' +
                           @strTSQLErrorProcedure + '**' +
                           'Error Line: ' +
                           RTRIM(STR(@intTSQLErrorLine));
END CATCH;
TheEnd:
IF (@intErrorID <> 0)
BEGIN
   --If an error occured return it along with the SQL command
   SELECT @intErrorID AS ErrorID,
          @strErrorMessage AS ErrorMessage,
          @strSQL AS SQLCommand
   RETURN
END
/*
IF (LEN(@strErrorMessage) > 0)
BEGIN
   PRINT 'Error did occur.';
   PRINT CAST(@intErrorID AS NVARCHAR(10));
   PRINT @strErrorMessage;
END
ELSE IF (@intErrorID < 0)
BEGIN
   PRINT 'Error did occur.';
   PRINT CAST(@intErrorID AS NVARCHAR(10));
   PRINT @strErrorMessage;;
END
ELSE
BEGIN
   PRINT @strSQL;
   PRINT 'Error did not occur.';
   PRINT @strSQLSelect;
   PRINT @strSQLFrom;
   PRINT @strSQLInnerJoin;
   PRINT @strSQLWhere;
   PRINT @strSQLOrderBy;
END
 
--TSQL CODE TO TEST THE ABOVE STORED PROCEDURE CODE
DECLARE     @DateFieldName     DATETIME;
SET         @DateFieldName     = NULL;
 
DECLARE     @IntFieldName INT;
SET         @IntFieldName = NULL;
 
DECLARE     @NVarCharFieldName NVARCHAR(100);
SET         @NVarCharFieldName = NULL;
 
DECLARE     @strSQL NVARCHAR(100);
SET         @strSQL = NULL;
 
EXECUTE MSSQLServer2005TSQLStoredProcedureName   @DateFieldName,
                                                 @IntFieldName,
 	                                        @NVarCharFieldName,
                                                 @strSQL
*/
 
 
 
 
 
 
 
----------------VB6------------------
 
 
 
Private Sub Button1_Click()
  'Ref: http://support.microsoft.com/kb/194792
  
  Dim ADODBConnection As ADODB.Connection
  Dim ADODBCommand As New ADODB.Command
  Dim ADODBParameter As New ADODB.Parameter
  Dim ADODBRecordset As ADODB.Recordset
  Dim TotalNumberOfRows As Long
'  Dim ConnectionString As String
'  ConnectionString = "Server=localhost;Database=ImpactMD350;Trusted_Connection=yes;UID=DefaultUser;PWD=123Abc;"
'  Set ADODBConnection = New ADODB.Connection
'  With ADODBConnection
'     .Provider = "SQLOLEDB"
'     .CursorLocation = adUseServer  'Must use Server side cursor.
'     .CommandTimeout = 30
'     .Open ConnectionString
'  End With
'  MsgBox ("ADODB Connection.State = " & ADODBConnection.State)
'
  Set ADODBConnection = New ADODB.Connection
  With ADODBConnection
     .Open pstrConnectionString
  End With
  'Set ADODBCommand to call the stored procedure
  'using the ADODBConnection object
  Set ADODBCommand = New ADODB.Command
  Set ADODBCommand.ActiveConnection = ADODBConnection
  With ADODBCommand
     .CommandText = "MSSQLServer2005TSQLStoredProcedureName"
     .CommandType = adCmdStoredProc
     .Prepared = False
  End With
 
  'Parameter 0 is the stored procedure Return code.
  '  Set ADODBParameter = ADODBCommand.CreateParameter("Return", adInteger, _
  '                                                    adParamReturnValue, , 0)
  '  ADODBCommand.Parameters.Append ADODBParameter
  '  ADODBCommand.Parameters("Return").Value = -1
      
  'OPTIONAL input parameters for the
  'stored procedure input parameters.
  ADODBCommand.Parameters.Refresh
  If (IsDate(TextBox1.Text)) Then
     Set ADODBParameter = ADODBCommand.CreateParameter("DateFieldName", adDate, adParamInput)
     ADODBCommand.Parameters.Append ADODBParameter
     ADODBCommand.Parameters("DateFieldName").Value = TextBox1.Text
  Else
     Set ADODBParameter = ADODBCommand.CreateParameter("DateFieldName", adDate, adParamInput)
     ADODBCommand.Parameters.Append ADODBParameter
  End If
  
  If (Trim$(TextBox2.Text) <> vbNullString) Then
     TextBox2.Text = Trim$(TextBox2.Text)
     Set ADODBParameter = ADODBCommand.CreateParameter("IntFieldName", adInteger, adParamInput)
     ADODBCommand.Parameters.Append ADODBParameter
     ADODBCommand.Parameters("IntFieldName").Value = TextBox2.Text
  Else
     Set ADODBParameter = ADODBCommand.CreateParameter("IntFieldName", adInteger, adParamInput)
     ADODBCommand.Parameters.Append ADODBParameter
  End If
  
  If (Trim$(TextBox3.Text) <> vbNullString) Then
     TextBox3.Text = Trim$(TextBox3.Text)
     Set ADODBParameter = ADODBCommand.CreateParameter("NVarCharFieldName", adVarChar, adParamInput, 100)
     ADODBCommand.Parameters.Append ADODBParameter
     ADODBCommand.Parameters("IntFieldName").Value = CInt(Val(TextBox3.Text))
  Else
     Set ADODBParameter = ADODBCommand.CreateParameter("NVarCharFieldName", adVarChar, adParamInput, 100)
     ADODBCommand.Parameters.Append ADODBParameter
  End If
  
  Set ADODBParameter = ADODBCommand.CreateParameter("strSQL", adVarChar, adParamOutput, 1024)
  ADODBCommand.Parameters.Append ADODBParameter
  
  Set ADODBRecordset = Nothing
  Set ADODBRecordset = New Recordset
  'ADODBCommand.Parameters.Refresh
    
  'Set ADODBRecordset = ADODBCommand.Execute
  MsgBox ("Just before execution, ADODB Connection.State = " & ADODBConnection.State)
  With ADODBCommand
     Call .Execute(TotalNumberOfRows, , adExecuteNoRecords)
  End With
  MsgBox ("Just after execution, ADODB Connection.State = " & ADODBConnection.State)
  
  'ADODBRecordset.Open ADODBCommand,, adOpenKeyset, adLockBatchOptimistic
  'ADODBRecordset.Open ADODBCommand, , adOpenStatic, adLockOptimistic
  'ADODBRecordset.Open ADODBCommand, , adOpenStatic, adLockReadOnly
  
    'The ADODBRecordset will use the ADODBConnection object's
  'server-side cursor and a static (navigable) cursor type
  '  With ADODBRecordset
  '     .CursorType = adOpenStatic
  '     .LockType = adLockBatchOptimistic
  '  End With
  '  Set ADODBRecordset = ADODBCommand.Execute
 
  'Always displaying a record count of -1 despite the counter variable, c,
  'clearly indicating that rows exists in the ADODBRecordset object as c=3000+ rows
  'depending on when on the run the test against the SQL query
  'Using the current version of executing that is uncommented above
  'the adExecuteNoRecords option is set.
  'This will cause an error to occur if I uncomment the MsgBox below
  'MsgBox ("After Opening: " & ADODBRecordset.RecordCount)
  'This msg is currently throwing an error stating the ordinal doesn't exist
  'as if to say that the output parameter doesn't exist in the ADORecordset.
  'The Loop below however will reveal that c, the counter variable, has a value of 3000+ rows.
  'This seems to indicate that 3000+ rows exist.
  'The example at http://support.microsoft.com/kb/194792
  'is what I'll use here as a starting point for
  'why I did what I did exactly.
  MsgBox (ADODBCommand.Parameters("strSQL").Value)
  If Not ADODBRecordset Is Nothing Then
  Else
     Dim c As Long
     ADODBRecordset.MoveFirst
     Do Until ADODBRecordset.EOF
        c = c + 1
        ADODBRecordset.MoveNext
     Loop
  End If
  'MsgBox (Str(c) & " = i, Before Closing: " & ADODBRecordset.RecordCount)
 
  If ADODBRecordset.State = adStateOpen Then ADODBRecordset.Close
  Set DataGrid1.DataSource = ADODBRecordset
  'DataGrid1.Refresh
  Set ADODBRecordset = Nothing
  Set ADODBParameter = Nothing
  Set ADODBCommand = Nothing
  Set ADODBConnection = Nothing
End Sub
Attachments:
 
Debugging.jpeg (3 KB) (File Type Details) 
Showing the counter variable, c, counted to 253 rows returned in that try. 3000+ rows in others. RecordCount always = -1.
Showing the counter variable, c, counted to 253 rows returned in that try. 3000+ rows in others. RecordCount always = -1.
 
[+][-]09.05.2008 at 12:54AM PDT, ID: 22396157

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: VB Database Programming, MS SQL Server, SQL Server 2005
Tags: Microsoft, Visual Basic 6, 6.0 SP6, Using Microsoft SQL Server 2005 to execute a dynamic SQL query using sp_executesql with optional parameters set to NULL in stored procedure., Visual Basic 6, MS ADO, SQL Server 2005 T-SQL, optional nulls, sp_executesql
Sign Up Now!
Solution Provided By: angelIII
Participating Experts: 3
Solution Grade: A
 
 
[+][-]09.05.2008 at 03:39AM PDT, ID: 22396996

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]09.05.2008 at 05:08AM PDT, ID: 22397755

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]09.29.2008 at 01:21AM PDT, ID: 22594567

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
[+][-]10.03.2008 at 06:19PM PDT, ID: 22639117

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628