DropDownList not populating with top row of data

Discussion in 'ASP .Net' started by Assimalyst, Aug 8, 2005.

  1. Assimalyst

    Assimalyst Guest

    Hi,

    I'm attempting to populate a dropdownlist with data from a datareader.
    It works OK except whatever data is extracted as the very top line
    (Index = 0) cannot be seen in the dropdownlist.

    Here is my code:

    string strEstNo = Session["establishment"].ToString();

    SqlCommand cmd = new SqlCommand("proc_GetSurgeonName", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@estNo", strEstNo));

    SqlDataReader dr = cmd.ExecuteReader();
    dr.Read();

    // bind the drop down
    surgeonNameCboBx.DataSource = dr;
    surgeonNameCboBx.DataTextField = "sgnFullName";
    surgeonNameCboBx.DataValueField = "surgeonNo";
    surgeonNameCboBx.DataBind();
    surgeonNameCboBx.SelectedIndex = 0;

    // close datareader
    dr.Close();


    And here's the stored procedure:

    CREATE PROCEDURE proc_GetSurgeonName
    (@estNo int
    )

    AS

    SELECT *, sgnTitle + ' ' + sgnFName + ' ' + sgnLName AS sgnFullName
    FROM tblSurgeon
    WHERE estNo = @estNo
    ORDER BY sgnFullName
    GO

    I have tested this in query analyser, and all expected rows are found.

    I noticed when i added in ORDER BY that a different row was missing
    than without it, i.e.

    List of ID & sgyFullName without ORDER BY sgyFullName shown in dropdown
    list:
    2 Dr Paul Jones
    3 Dr Andrew Thorpe

    List of ID & sgyFullName with ORDER BY sgyFullName shown in dropdown
    list:
    1 Dr Kevin Smith
    2 Dr Paul Jones

    List using query analyser without ORDER BY:
    1 Dr Kevin Smith
    2 Dr Paul Jones
    3 Dr Andrew Thorpe

    Anyone know why the top row isn't being read into the DropDownList?

    Thanks
    Assimalyst, Aug 8, 2005
    #1
    1. Advertising

  2. Not sure about the "order by stuff". However, I did notice that in the code
    you have a "dr.read()" before the databind(). Take out the "dr.Read()". This
    reads the first row. Then when the DataBind() is executed it does not get the
    first record. (It does a dr.Read() in a loop behind the scenes.

    "Assimalyst" wrote:

    > Hi,
    >
    > I'm attempting to populate a dropdownlist with data from a datareader.
    > It works OK except whatever data is extracted as the very top line
    > (Index = 0) cannot be seen in the dropdownlist.
    >
    > Here is my code:
    >
    > string strEstNo = Session["establishment"].ToString();
    >
    > SqlCommand cmd = new SqlCommand("proc_GetSurgeonName", conn);
    > cmd.CommandType = CommandType.StoredProcedure;
    >
    > cmd.Parameters.Add(new SqlParameter("@estNo", strEstNo));
    >
    > SqlDataReader dr = cmd.ExecuteReader();
    > dr.Read();
    >
    > // bind the drop down
    > surgeonNameCboBx.DataSource = dr;
    > surgeonNameCboBx.DataTextField = "sgnFullName";
    > surgeonNameCboBx.DataValueField = "surgeonNo";
    > surgeonNameCboBx.DataBind();
    > surgeonNameCboBx.SelectedIndex = 0;
    >
    > // close datareader
    > dr.Close();
    >
    >
    > And here's the stored procedure:
    >
    > CREATE PROCEDURE proc_GetSurgeonName
    > (@estNo int
    > )
    >
    > AS
    >
    > SELECT *, sgnTitle + ' ' + sgnFName + ' ' + sgnLName AS sgnFullName
    > FROM tblSurgeon
    > WHERE estNo = @estNo
    > ORDER BY sgnFullName
    > GO
    >
    > I have tested this in query analyser, and all expected rows are found.
    >
    > I noticed when i added in ORDER BY that a different row was missing
    > than without it, i.e.
    >
    > List of ID & sgyFullName without ORDER BY sgyFullName shown in dropdown
    > list:
    > 2 Dr Paul Jones
    > 3 Dr Andrew Thorpe
    >
    > List of ID & sgyFullName with ORDER BY sgyFullName shown in dropdown
    > list:
    > 1 Dr Kevin Smith
    > 2 Dr Paul Jones
    >
    > List using query analyser without ORDER BY:
    > 1 Dr Kevin Smith
    > 2 Dr Paul Jones
    > 3 Dr Andrew Thorpe
    >
    > Anyone know why the top row isn't being read into the DropDownList?
    >
    > Thanks
    >
    >
    =?Utf-8?B?QnJhZCBSb2JlcnRz?=, Aug 8, 2005
    #2
    1. Advertising

  3. What happens when it fails? Is there a message? What is displayed in the
    browser?

    "Assimalyst" wrote:

    > Hi,
    >
    > I'm attempting to populate a dropdownlist with data from a datareader.
    > It works OK except whatever data is extracted as the very top line
    > (Index = 0) cannot be seen in the dropdownlist.
    >
    > Here is my code:
    >
    > string strEstNo = Session["establishment"].ToString();
    >
    > SqlCommand cmd = new SqlCommand("proc_GetSurgeonName", conn);
    > cmd.CommandType = CommandType.StoredProcedure;
    >
    > cmd.Parameters.Add(new SqlParameter("@estNo", strEstNo));
    >
    > SqlDataReader dr = cmd.ExecuteReader();
    > dr.Read();
    >
    > // bind the drop down
    > surgeonNameCboBx.DataSource = dr;
    > surgeonNameCboBx.DataTextField = "sgnFullName";
    > surgeonNameCboBx.DataValueField = "surgeonNo";
    > surgeonNameCboBx.DataBind();
    > surgeonNameCboBx.SelectedIndex = 0;
    >
    > // close datareader
    > dr.Close();
    >
    >
    > And here's the stored procedure:
    >
    > CREATE PROCEDURE proc_GetSurgeonName
    > (@estNo int
    > )
    >
    > AS
    >
    > SELECT *, sgnTitle + ' ' + sgnFName + ' ' + sgnLName AS sgnFullName
    > FROM tblSurgeon
    > WHERE estNo = @estNo
    > ORDER BY sgnFullName
    > GO
    >
    > I have tested this in query analyser, and all expected rows are found.
    >
    > I noticed when i added in ORDER BY that a different row was missing
    > than without it, i.e.
    >
    > List of ID & sgyFullName without ORDER BY sgyFullName shown in dropdown
    > list:
    > 2 Dr Paul Jones
    > 3 Dr Andrew Thorpe
    >
    > List of ID & sgyFullName with ORDER BY sgyFullName shown in dropdown
    > list:
    > 1 Dr Kevin Smith
    > 2 Dr Paul Jones
    >
    > List using query analyser without ORDER BY:
    > 1 Dr Kevin Smith
    > 2 Dr Paul Jones
    > 3 Dr Andrew Thorpe
    >
    > Anyone know why the top row isn't being read into the DropDownList?
    >
    > Thanks
    >
    >
    =?Utf-8?B?QnJhZCBSb2JlcnRz?=, Aug 8, 2005
    #3
  4. Assimalyst

    Assimalyst Guest

    Thanks Brad, That was what was strange there were no errors etc. just
    the missing row.

    Removing the dr.Read() line seems to have fixed that though.

    Thanks again.
    Assimalyst, Aug 8, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?UmljaGFyZEg=?=

    Checkbox and dropdownlist row in repeater row

    =?Utf-8?B?UmljaGFyZEg=?=, Mar 23, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    6,375
    =?Utf-8?B?UmljaGFyZEg=?=
    Mar 24, 2006
  2. Frederic HOUDE
    Replies:
    4
    Views:
    594
    Frederic HOUDE
    Aug 31, 2004
  3. Dhananjay
    Replies:
    1
    Views:
    657
    Mike Placentra II
    Dec 23, 2007
  4. D
    Replies:
    0
    Views:
    206
  5. Dhananjay
    Replies:
    0
    Views:
    107
    Dhananjay
    Dec 18, 2007
Loading...

Share This Page