Link to home
Start Free TrialLog in
Avatar of Stefan Motz
Stefan MotzFlag for United States of America

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Big Monty
Big Monty
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Stefan Motz

ASKER

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 )
^
the function can go anywhere in your code.

replace

<%=rs("DestQueueId")%>

with

<%=getNameFromID( rs("DestQueueId") )%>
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 )
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
--------------^
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great, now it's perfect, thank you very much!