Populate 2 DDL's with SQL Database Names
-
When I run this code, only one DDL (cboSDB) populates, the other remains empty. Code does not trip any errors. Am I missing something obvious? Thanks ****************** Using conn As New SqlConnection(connect) Try conn.Open() ' get DataTable with all available metadata collections Dim dt1 As DataTable = conn.GetSchema() Dim cmd As SqlCommand = New SqlCommand Dim dr As SqlDataReader cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "sp_databases" cmd.Connection = conn dr = cmd.ExecuteReader cboSDB.Items.Clear() cboSDB.Items.Add(New ListItem("Select", "0")) cboDDB.Items.Clear() cboDDB.Items.Add(New ListItem("Select", "0")) ' bind DataTable controls to display rows cboSDB.DataSource = dr cboSDB.DataTextField = "DATABASE_NAME" cboSDB.DataValueField = "DATABASE_NAME" cboSDB.DataBind() ' This is where it won't work yet code is exactly the same as above. cboDDB.DataSource = dr cboDDB.DataTextField = "DATABASE_NAME" cboDDB.DataValueField = "DATABASE_NAME" cboDDB.DataBind() ' End cmdSConn.Enabled = False Catch ex As System.Exception clsE.logWizError(ex.Message.ToString, "COULD NOT CONNECT TO SOURCE.", sClient) lblMsg.Text = "COULD NOT CONNECT TO DATABASE. PLEASE TRY AGAIN." lblMsg.ForeColor = Drawing.Color.DarkRed lblMsg.Visible = True Finally conn.Close() End Try End Using
-
When I run this code, only one DDL (cboSDB) populates, the other remains empty. Code does not trip any errors. Am I missing something obvious? Thanks ****************** Using conn As New SqlConnection(connect) Try conn.Open() ' get DataTable with all available metadata collections Dim dt1 As DataTable = conn.GetSchema() Dim cmd As SqlCommand = New SqlCommand Dim dr As SqlDataReader cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "sp_databases" cmd.Connection = conn dr = cmd.ExecuteReader cboSDB.Items.Clear() cboSDB.Items.Add(New ListItem("Select", "0")) cboDDB.Items.Clear() cboDDB.Items.Add(New ListItem("Select", "0")) ' bind DataTable controls to display rows cboSDB.DataSource = dr cboSDB.DataTextField = "DATABASE_NAME" cboSDB.DataValueField = "DATABASE_NAME" cboSDB.DataBind() ' This is where it won't work yet code is exactly the same as above. cboDDB.DataSource = dr cboDDB.DataTextField = "DATABASE_NAME" cboDDB.DataValueField = "DATABASE_NAME" cboDDB.DataBind() ' End cmdSConn.Enabled = False Catch ex As System.Exception clsE.logWizError(ex.Message.ToString, "COULD NOT CONNECT TO SOURCE.", sClient) lblMsg.Text = "COULD NOT CONNECT TO DATABASE. PLEASE TRY AGAIN." lblMsg.ForeColor = Drawing.Color.DarkRed lblMsg.Visible = True Finally conn.Close() End Try End Using
You don't do databinding like this using a DataReader. A DataReader reads through the returned records forward only. Since during the first bind, you had the reader go through all the records once, there's no way to get it to go back and do it again for the second bind. Drop the DataReader and use a DataAdapter to fill a DataTable, then you can bind both ComboBox's (please don't use abbreviations like DDL!) to that DataTable.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007