[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.odbcnet

Stored Procedure does not return multiple tables.

Dcorso

6/25/2002 6:33:00 PM

I am passing a single parameter to a stored procedure that returns two or
more tables. The first table is created in the dataset with no problem but
I get a NullReferenceException error. Here is the code.


Dim oConn As New OdbcConnection()
Dim oCom As New OdbcCommand()
Dim da As New OdbcDataAdapter()
Dim ds As New DataSet()

oConn.ConnectionString = "DSN=" & ServerName & ";UID=user;PWD=pwd;"
oConn.Open()
oCom.Connection = oConn
oConn.ChangeDatabase(db.Name)
oCom.CommandType = CommandType.StoredProcedure
oCom.Parameters.Add("@parameter", OdbcType.Char, 10)
oCom.Parameters("@parameter").Value = "test"
oCom.CommandText = "{call test_p (?)}"
da.SelectCommand = oCom
da.Fill(ds)
8 Answers

(Hussein Abuthuraya(MSFT))

6/25/2002 11:04:00 PM

0

If you execut multi-select command or a stored procedure that returns multible tables, the table names will be "Table" for the first Select and "Table1" for the second select
and so on. So how you are checking for the tables that are in the dataset?

Have you tried to loop through the tables collection in the dataset to check on what's there? You may use codesimilar to this:

Dim dt As DataTable
For Each dt In ds.Tables
MessageBox.Show(dt.TableName)
Next

Is it possible to post the SP script?


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.co....

Dcorso

6/25/2002 11:33:00 PM

0

I loop through the tables using ds.tables.count. If I run the same proc
with no parameters, I get the dataset with both tables. As soon as I add
the parameter, I get the dataset with the first table before it throws a
nullexception error.


"Hussein Abuthuraya(MSFT)" <HussAbOnline@microsoft.com> wrote in message
news:uv9EEvIHCHA.1600@cpmsftngxa08...
> If you execut multi-select command or a stored procedure that returns
multible tables, the table names will be "Table" for the first Select and
"Table1" for the second select
> and so on. So how you are checking for the tables that are in the
dataset?
>
> Have you tried to loop through the tables collection in the dataset to
check on what's there? You may use codesimilar to this:
>
> Dim dt As DataTable
> For Each dt In ds.Tables
> MessageBox.Show(dt.TableName)
> Next
>
> Is it possible to post the SP script?
>
>
> Thanks,
> Hussein Abuthuraya
> Microsoft Developer Support
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.co....
>
>

Dcorso

6/25/2002 11:37:00 PM

0

Also, if I use the parameter and only return the first table, it works
without an error.

"Dcorso" <dcorso@reitmr.com> wrote in message
news:e7HIp$IHCHA.2412@tkmsftngp11...
> I loop through the tables using ds.tables.count. If I run the same proc
> with no parameters, I get the dataset with both tables. As soon as I add
> the parameter, I get the dataset with the first table before it throws a
> nullexception error.
>
>
> "Hussein Abuthuraya(MSFT)" <HussAbOnline@microsoft.com> wrote in message
> news:uv9EEvIHCHA.1600@cpmsftngxa08...
> > If you execut multi-select command or a stored procedure that returns
> multible tables, the table names will be "Table" for the first Select and
> "Table1" for the second select
> > and so on. So how you are checking for the tables that are in the
> dataset?
> >
> > Have you tried to loop through the tables collection in the dataset to
> check on what's there? You may use codesimilar to this:
> >
> > Dim dt As DataTable
> > For Each dt In ds.Tables
> > MessageBox.Show(dt.TableName)
> > Next
> >
> > Is it possible to post the SP script?
> >
> >
> > Thanks,
> > Hussein Abuthuraya
> > Microsoft Developer Support
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > Are you secure? For information about the Microsoft Strategic Technology
> Protection Program and to order your FREE Security Tool Kit, please visit
> > http://www.microsoft.co....
> >
> >
>
>

(Hussein Abuthuraya(MSFT))

6/25/2002 11:58:00 PM

0

Is it possible to post the Stored Procedure script?


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.co....

Dcorso

6/26/2002 12:01:00 AM

0


create proc dave_test_p
(@parameter char(10) = null )
as
begin
select 'PAYER', payer_id, convert(char(30),Payer_name) from payer
select 'PLACE', place_id, convert(char(30),PLace_name) from place
end



"Hussein Abuthuraya(MSFT)" <HussAbOnline@microsoft.com> wrote in message
news:z07uDNJHCHA.2512@cpmsftngxa07...
> Is it possible to post the Stored Procedure script?
>
>
> Thanks,
> Hussein Abuthuraya
> Microsoft Developer Support
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.co....
>
>

(Hussein Abuthuraya(MSFT))

6/26/2002 3:49:00 AM

0

OK. The stored procedure name that you sent me is "dave_test_p" and the one in the code is "test_p". Is this a typo or you are executing the wrong stored procedure?

Other than this, the script looks fine. I tried to reproduce the behavior on SQL Server and it always works. I don't see a relation between passing a parameter or not
because the parameter value is not used in the stored procedure.

Here is what I tried:
Dim nwindConn As OdbcConnection = New OdbcConnection("dsn=myDSN;trusted_connection=yes")
Dim salesCMD As OdbcCommand = New OdbcCommand("{call dave_test_p(?)}", nwindConn)
salesCMD.CommandType = CommandType.StoredProcedure

Dim myParm As OdbcParameter = salesCMD.Parameters.Add("@parameter", OdbcType.Char, 10)
myParm.Value = "test"

Dim da As New OdbcDataAdapter()
Dim ds As New DataSet()

da.SelectCommand = salesCMD
da.Fill(ds)

Dim dt As DataTable
For Each dt In ds.Tables
MessageBox.Show(dt.TableName)
Next

SP:
ALTER proc dave_test_p
(@parameter char(10) = null )
as
begin
select 'customers', Customerid, convert(char(30),CompanyName) from customers
select 'Orders' , orderid, convert(char(30),ShipName) from orders
end



Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.co....

Dcorso

6/26/2002 7:27:00 PM

0

Sorry for the typo. The name had been changed. I have run this on an MS
SQL server and it runs fine. The database in question however is on a
Syabse SQL server.



"Hussein Abuthuraya(MSFT)" <HussAbOnline@microsoft.com> wrote in message
news:m1EYWOLHCHA.1560@cpmsftngxa07...
> OK. The stored procedure name that you sent me is "dave_test_p" and the
one in the code is "test_p". Is this a typo or you are executing the wrong
stored procedure?
>
> Other than this, the script looks fine. I tried to reproduce the behavior
on SQL Server and it always works. I don't see a relation between passing a
parameter or not
> because the parameter value is not used in the stored procedure.
>
> Here is what I tried:
> Dim nwindConn As OdbcConnection = New
OdbcConnection("dsn=myDSN;trusted_connection=yes")
> Dim salesCMD As OdbcCommand = New OdbcCommand("{call
dave_test_p(?)}", nwindConn)
> salesCMD.CommandType = CommandType.StoredProcedure
>
> Dim myParm As OdbcParameter =
salesCMD.Parameters.Add("@parameter", OdbcType.Char, 10)
> myParm.Value = "test"
>
> Dim da As New OdbcDataAdapter()
> Dim ds As New DataSet()
>
> da.SelectCommand = salesCMD
> da.Fill(ds)
>
> Dim dt As DataTable
> For Each dt In ds.Tables
> MessageBox.Show(dt.TableName)
> Next
>
> SP:
> ALTER proc dave_test_p
> (@parameter char(10) = null )
> as
> begin
> select 'customers', Customerid, convert(char(30),CompanyName) from
customers
> select 'Orders' , orderid, convert(char(30),ShipName) from orders
> end
>
>
>
> Thanks,
> Hussein Abuthuraya
> Microsoft Developer Support
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.co....
>
>

(Hussein Abuthuraya(MSFT))

6/26/2002 8:51:00 PM

0

I'm sorry but out there should be somebody else with Sybase experience who could shed some light on this particular problem with Sybase. Whay don't you post more
details about the Sybase server version and maybe the OleDB provider version and maker you are using?

Anybody Sybase gurus?


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.co....