Build 10 dropdownlists from DataSet? 1

[lanm]

I need to populate 10 dropdown lists with the same SQL query. I've already tried to build three w/in the same routine, but only the first ddl is getting built.

Any suggestions are welcome!

Thanks!

 

[Veep]

Stored Procedure:

Create Procedure dbo.GetStuff
AS
SELECT * FROM tbl_1
SELECT * FROM tbl_2
SELECT * FROM tbl_3
GO

Web Page Pseudocode:

// create connection, connection string etc.
// create command object
// create SqlDataAdapter
// fill the DataSet
// Load the first dropdown with ds.Tables[0].DefaultView as the DataSource
// set the DataValueField and DataTextField
// DataBind()
// Load the second dropdown with ds.Tables[1].DefaultView as the DataSource
// set the DataValueField and DataTextField
// DataBind()
// etc...

You also might want to add this DataSet to the cache if your users are going to be hitting it often.

 

[jbenson001]

All you need to do is to run your sql query and put the results into a dataset or datatable. Then, bind each dropdownlist (datavaluefield, datatextfield) to the columns in the dataset.

If this does not help, let me know. It would be best if you posted your code so we can see what it is you are doing, and what you are trying to do.

Jim

 

[lanm]

You're right, I should have posted the code. Here it is!

I was using:

Public Sub Build_KSddlNewName()
'Make SQL Statement to populate New Name ddl
Dim strSQLNewName As String
strSQLNewName = "SELECT Authority, Number, Signature,

, Authority + ' | ' + " 
strSQLNewName &= "Number AS Expr4 FROM Sig_Auth_Nms " 
strSQLNewName &= "WHERE [Code]<>'' " 
strSQLNewName &= "Order By Authority" 
Dim sCon1 As New SqlConnection 
sCon1.ConnectionString = Session("DBDDL") 
sCon1.Open() 
Dim cmdRes As New SqlCommand(strSQLNewName, sCon1) 

Dim myNewNameRDR As SqlDataReader = cmdRes.ExecuteReader() 
If myNewNameRDR.HasRows = True Then 
KSddlNewName.DataSource = myNewNameRDR 
KSddlNewName.DataTextField = "Expr4" 
KSddlNewName.DataValueField = "Number" 
KSddlNewName.DataBind() 
KSddlNewName.Items.Insert(0, "") 
ksddlNewName2.DataSource = myNewNameRDR 
ksddlNewName2.DataTextField = "Expr4" 
ksddlNewName2.DataValueField = "Number" 
ksddlNewName2.DataBind() 
ksddlNewName2.Items.Insert(0, "") 
ksddlNewname3.DataSource = myNewNameRDR 
ksddlNewname3.DataTextField = "Expr4" 
ksddlNewname3.DataValueField = "Number" 
ksddlNewname3.DataBind() 
ksddlNewname3.Items.Insert(0, "") 
End If 
myNewNameRDR.Close() 
myNewNameRDR = Nothing 
sCon1.Close() 

Call buildKSddlNewP3ResourceCode() 

'Make SQL Statement to populate the third ddl, KSddlNewRateType 
Dim strSQLNewRateType As String 
strSQLNewRateType = "SELECT ComboCode + ' - ' + Description AS RtCdCnct, ComboCode FROM " 
strSQLNewRateType &= "[Resource Table - Combo Codes]" 

sCon1.Open() 
Dim cmdRateType As New SqlCommand(strSQLNewRateType, sCon1) 
Dim myNewRateTypeRDR As SqlDataReader = cmdRateType.ExecuteReader() 
If myNewRateTypeRDR.HasRows = True Then 
KSddlNewRateType.DataSource = myNewRateTypeRDR 
KSddlNewRateType.DataTextField = "RtCdCnct" 
KSddlNewRateType.DataValueField = "ComboCode" 
KSddlNewRateType.DataBind() 
KSddlNewRateType.Items.Insert(0, "") 
End If 
myNewRateTypeRDR.Close() 
myNewRateTypeRDR = Nothing 
sCon1.Close()


...but I'm going to try to use:
Dim cnn As New SqlConnection("data source = desktop; initial catalog= northwind; trusted_connection=true") 
cnn.Open() 
' 
Dim cmd As New SqlCommand("Data_Reader", cnn) 
cmd.CommandType = CommandType.StoredProcedure 


Dim dr As SqlDataReader 
dr = cmd.ExecuteReader 

With ddlLastName 
.DataSource = dr 
.DataTextField = "LastName" 
.DataValueField = "LastName" 
.DataBind() 
End With 

dr.NextResult() 
With ddlCompanyName 
.DataSource = dr 
.DataTextField = "CompanyName" 
.DataValueField = "CompanyName" 
.DataBind() 
End With 

cnn.Close() 
dr.Close()

 

[ca8msm]

You shouldn't use a DataReader if you are going to be binding a DDL to it's results as DataReaders are forward-only. You should (as Jim said) put the results into a DataTable then you can just do somthing like:

DropDownList1.DataSource = myDataTable
DropDownList1.DataBind

DropDownList2.DataSource = myDataTable
DropDownList2.DataBind

etc...

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[lanm]

Thanks ca8msm!

I ended up usin a DataSet for all 10 DDLs.

What I'd like to do is figure out how to cache those ddls or make this page faster / more efficient, since the user may add the 10 items, then need to add another 10. I've got to build two other ddls, which means the page will have a total of 30 ddls. I'll use a sproc too.

Stayed up till 12:30 last night try to add stuff in the code behind, but couldn't get it to work.

Thanks for the help! Any suggestions are welcome!

 

[ca8msm]

Something that may help with speed is by using a DataTable rather than a DataSet (as I can't see anywhere where you are actually using more than one DataTable in the DataSet).


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[lanm]

Great..thanks ca8msm! I'll try that and let you know.
Each of the three ddls will call different sprocs.