Not getting all results from SqlDataReader RSS

• • Reply
  

  sab0tr0n

  Member

  2 Points

  5 Posts

  Not getting all results from SqlDataReader

  May 05, 2012 02:49 PM|LINK

  Can anybody help me with the issue i'm seeing?  For some reason when I run my page, I get my drop down lists to populate the data, however the first item in my database, per each sql query, doesn't get populated. 

  For example:

  my database table:

  Category

  1 Books

  2 Clothing

  3 Toys

  4 Household Items

  my first query - USE LakerBids SELECT Category FROM ProductCategories my drop down list gets populated with

  Clothing

  Toys

  Household Items

   

  I have 2 other drop down lists I'm populating and those are doing the same thing.  Once I get this figured out, i'll try to figure out the other problem I'm having with inserting the data in the database.

  Thank you!

  using System;
  using System.Collections.Generic;
  using System.Configuration;
  using System.Linq;
  using System.Web;
  using System.Web.UI;
  using System.Web.UI.WebControls;
  using System.Data.SqlClient;
  
  namespace eLakeland
  {
      public partial class InsertItems : System.Web.UI.Page
      {
          protected void Page_Load(object sender, EventArgs e)
          {
              SqlConnection connection;
              SqlCommand populateList;
              SqlDataReader reader;
  
              string connectionString = ConfigurationManager.ConnectionStrings["LakerBids"].ConnectionString;
              connection = new SqlConnection(connectionString);
              populateList = new SqlCommand("USE LakerBids SELECT Category FROM ProductCategories;" +
                                      "USE LakerBids SELECT SubCategory FROM ProductSubCategories;" +
                                    "USE LakerBids SELECT LName FROM Users", connection);
              if (!IsPostBack)
              {
                  try
                  {
                      connection.Open();
                      reader = populateList.ExecuteReader();
  
                      while (reader.Read())
                      {
                          pcategory.DataSource = reader;
                          pcategory.DataValueField = "Category";
                          pcategory.DataBind();
                      }
                      reader.NextResult();
                      while (reader.Read())
                      {
                          psubcategory.DataSource = reader;
                          psubcategory.DataValueField = "SubCategory";
                          psubcategory.DataBind();
                      }
                      reader.NextResult();
                      while (reader.Read())
                      {
                          user.DataSource = reader;
                          user.DataValueField = "LName";
                          user.DataBind();
                      }
                      reader.Close();
                  }
                  finally
                  {
                      connection.Close();
                  }
              }
          }
  
          protected void AddItem(object sender, EventArgs e)
          {
              if (Page.IsValid)
              {
                  SqlConnection connection;
                  SqlCommand insertData;
  
                  string connectionString = ConfigurationManager.ConnectionStrings["LakerBids"].ConnectionString;
                  connection = new SqlConnection(connectionString);
                  insertData = new SqlCommand("INSERT INTO Products (ProductName, ProductDesc, CategoryID, SubCatID, StatusID, UserID, ReservePrice, AuctionLength, BidID)" +
                  "VALUES (@ProductName, @ProductDesc, @CategoryID, @SubCatID, 1, @UserID, @ReservePrice, @AuctionLength, NULL)", connection);
  
                  insertData.Parameters.Add("@ProductName", System.Data.SqlDbType.NVarChar, 50);
                  insertData.Parameters["@ProductName"].Value = pname.Text;
  
                  insertData.Parameters.Add("@ProductDesc", System.Data.SqlDbType.NVarChar, 200);
                  insertData.Parameters["@ProductDesc"].Value = pdesc.Text;
  
                  insertData.Parameters.Add("@CategoryID", System.Data.SqlDbType.Int);
                  insertData.Parameters["@CategoryID"].Value = pcategory.SelectedIndex;
  
                  insertData.Parameters.Add("@SubCatID", System.Data.SqlDbType.Int);
                  insertData.Parameters["@SubCatID"].Value = psubcategory.SelectedIndex;
  
                  insertData.Parameters.Add("@UserID", System.Data.SqlDbType.Int);
                  insertData.Parameters["@UserID"].Value = user.SelectedIndex;
  
                  insertData.Parameters.Add("@ReservePrice", System.Data.SqlDbType.Money);
                  insertData.Parameters["@ReservePrice"].Value = Convert.ToDecimal(reserveprice.Text);
  
                  insertData.Parameters.Add("@AuctionLength", System.Data.SqlDbType.Int);
                  insertData.Parameters["@AuctionLength"].Value = Convert.ToInt32(auctionlength.Text);
  
                  try
                  {
                      connection.Open();
                      insertData.ExecuteNonQuery();
                      Response.Redirect("Categories.aspx");
                  }
                  catch (Exception error)
                  {
                      dberror.Text = error.ToString();
                  }
                  finally
                  {
                      connection.Close();
                  }
              }
          }
      }
  }

  sqldatareader

  • Moved by mbanavige on May 05, 2012 10:06 PM
• • Reply
  

  Ken Tucker

  All-Star

  16797 Points

  2608 Posts

  MVP

  Re: Not getting all results from SqlDataReader

  May 05, 2012 02:55 PM|LINK

  Why are you calling reader.Read() before you bind?  You should call ExecuteReader and then bind to the reader.

  sqldatareader

  Developer at Sea World
  Space Coast .Net User Group
• • Reply
  

  sab0tr0n

  Member

  2 Points

  5 Posts

  Re: Not getting all results from SqlDataReader

  May 05, 2012 03:20 PM|LINK

  Well, that might be a very good question.  You see, I'm taking a asp.net class and to be honest, i'm still learning the ins and outs of this.  Do you think you could show me an example of what should be done using a segment of my code?  I've tried moving it around myself aready and got some invalid results.

  Thank you for the reply Ken!
  

  Ken Tucker

  Why are you calling reader.Read() before you bind?  You should call ExecuteReader and then bind to the reader.

• • Reply
  

  mbanavige

  All-Star

  134963 Points

  15421 Posts

  ASPInsiders

  Moderator

  MVP

  Re: Not getting all results from SqlDataReader

  May 05, 2012 10:05 PM|LINK

  you'd want to remove the while reader.Read() bit entirely.  The databinding operation is what's going to read through the reader.  If you call read() yourself and then dont do anything with the data that was read, you're essentially throwing it away.

  i think you'd end up with code something like this:

  connection.Open();
              reader = populateList.ExecuteReader();
  
              pcategory.DataSource = reader;
              pcategory.DataValueField = "Category";
              pcategory.DataBind();
           
              reader.NextResult();
           
              psubcategory.DataSource = reader;
              psubcategory.DataValueField = "SubCategory";
              psubcategory.DataBind();
              
              reader.NextResult();
              
              user.DataSource = reader;
              user.DataValueField = "LName";
              user.DataBind();
              
              reader.Close();

  
  
  

  Mike Banavige
  • Marked as answer by sab0tr0n on May 06, 2012 01:37 AM
• • Reply
  

  sab0tr0n

  Member

  2 Points

  5 Posts

  Re: Not getting all results from SqlDataReader

  May 06, 2012 01:36 AM|LINK

  Mike,

  Thank you for replying.  I tried your suggestion and sure enough, that was it!  I really appreciate it.

  Thanks again!

  mbanavige

  you'd want to remove the while reader.Read() bit entirely.  The databinding operation is what's going to read through the reader.  If you call read() yourself and then dont do anything with the data that was read, you're essentially throwing it away.

  i think you'd end up with code something like this:

  connection.Open();
              reader = populateList.ExecuteReader();
   
              pcategory.DataSource = reader;
              pcategory.DataValueField = "Category";
              pcategory.DataBind();
            
              reader.NextResult();
            
              psubcategory.DataSource = reader;
              psubcategory.DataValueField = "SubCategory";
              psubcategory.DataBind();
               
              reader.NextResult();
               
              user.DataSource = reader;
              user.DataValueField = "LName";
              user.DataBind();
               
              reader.Close();