Populating dropdown list based on session variable?

[elleme]

I have a dropdown list that I would like to populate based on values in a SQL database. The database has a table of addresses for families. Some families have more than one address, and have different family members at each address. The purpose of this dropdown list is to assign each family member to whatever address. I would like to limit the list items to the number of addresses. So if a family has address 1, 2, 3, then 1,2,3 are displayed, but if there is only 1, then only 1 is displayed.

I created a dynamic list box that selected these values from a recordset called from a SQL stored procedure that restricted the list based on the subject ID, which is also used as a session variable on the web page.

However, when I load the page, I get an error message, "Procedure 'pr_AvailableContactID' expects parameter '@subID', which was not supplied", even though I have supplied this ID as the session variable.

Should I try to restrict based on subject ID in ASP instead of in the SQL code? How can I limit the list?

Here is some of the code:
SQL:
ALTER PROC dbo.pr_AvailableContactID(
@idDCC int
)
AS

SELECT idDCC, c.FamilyID, ContactID
FROM tblContact c
INNER JOIN tblScreenedElig se
ON c.FamilyID = se.FamilyID

WHERE idDCC=@idDCC

ASP:

<%

set AvailableContactID = Server.CreateObject("ADODB.Command")
AvailableContactID.ActiveConnection = MM_Teddy_STRING
AvailableContactID.CommandText = "dbo.pr_AvailableContactID"
AvailableContactID.Parameters.Append AvailableContactID.CreateParameter("@RETURN_VALUE", 3, 4)
AvailableContactID.CommandType = 4
AvailableContactID.CommandTimeout = 0
AvailableContactID.Prepared = true
set ContactIDAvailable = AvailableContactID.Execute
ContactIDAvailable_numRows = 0

%>

<select name="MomContactID" id="MomContactID">
<%
While (NOT ContactIDAvailable.EOF)
%>
<option value="<%=(ContactIDAvailable.Fields.Item("ContactID").Value)%>" <%If (Not isNull(SibParView.Fields.Item("MomContactID").Value)) Then If (CStr(ContactIDAvailable.Fields.Item("ContactID").Value) = CStr(SibParView.Fields.Item("MomContactID").Value)) Then Response.Write("SELECTED") : Response.Write("")%> ><%=(ContactIDAvailable.Fields.Item("ContactID").Value)%></option>
<%
ContactIDAvailable.MoveNext()
Wend
If (ContactIDAvailable.CursorType > 0) Then
ContactIDAvailable.MoveFirst
Else
ContactIDAvailable.Requery
End If
%>

thanks!

 

[mbiro]

I do not see where your stored procedure is looking for @subID nor do I see where you are passing @subID to the procedure. Can you provide that code? It is good practice to only ask the database for the records you need so I would not have the asp filter.

 

[monksnake]

SQL:
ALTER  PROC dbo.pr_AvailableContactID(
    @idDCC int
    )
AS

SELECT idDCC, c.FamilyID, ContactID
FROM tblContact c
    INNER JOIN tblScreenedElig se
        ON c.FamilyID = se.FamilyID

WHERE idDCC=@idDCC

Why do you need to ALTER this stored procedure every time it's called?

AvailableContactID.CommandText = "dbo.pr_AvailableContactID"

Here, as mbiro pointed out, you are not passing anything as a parameter to this SP. To do that add a space and the variable name you need to pass

AvailableContactID.CommandText = "dbo.pr_AvailableContactID " [!]& variableName[/!]

<.

 

[Tarwn]

Or add it as a parameter the way your handling the return value.

Best MS KB Ever:

http://support.microsoft.com/kb/555375