Classic ASP - Retrieve column from SQL Server Database

Hi Experts,
I'm updating a field in my table with a dropdown, which is populated from a different table:
This is the dropdown:

<select name="DestQueueId">
<%
Dim rsName
set rsName=Server.CreateObject("ADODB.recordset")
sql="SELECT distinct(Name), QueueEnvironmentId FROM QueueEnvironment order by Name"
Set rsName = AdoCon.Execute(sql)
%>
<option value="">Change Environment:</option>
<%
Do While Not rsName.EOF
Response.Write "<option value='" & rsName("QueueEnvironmentId") & "'>"& rsName("Name") &"</option>"
rsName.MoveNext
Loop
Response.Write "</select>"
%>
<input type="Submit" name="Submit" value="Submit" />

Open in new window


After I submit, the DestQueueId column is updated in my table with the value of QueueEnvironmentId.
QueueEnvironmentId is an integer.
Name is a string.
When I view  <%=rs("DestQueueId")%> on my updated page, the integer is displayed.
How could I have the Name string displayed instead?

I would appreciate your help.
romsomAsked:
Who is Participating?
 
Big MontyConnect With a Mentor Senior Web Developer / CEO of ExchangeTree.org Commented:
here's a function you can call to get the NAME for a given ID:
<%
Response.Write getNameFromID( rs("DestQueueId") )

function getNameFromID( id )
   Dim rsName, results
   set rsName=Server.CreateObject("ADODB.recordset")
   sql="SELECT [Name] FROM QueueEnvironment where DestQueueId = " & id
   Set rsName = AdoCon.Execute(sql)

   if not rsName .BOF and not rsName .EOF then 
       results = rsName("Name")
    end if

    getNameFromID = results
end function
%>

Open in new window

0
 
romsomAuthor Commented:
Where do I have to place this code? Shall I replace <%=rs("DestQueueId")%> ?
Now it gives me an error message:
Microsoft VBScript compilation error '800a03ea'

Syntax error

/RecallAffiliation.asp, line 208

function getNameFromID( id )
^
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
the function can go anywhere in your code.

replace

<%=rs("DestQueueId")%>

with

<%=getNameFromID( rs("DestQueueId") )%>
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
romsomAuthor Commented:
I replaced <%=rs("DestQueueId")%> with <%=getNameFromID( rs("DestQueueId") )%>
but no matter where put the function on my page it still gives me the syntax error message at the this line:
function getNameFromID( id )
0
 
romsomAuthor Commented:
This is what my page looks like now:
<%
Response.Write getNameFromID( rs("DestQueueId") )

function getNameFromID( id )
   Dim rsName, results
   set rsName=Server.CreateObject("ADODB.recordset")
   sql="SELECT [Name] FROM QueueEnvironment where QueueEnvironmentId = " & id
   Set rsName = AdoCon.Execute(sql)

   if not rsName .BOF and not rsName .EOF then 
       results = rsName("Name")
    end if

    getNameFromID = results
end function
%>

<table>
<tr>
<td>Alias Name</td>
<td>Host Name</b></td>
<td">Env</td>
</tr>
<%
rs.AbsolutePage = intPage
For intRecord = 1 To rs.PageSize
%>
<tr>
<td><%=rs("AliasName")%></td>
<td><%=rs("HostName")%></td>
<td><%=getNameFromID( rs("DestQueueId") )%></td>
</tr>
<%
	rs.MoveNext
If rs.EOF Then Exit For
Next
rs.Close
set rs = Nothing

%>
</table>

Open in new window


The error I get now is as follows:
Microsoft VBScript compilation error '800a03f9'

Expected 'Then'

/test.asp, line 56

if not rsName .BOF and not rsName .EOF then
--------------^
0
 
Big MontyConnect With a Mentor Senior Web Developer / CEO of ExchangeTree.org Commented:
change that line to

if not rsName.BOF and not rsName.EOF then
0
 
romsomAuthor Commented:
Great, now it's perfect, thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.