How to append a row to an existing drop down list so the user sees None and the selected value is 0 as the first choice.

Greetings experts.

I am loading a drop down list using the following vb.net procedure. My question is how do I add an entry to the ddlHourDrop below so that the first choice in the list is None in the datatextfield property and 0 in the datavaluefield property without None and 0 actually being a row in the source table  PS_Hours (see stored procedure below).

Thanks,

Wayne

   Private Sub HourAdd_ddl()
        ' This code loads the hour drop-down list used when adding a time record
        Dim cmdSelect As SqlCommand
        Dim dtrHour As SqlDataReader
        cmdSelect = New SqlCommand("PS_HourDrop", conPS)
        cmdSelect.CommandType = CommandType.StoredProcedure
        conPS.Open()
        dtrHour = cmdSelect.ExecuteReader()
        ddlHourDrop.DataSource = dtrHour
        ddlHourDrop.DataTextField = "HourDesc"
        ddlHourDrop.DataValueField = "Hourid"
        ddlHourDrop.DataBind()
        conPS.Close()
    End Sub

The following is the stored procedure that loads the ddl.

CREATE PROCEDURE PS_HourDrop
AS
SELECT HourDesc, Hourid
From PS_Hours
ORDER BY Hourid
GO
WReadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

iboutchkineCommented:
before populating the drop down add an empty line
DropDownList1.Items.Add("")

after that populate from your SP

and then DropDown1.SelectedIndex = 0
0
WReadAuthor Commented:
Thanks for the suggestion above. However, when I add the following line of code you suggested
' DropDownList1.Items.Add("") ' before I populate the ddl with my SP, the new item is not inserted into the ddl.
I tried doing what you suggested without calling the SP and the new blank row does in fact get inserted into the ddl. I still have the original problem which is I can't populate the ddl manually(as you suggested) AND from the SP. It is as if the SP is replacing/overriding the added item. Is this possible? Recall the following line in my procedure above
ddlHourDrop.DataSource = dtrHour. I think this line says that the datasource for my ddl is the datareader named dtrHour.
Does this line overwrite your items add line of code?

Could you please be more specific with regard to exactly where I should insert these two lines of code with regard to my vb procedure above? FYI, Following your instructions I inserted  DropDownList1.Items.Add("") at the very top of my procedure named HourAdd_ddl(). Next I inserted DropDown1.SelectedIndex = 0 just before the folllowing databind
statement  ddlHourDrop.DataBind() that is just before the ConPS.close() statement. Thanks again for your suggestion.
Any additional ideas?



0
ihenryCommented:
After bind insert a new item to the dropdownlist at the first position

        ddlHourDrop.DataSource = dtrHour
        ddlHourDrop.DataTextField = "HourDesc"
        ddlHourDrop.DataValueField = "Hourid"
        ddlHourDrop.DataBind()
        ddlHourDrop.Insert( 0, "None" )
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

ptakjaCommented:
How about setting the SelectedItem value to -1 after data binding?
0
dsaboCommented:
I'll recommend to add new row with the parameters that you want to show, to the table returning form the sp, and then databind.....

example

DataTable tab1 = DataLayer.GetTable();  // i'm guessing that gettable returns a table based on a sp.
DataRow r1 = tab1.NewRow();
r1["ID"] = 0;
r1["name"] = "None";      // I'm guessing that the table has a columnd ID and a column None, and one is the Display Value  
                                     //and the other is the Value Member
tab1.Rows.Add(r1);

comboBox.DataSource = tab1;
comboBox.DisplayMember = "name";
comboBox.ValueMember = "ID";

comboBox.SelectedValue = 0;
hope this helps...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ihenryCommented:
Sorry I didn't read the q carefully, to make the text to "none" and value to 0

        ddlHourDrop.DataSource = dtrHour
        ddlHourDrop.DataTextField = "HourDesc"
        ddlHourDrop.DataValueField = "Hourid"
        ddlHourDrop.DataBind()
        ddlHourDrop.Insert( 0, New ListItem("None", "0") )
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.