SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 getting data from sp in front end
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

svibuk
Yak Posting Veteran

62 Posts

Posted - 02/03/2013 :  02:26:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 02/03/2013 :  06:18:41  Show Profile  Reply with Quote
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 - 02/04/2013 :  04:45:48  Show Profile  Reply with Quote
Invalid attempt to read when no data is present.
i get error on using dr.NextResult();
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 02/04/2013 :  06:17:51  Show Profile  Reply with Quote
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 - 02/04/2013 :  10:20:46  Show Profile  Reply with Quote
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

Edited by - svibuk on 02/04/2013 10:25:27
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 02/04/2013 :  19:35:15  Show Profile  Reply with Quote
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 - 02/05/2013 :  02:28:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 02/05/2013 :  07:38:42  Show Profile  Reply with Quote
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 - 02/05/2013 :  10:18:44  Show Profile  Reply with Quote
thanks for the solution
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 02/05/2013 :  10:53:40  Show Profile  Reply with Quote
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 - 02/06/2013 :  02:26:22  Show Profile  Reply with Quote
thanks
u r solution fixed the problem accurately

thanks once again
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000