DropDownList not populating with top row of data

  • Thread starter Thread starter Assimalyst
  • Start date Start date
A

Assimalyst

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
 
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.
 
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.
 
Back
Top