| Author |
 Topic  |
|
|
svibuk
Yak Posting Veteran
50 Posts |
 Posted - 02/03/2013 : 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
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 02/03/2013 : 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(); |
 |
|
|
svibuk
Yak Posting Veteran
50 Posts |
Posted - 02/04/2013 : 04:45:48
|
Invalid attempt to read when no data is present.
i get error on using dr.NextResult(); |
|
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 02/04/2013 : 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 |
|
|
|
svibuk
Yak Posting Veteran
50 Posts |
Posted - 02/04/2013 : 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 |
Edited by - svibuk on 02/04/2013 10:25:27 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 02/04/2013 : 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 |
|
|
|
svibuk
Yak Posting Veteran
50 Posts |
Posted - 02/05/2013 : 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();
}
}
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 02/05/2013 : 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();
}
}
|
|
|
|
svibuk
Yak Posting Veteran
50 Posts |
Posted - 02/05/2013 : 10:18:44
|
thanks for the solution
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 02/05/2013 : 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) |
|
|
|
svibuk
Yak Posting Veteran
50 Posts |
Posted - 02/06/2013 : 02:26:22
|
thanks
u r solution fixed the problem accurately
thanks once again |
|
|
| |
Topic |
|
getting data from sp in front end
