populate dropdownlist off stored precedure

[BobotheBugbear]

I have a dropdownlist that I need to populate from a DB. The general idea of populating a dropdownlist from a DB part is easy. The part that I am having problems with is that I need to pass a value to a stored procedure and based upon the results of the query, fill the dropdownlist.

Below is the general code but it doesnt work as it wont pass the value to the stored procedure as the code structure is not correct.

Dim strConn As String = ConfigurationSettings.AppSettings(Convert.ToString("wellsourceOSLA"))
Dim objDS As New DataSet()
Dim objConn As New SqlClient.SqlConnection()
Dim objCommand As SqlClient.SqlCommand
Dim objParam As SqlClient.SqlParameter
Dim strProc As String = "sp_GetSSOClasses"

'sso id
objParam = objCommand.Parameters.Add("@intClass_k", SqlDbType.Int)
objParam.Value = Convert.ToInt32(intSSO_k)

objCommand = New SqlClient.SqlCommand(strProc, objConn)
objCommand.CommandText = strProc
objCommand.CommandType = CommandType.StoredProcedure


ddlTermTypes.DataSource = objCommand.ExecuteNonQuery()
ddlTermTypes.DataTextField() = "strClass"
ddlTermTypes.DataValueField = "intClass_k"
ddlTermTypes.DataBind()
objDS = Nothing

Anyone have a solution, an example or an idea?

thanks

[A1ien51]

Okay, I wrote this off the top of my head so hopefully it runs:


Dim theCommand As SqlCommand
theCommand = New SqlCommand("spName")
theCommand.CommandType = CommandType.StoredProcedure
theCommand.Parameters.Add("@XXX", "XXX"))
theCommand.Connection = "YourConnectionString"
theCommand.Connection.Open()
theCommand.ExecuteNonQuery()
theCommand.Connection.Close()
Dim da As SqlDataAdapter = New SqlDataAdapter
da.SelectCommand = theCommand
Dim ds As DataSet = New DataSet
da.Fill(ds, "theOptions")

ddl.DataSource = ds.Tables("theOptions")
ddl.DataTextField = "XXX"
ddl.DataValueField = "XXX"
ddl.DataBind()


Eric