Member Avatar for edmicman

I know this is probably a basic question, but I'm stuck and can't seem to figure it out :-/

This is in VB.NET 2005 and I'm making a windows forms app. I'm filling a DataTable from a SQL command, and then binding a dropdownlist to that DataTable. I'm assigning the DisplayMember and ValueMember just fine, and the dropdownlist is working fine. But what I want to do is after binding it, add a "blank" choice (or some other text like "please choose something" to the first slot, and default to that being selected when the app runs. I know it's simple, I'm just blanking out here. Thanks!

Here's the code for what I'm doing:

Private Sub BindAccounts()
        Dim conn As New SqlConnection()
        Dim myCommand As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim myData As New DataTable

        Dim SQL As String
        Dim sSQL As String

        SQL = "server=" & My.Settings.DataSource & ";" _
         & "user id=" & Login.txtUserName.Text & ";" _
         & "password=" & Login.txtPassword.Text & ";" _
         & "database=" & My.Settings.InitialCatalog

        conn.ConnectionString = SQL
        sSQL = "select distinct lngAccountID_pk, strAccountName"
        sSQL = sSQL & " from tblAccounts"
        sSQL = sSQL & " where lngAccountID_pk in (select accountid from tblstaging)"
        sSQL = sSQL & " order by strAccountName"

        Try
            conn.Open()
            Try

                myCommand.Connection = conn
                myCommand.CommandText = sSQL
                myCommand.CommandType = CommandType.Text

                da.SelectCommand = myCommand
                da.Fill(myData)

                ddlAccount.DataSource = myData
                ddlAccount.DisplayMember = "strAccountName"
                ddlAccount.ValueMember = "lngAccountID_pk"

            Catch myerror As SqlException
                MsgBox("There was an error reading from the database: " 
& myerror.Message)

            End Try

            ddlAccount.SelectedIndex = 0

            conn.Close()

        Catch myerror As SqlException
            MessageBox.Show("Error Connecting to Database: " &
myerror.Message)
            conn.Dispose()
        End Try
    End Sub
  • 1 Contributor
  • 1 Reply
  • 216 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by edmicman

Dani AI

Generated

As started to uncover, the ASP.NET ListItem trick is a different beast from WinForms: when a ComboBox/DropDownList is bound via DataSource, the control ignores the Items collection. The cleanest, most reliable fix is to add a placeholder row to the bound DataTable before binding, and to suppress selection-change handling while the initial bind runs so the datagrid query does not fire prematurely.

' add a placeholder row at the top of the DataTable before binding
Dim blank As DataRow = myData.NewRow()
blank("lngAccountID_pk") = DBNull.Value   ' or -1 if the code expects an integer
blank("strAccountName") = "Please select..."
myData.Rows.InsertAt(blank, 0)

' then bind
ddlAccount.DataSource = myData
ddlAccount.DisplayMember = "strAccountName"
ddlAccount.ValueMember = "lngAccountID_pk"
ddlAccount.SelectedIndex = 0

To prevent the selection-change event from executing during bind, unsubscribe before bind and re-subscribe after, or use a simple boolean flag that the handler checks.

RemoveHandler ddlAccount.SelectedIndexChanged, AddressOf ddlAccount_SelectedIndexChanged
' perform bind
AddHandler ddlAccount.SelectedIndexChanged, AddressOf ddlAccount_SelectedIndexChanged

Also make the grid-binding handler defensive so it ignores the placeholder:

If ddlAccount.SelectedValue Is Nothing OrElse IsDBNull(ddlAccount.SelectedValue) Then Exit Sub
' safe to run grid query

Notes and caveats: inserting the row should happen before setting DataSource; if the PK column is strongly typed integer, using DBNull.Value may require guarding when reading SelectedValue (or use a sentinel like -1). An alternative is to use a BindingSource and AddNew() to create the placeholder. Using Form.Shown for post-load work also avoids some Load-time event quirks.

Member Avatar for edmicman

Derrrr, I found some old C# code I used in another project, I'm hoping to try it tonight after work. I think this might work?

// Add blank option
ddlDepartment.Items.Insert(0, new ListItem("", ""));

I think my problem was in the ListItem part - I'd forgotten about that.

On a related note (or maybe not), when I bind the account list it is firing the selection changed event and then trying to bind the datagrid (when I load the form), and is crapping out on that. Basically it's trying to do the SQL query before there is data for it, I think. Is there anything like a PostBack in forms programming? I don't want it to bind the datagrid on load....ah well, I'll figure it out!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.