How to populate drop-down from the stored procedure results

[Ankor]

Hello. I am very new to .NET programming, so my question is probably easy to answer. Or, maybe someone can direct me to the web source where I will be able to find the solution. Anyway, I need to populate a drop-down list with the results from the stored procedure. I was able to write a partially working code, but still have two problems. The first one is that my procedure returns multiple record sets, and I need to have some kind of a loop to append all of them to each other and load all information into the drop-down. Something like this code in ASP:

Do Until rsQuery is Nothing
RunProcedure = RunProcedure & rsQuery.GetString()
Set rsQuery = rsQuery.Nextrecordset
Loop

The second problem is that if I load the first record set and test the page, the value selected in this drop-down will not be recognized by another stored procedure where I pass it as a parameter. Here is the code I have for the Page_Load event. I believe my problem is somewhere in the highlighted part, but not sure where.

Try
'Refer to the connection in web.config
strConnection = ConfigurationSettings.AppSettings("WebConfConn")
'Connect to the database
sqlConn = New SqlConnection(strConnection)
'Create a command string
strCmd = "DB_SP_DisplayNames"
'Create a command
sqlCmd = New SqlCommand(strCmd, sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure
'Open Connection
sqlConn.Open()
'Set Data Reader
sqlReader = sqlCmd.ExecuteReader()

'Get data from the Data Reader and load it to the drop-down list
ddlName.DataSource = sqlReader
ddlName.DataTextField = "sName"
ddlName.DataValueField = "sName"
ddlName.DataBind()

Catch ex As Exception
Response.Write(ex.ToString & "<br>")
Finally
sqlReader.Close()
sqlConn.Close()
End Try

Thank you in advance for all your help.

 

[dvannoy]

heres a simple example

Dim cn As SqlConnection = New SqlConnection

Dim cmd As New SqlCommand("YourSPName", YourConnection)

cmd.CommandType = CommandType.StoredProcedure

YourConnection.Open()

YourDropDown.DataSource = cmd.ExecuteReader

YourDropDown.DataTextField = "YourValue"
YourDropDown.DataValueField = "YourValue"
YourDropDown.DataBind()

YourConnection.Close()

 

[Ankor]

I believe this is the same code I put into my first message. My problem is that the stored procedure returns multiple record sets, and this example will populate only the first one.

 

[jbenson001]

What is the code for the SP? How can a SP return multiple record sets?

Jim

 

[dvannoy]

are you trying to combine two SP's into a dropdown?

post your SP

 

[Ankor]

An SP with coursors can return multiple record sets. It will display a separate set of data for each value in the fetch.

 

[Ankor]

Here is a stored procedure that can return multiple record sets. It is not an actual procedure, it's a sample, and I wonder if all values from it can be populated into the drop-down list:

CREATE PROCEDURE [dbo].[DB_SP_DisplayNames]

AS

SET NOCOUNT ON

DECLARE @sTitle CHAR(3)

DECLARE T_CURSOR CURSOR FOR
SELECT DISTINCT SpTitle
FROM NameTable ORDER BY 1

OPEN T_CURSOR
FETCH NEXT FROM T_CURSOR INTO @sTitle
WHILE @@FETCH_STATUS=0
BEGIN
SELECT DISTINCT EmpName
FROM NameTable WHERE SpTitle = @sTitle ORDER BY 1
FETCH NEXT FROM T_CURSOR INTO @sTitle
END
CLOSE T_CURSOR
DEALLOCATE T_CURSOR

SET NOCOUNT OFF
GO

 

[jbenson001]

Why don't you select your final results into a temp table, and return that to your page?

 

[Ankor]

Well, I can rewrite the stored procedures, or use temporary tables, but before I will be looking for the work around in the database, I'd like to check if the same thing is doable in .NET. It was possible to loop through record sets in ASP, so I think .NET should have this same capability too.

 

[Ankor]

In case if someone else needs it, here is how it works. The code in my first example:

ddlName.DataSource = sqlReader
ddlName.DataTextField = "sName"
ddlName.DataValueField = "sName"
ddlName.DataBind()

should be replaced with the following code:

Do
While sqlReader.Read
ddlName.Items.Add(sqlReader("sName"))
End While
Loop While sqlReader.NextResult()