Click here to Skip to main content
15,919,341 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
Using C# in Visual Studio, how to populate Form ListBox with SqlServer stored-procedure (containing select of one column from one table with no where-criteria)?

Current code:
C#
SqlConnection conn = new SqlConnection(connectionString);
try
{
   conn.Open();
   SqlCommand cmd = new SqlCommand("pr_MySelectProcedure", conn);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Connection = conn;
   string tempVal = string.Empty;
   using (SqlDataReader reader = cmd.ExecuteReader())
   {
      // if (reader.HasRows == true) ... passed this test, so continue with other code
      do
      {
         // passes test: if (reader.FieldCount > 0)
         tempVal = reader[0].ToString(); // exception error here: no data present
         // also fails with: tempVal = reader["firstcolumnname"].ToString();
      } while (reader.Read());
   }
}
catch ...

Thank you!
Posted
Updated 12-Apr-12 8:46am
v3
Comments
[no name] 12-Apr-12 14:21pm    
Apologies, I see that the other one was deleted. In the future you can use the "Improve question" widget to edit your question.
D. Schrenk 12-Apr-12 14:23pm    
Deleted previous question and entered new question with more Tags (as requested) and with more info.
Shahin Khorshidnia 12-Apr-12 14:28pm    
You don't need to delete the question and repost it.
You can simply click on Improve question (Green link) and edit or re-tag it.
Thank you
Shahin Khorshidnia 12-Apr-12 14:29pm    
And it still needs to re-tag. I ask you specify it. What is the base of your application? WinForm? or ASP.Net or WPF? or other?

1 solution

You need to call reader.Read() before trying to access the data. In your example you are calling it after. Change your "do" loop to a "while" should fix it.

C#
while(reader.Read())
{
   tempVal = reader[0].ToString(); 
}
 
Share this answer
 
v3
Comments
D. Schrenk 12-Apr-12 14:53pm    
The compiler would not let me have the while-stmt with the do-stmt. I will try again. Looking at other c# code, I will try to do one Read before the do-while loop. Thank you.
[no name] 12-Apr-12 14:59pm    
See updated
D. Schrenk 12-Apr-12 15:25pm    
I will change code to use your while-loop. Adding one Read before the do-loop also solved the problem--as you suspected, no data had actually been read yet. I successfully inserted the retrieved db data into the ListBox on the Form. Thank you!
[no name] 12-Apr-12 15:30pm    
Great! You're welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900