July 8, 2002 at 12:17 pm
We have a stored proc that when executed from Query Analyzer returns a valid recordset however when the same statement is passed through VB it returns nothing (actually a value of -1).
The stored procedure takes in some variables and then uses those to build a dynamic SQL statement and then executes it using exec(@String). If we tweak the stored procedure to contain only a simple select it works fine.
What could be the problem?? I'm sure this is something stupid that I am overlooking so, I am looking forward to any answer you can give. Thanks in advance.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 8, 2002 at 12:55 pm
Try using set nocount on.
Andy
July 8, 2002 at 1:48 pm
Wait, I posted again but recant. Do you mean add the set nocount on in the VB command line or in the Stored Proc?
Edited by - DavidB on 07/08/2002 1:50:59 PM
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 8, 2002 at 5:25 pm
July 9, 2002 at 7:14 am
Andy - That was already done. See this is kind of weird and my lack of VB knowledge (although that is changing rapidly) is not helping here.
I actually found that the procedure is working "fine" and is returning the record set but the VB application is seeing a -1 when you print snapData.RecordCount (snapData is the recordset). All other procedures we have written return the correct value for the number of records. The only difference is that this procedure contains a dynamic SQL string that is executed at the end of the procedure. It's not critical to get the snapData.RecordCount value to be correct but I would like to know why that is not working at this point.
Any thoughts here would be greatly appreciated.
David
Edited by - DavidB on 07/09/2002 07:42:46 AM
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 11, 2002 at 6:39 am
Could you post the SP?
July 11, 2002 at 6:50 am
Here is a glimpse of a mocked up version of the procedure. Pretty straight forward. Any other thoughts.
create procedure usp_Test1
@filtid int,
@ownrep varchar(32),
@owngrp varchar(32)
as
set nocount on
declare
@sqlstr varchar(512),
@wherestmt varchar(512)
set @wherestmt = (
select FilterCriteria
from dbo.TaskFilter
where TaskFilterID = @filtid
)
set @sqlstr = (
'select *
from Test1
where
owner_grp = '''+@owngrp+'''
and owner_rep = '''+@ownrep+'''
and '+@wherestmt+'')
exec sp_executesql
@stmt = @sqlstr
go
**Note, this is a mocked up version and I don't know if this will run as I have not tried it.
One other note, we have moved forward with the procedure as it does return the data to VB but we are still struggling with the fact that the RecordCount in VB still returns -1. I have to believe it is because of the dynamic SQL but it seems strange that MS would not have seen this or that there are no other posts that I can find on the issue.
Intrigued...... and thanks for the reply. Looking forward to your thoughts.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 11, 2002 at 7:10 am
Actually can you post the code from vb (please alter security stuff)? It may be the type of cursor you are using in the recordset.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 11, 2002 at 8:09 am
We ran into this problem on our Intranet application. We had to add two lines:
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
This eliminated the -1 return problem.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 11, 2002 at 1:14 pm
Brian,
I tried your suggestion and still get the -1. Not a big problem but causes our VB guy to work with the Record Set differently.
Antares,
Here is the code snippet from the VB guy. Let me know if you have any inputs I can feed back.
gODBC_CONNECT_STRING = "ODBC;driver={SQL Server};UID=TLogin;PWD=TPassword;DATABASE=TestDB;SERVER=TestSrvr"
'------
' set the ODBC workspace variables
DBEngine.DefaultType = dbUseODBC
Set wsMain = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
'-----
' open the database
Dim snapData As Recordset
sConnect = gODBC_CONNECT_STRING
Set DBase = wsMain.OpenConnection("", dbDriverNoPrompt, False, sConnect)
DBase.QueryTimeout = 180
'-----
' get the tasks
sSQL = "execute dbo.usp_ExecuteFilter 1,’Tester’,’Tester Group’"
Set snapData = DBase.OpenRecordset(sSQL, dbOpenDynaset)
'-----
' store the tasks in the gridWith snapData
With snapData
If (Not .EOF) And (Not .BOF) Then
iRow = 1
vsTasks.Redraw = False
While Not .EOF
vsTasks.AddItem ""
vsTasks.TextMatrix(iRow, 0) = .Fields("product") & ""
vsTasks.TextMatrix(iRow, 1) = .Fields("project") & ""
vsTasks.TextMatrix(iRow, 2) = .Fields("category") & ""
vsTasks.TextMatrix(iRow, 3) = .Fields("short description") & ""
vsTasks.TextMatrix(iRow, 4) = .Fields("status") & ""
vsTasks.TextMatrix(iRow, 5) = Format(.Fields("estimated date") & "", "mm/dd/yyyy")
vsTasks.TextMatrix(iRow, 6) = .Fields("priority") & ""
vsTasks.TextMatrix(iRow, 7) = .Fields("incident") & ""
vsTasks.TextMatrix(iRow, 8) = .Fields("estimated hours") & ""
vsTasks.TextMatrix(iRow, 9) = .Fields("long description") & ""
vsTasks.TextMatrix(iRow, 10) = Format(.Fields("assigned date") & "", "mm/dd/yyyy")
vsTasks.TextMatrix(iRow, 11) = .Fields("owner") & ""
vsTasks.TextMatrix(iRow, 12) = .Fields("contact") & ""
vsTasks.TextMatrix(iRow, 13) = .Fields("contact phone") & ""
iRow = iRow + 1
.MoveNext
Wend
vsTasks.Redraw = True
End If
End With
'-----
' close the connections
snapData.Close
DBase.Close
End Sub
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 11, 2002 at 1:34 pm
Have him try this.
Dim recCnt as integer
Set snapData = DBase.OpenRecordset(sSQL, dbOpenDynaset)
snapData.MoveLast 'Read recordset into memory to get number.
recCnt = snapData.RecordCount
snapData.MoveFirst
and see if it returns.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 11, 2002 at 2:27 pm
July 11, 2002 at 3:04 pm
Yes, we are using DAO. I have been able to go through your ADO tutorial and found it to be pretty easy but I am just breaking into the VB stuff. Our apps guys are overworked and have not had the chance to check out ADO so, this project was pushed through the old school funnel.
I have provided your tutorial to them though so, hopefully it will move that way soon. Thanks.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply