SP not showing results on spreadsheet VB.net

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

SP not showing results on spreadsheet VB.net

Author

Topic

rottenpinky
Starting Member

1 Post

Posted - 2011-03-11 : 14:11:21

Hi everybody, I'm new to this forum and kinda new to all this SQL and VB.net programming and I'm having a few problems using SQL Server 2008, VB.net 2008 and Farpoint Spread 8.0.

I'm trying to run a Stored Procedure which returns a resultset and then displaying those results in a FPS sheet. My stored procedure looks like this (I'm using this SP only for test purposes).

create procedure [dbo].[sp_prueba2]
@numero int
AS
Begin
if @numero = 1
select empresa, nombre, clave from usuarios where Nombre = 'PINKY'
if @numero = 2
select empresa, nombre, clave from usuarios where Nombre = 'ZUKO'
if @numero not in (1,2)
select empresa, nombre, clave from usuarios
return 0
END

After creating the stored procedure, I use this code to call it from VB.net using ADODB on my form1_load sub.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sqlstr As String = "Execute sp_prueba2 0" 'If I change this execute statement for a select statement, it does return data
cnn = New ADODB.Connection
rst = New ADODB.Recordset
cnn.Open("provider= sqloledb;Integrated Security=SSPI;Persist Security Info=True;" & _
"data source = localhost;" & _
"initial catalog = database; user ID = user; Password = password")
rst.Open(sqlstr, cnn, ADODB.CursorTypeEnum.adOpenStatic)
sp1.DataSource = rst 'sp1 is my spreadsheet

After this, all i get on my spreadsheet are the column's names (Empresa, Nombre, Clave) but no data. I already ran the procedure directly from SQL server 2008 and it DOES return data. I also modified the code to run a select statement directly and that way, the spreadsheet also displays the data, but i need it to be done using a Stored Procedure. Is there a way I can do it?

PS: Excuse my bad english, it's not my native language.

Thank you.

No God, No Fate, only the Power of Will.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources