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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 getting data from sp in front end

Author  Topic 

svibuk
Yak Posting Veteran

62 Posts

Posted - 2013-02-03 : 02:26:12
have a SP with 2 begin statements as


begin
select id,name,country from details where id='0001'
end
begin
select lang as language from masterL where id='0001'
end

in front end i have data as
label1.text=dr["name"].tostring();
=dr["language"].tostring();
but i get error for language as indexoutofreangeexception

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-03 : 06:18:41
The results of the two selects would be in two result sets. So to be able to use the second result set, you need to move to the next result set. Depending on what method you are using to get the data from the database, there should be some method that will allow you to do this. If you are using ADO.Net, the method name is NextResult. So, in C# you would do the following:
label1.text=dr["name"].tostring();
...
dr.NextResult();
...
label2.text=dr["language"].tostring();
Go to Top of Page

svibuk
Yak Posting Veteran

62 Posts

Posted - 2013-02-04 : 04:45:48
Invalid attempt to read when no data is present.
i get error on using dr.NextResult();
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-04 : 06:17:51
Does the query "select lang as language from masterL where id='0001'" return any rows at all? You can open an SSMS query window and run the query to check. If it does, then, can you post the query/stored procedure you are using and the C# code?

Regardless of whether the query returns any rows or not, in your C# code, before using the data you should check if there are any rows returned at all. You can use HasRows property of the DataReader to do this. See here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.hasrows.aspx
Go to Top of Page

svibuk
Yak Posting Veteran

62 Posts

Posted - 2013-02-04 : 10:20:46
stored procedure when executed from sqlserver the rows are displayed as
needed
i get data for both the select stmts

i do check for rows
if (dr.HasRows)
{
while (dr.Read())
{

}}

i get error only for the second select stmt in the same procedure
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-04 : 19:35:15
Are you able to post the stored proc and the code? The fragments you posted look okay, so without seeing the code I am unable to suggest anything useful.

You could also go to this page and start with the example in the section "Retrieving Multiple Result Sets using NextResult", get that to work and compare it with your code to see what might be wrong. http://msdn.microsoft.com/en-us/library/haa3afyz.aspx
Go to Top of Page

svibuk
Yak Posting Veteran

62 Posts

Posted - 2013-02-05 : 02:28:54
stored procedure
ALTER PROCEDURE [dbo].[usp_mdetails]

@pMcid varchar(50)
AS
BEGIN

SET NOCOUNT ON;


select cid,name,dob,city,country from m_details m where m.cid=@pMcid

END

BEGIN

SELECT lang as language from M_LANG l where l.cid=@pMcid
END


====================== code========


SqlCommand cmd = new SqlCommand("usp_mdetails", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pcid", SqlDbType.VarChar).Value = txtcid.Text.Trim();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{

lblname.Text = dr["name"].ToString();
while (dr.Read())
{

lbllang.Text = dr["language"].ToString();
}
dr.NextResult();

}
}
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 07:38:42
Change the stored proc and the code to this:

ALTER PROCEDURE [dbo].[usp_mdetails]

@pMcid varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (1) cid,name,dob,city,country from m_details m where m.cid=@pMcid
SELECT TOP (1) lang as language from M_LANG l where l.cid=@pMcid
END
----------------------------------
SqlCommand cmd = new SqlCommand("usp_mdetails", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pcid", SqlDbType.VarChar).Value = txtcid.Text.Trim();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{

lblname.Text = dr["name"].ToString();
}
}
dr.NextResult();
if (dr.HasRows)
{
while (dr.Read())
{

lbllang.Text = dr["language"].ToString();
}
}
Go to Top of Page

svibuk
Yak Posting Veteran

62 Posts

Posted - 2013-02-05 : 10:18:44
thanks for the solution
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 10:53:40
You are very welcome ( assuming that, that fixed the problem. If it did not, don't give up now, it can be fixed)
Go to Top of Page

svibuk
Yak Posting Veteran

62 Posts

Posted - 2013-02-06 : 02:26:22
thanks
u r solution fixed the problem accurately

thanks once again
Go to Top of Page
   

- Advertisement -