DropDownList not populating with top row of data

[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

[Brad Roberts]

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

[Brad Roberts]

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

[Assimalyst]

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.