Author |
Topic |
svibuk
Yak Posting Veteran
62 Posts |
Posted - 2013-02-03 : 02:26:12
|
have a SP with 2 begin statements asbeginselect id,name,country from details where id='0001'endbeginselect lang as language from masterL where id='0001'endin 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(); |
|
|
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(); |
|
|
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 |
|
|
svibuk
Yak Posting Veteran
62 Posts |
Posted - 2013-02-04 : 10:20:46
|
stored procedure when executed from sqlserver the rows are displayed asneededi get data for both the select stmtsi do check for rowsif (dr.HasRows) { while (dr.Read()) {}}i get error only for the second select stmt in the same procedure |
|
|
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 |
|
|
svibuk
Yak Posting Veteran
62 Posts |
Posted - 2013-02-05 : 02:28:54
|
stored procedureALTER PROCEDURE [dbo].[usp_mdetails] @pMcid varchar(50)ASBEGIN SET NOCOUNT ON; select cid,name,dob,city,country from m_details m where m.cid=@pMcidENDBEGINSELECT lang as language from M_LANG l where l.cid=@pMcidEND ====================== 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
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)ASBEGINSET NOCOUNT ON;SELECT TOP (1) cid,name,dob,city,country from m_details m where m.cid=@pMcidSELECT TOP (1) lang as language from M_LANG l where l.cid=@pMcidEND----------------------------------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
62 Posts |
Posted - 2013-02-05 : 10:18:44
|
thanks for the solution |
|
|
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) |
|
|
svibuk
Yak Posting Veteran
62 Posts |
Posted - 2013-02-06 : 02:26:22
|
thanksu r solution fixed the problem accuratelythanks once again |
|
|
|