Start Free Trial

asked on

I need to make a page that uses one table in SQL to update another.

Hello,

I need to make an asp or aspx page that will have a dropdown box that contains an employee # from the employees (named Employee) table. When the user selects thier # number, the code then updates another table (in the same SQL database into a table named Register) The update should include several or all of the fields (i.e FirstName, LastName, EmployeeNumber, Email.) I need the code befor 5:00 . I am in a big crunch I will chack back often..

Please Help me out!

~David

ASKER

I have allready made all of the DB connections, I just need the code for the page.

Thanks Again People!

You guys are awesome programers.

ASKER

Also help full (but not required) is if the register table reaches a certain amount (say 100) user would geat a message stating that the class is full please choos another class. (the table would not get upgdated) and be redirected to a different page.

ASKER CERTIFIED SOLUTION

fritz_the_blank

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fritz_the_blank

HEre is the code for updateEmployee.asp

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> New Document </TITLE>

</HEAD>

<BODY>
<%
dim intEmployee
intEmployee = Request.Form("intEmployee")
if intEmployee <> "" then
      'open connecton
      objConnection.execute("INSERT INTO REGISTER (intEmployee) VALUES (" & intEmployee & ")"
else
      Response.write("No employee number indicated!")
end if
%>

</BODY>
</HTML>

fritz_the_blank

I am sure that you will need to have many modifications to this to make it do exactly what you want, but this should be a good start.

FtB

To use values from employee table do something like...
"insert into Register from select Employee where id=" & request.form("intEmployee")

But you really shouldn't. If you need details of an employee in register you should just join to the employee table.

ASKER

Can I include the join in the asp?

fritz_the_blank

Could you be more specific? Are you speaking about my code or joeposter649's? Also, all you really need is the employee number because once you have that in the register table, you can make joins as needed.

FtB

ASKER

Yes, but it has to run by itself. I won't be at the company much longer.

ASKER

This is the code frontpage creates to insert a database results object that just displays a drop down list of employye numbers. i am going to try out fritz the blanks code now. i am new at this so it may take me a bit of time.

<body>

<form>
      <nobr>
      
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Employee"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="TA-UPDATE"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="EmployeeNumber"
fp_sMenuValue="EmployeeNumber"
fp_sColTypes="&EmployeeNumber=202&FirstName=200&LastName=200&ThirdName=200&FourthName=200&Title=200&Address1=200&Address2=200&Town=200&Unit=200&Region=200&AreaCode=200&PhoneNumber=200&PhoneExtension=200&Email=200&Fax=200&GA=200&"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<select NAME="EmployeeNumber" SIZE="1">
      
      <option><%=FP_FieldHTML(fp_rs,"EmployeeNumber")%></option>
      
      </select></nobr></form>

</body>

</html>

fritz_the_blank

You have to do this in frontpage?

Ftb

ASKER

Fritz,

I modified your code to this:

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Register For Class</title>
</head>

<body>

<form action="updateEmployee.asp" name="frmEmployee" method="POST">
<select name="intEmployee">
<option></option>
<%
'create and open your connecton
dim objRS, s-dataconnection="TA-UPDATE"
set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT EmployeeNumber, strName FROM Employees ORDER BY EmployeeNumber"
objRS.Open strSQL,objConnecton,3,3
do while not objRS.eof
%>
<option value="<%=objRS("EmployeeNumber")%>"><%=objRS("strName")%></option>
<%
objRS.MoveNext()
loop
%>
</select><br>
<INPUT TYPE="submit" value="Submit">
</form>
</body>

</html>

I don't know what to put as strName

When I load the page I get this error:

The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot be displayed.

--------------------------------------------------------------------------------

Please try the following:

Click the Refresh button, or try again later.

Open the zeusdev home page, and then look for links to the information you want.
HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

--------------------------------------------------------------------------------

Technical Information (for support personnel)

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/cssd/prototype/TA/default.asp, line 15, column 12

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)

Page:
GET /cssd/prototype/TA/default.asp

Time:
Friday, August 06, 2004, 4:04:52 PM

More information:
Microsoft Support

ASKER

The site has frontpage extensions installed on it.

fritz_the_blank

Maybe like this? I really don't do FrontPage:

'create and open your connecton
dim objRS
set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT EmployeeNumber, strName FROM Employees ORDER BY EmployeeNumber"
objRS.Open strSQL,TA-UPDATE,3,3
do while not objRS.eof

or:

'create and open your connecton
dim objRS
set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT EmployeeNumber, strName FROM Employees ORDER BY EmployeeNumber"
objRS.Open strSQL, s-dataconnection,3,3
do while not objRS.eof

fritz_the_blank

Also,you need to use the real field names, not the ones that I put there as place holders...

FtB

ASKER

I modified your second page the way I think it may fit.

Also the front page db connection is called TA-UPDATE (if that helps)

the code for the second page is:

<%
dim EmployeeNumber
EmployeeNumber = Request.Form("EmployeeNumber")
if intEmployee <> "" then
'open connecton
objConnection.execute("INSERT INTO Register (EmployeeNumber) VALUES (" & EmployeeNumber & ")"
else
Response.write("No employee number indicated!")
end if
%>
artspan --></nobr></form>

Am I leaving any thing out?

fritz_the_blank

More like this, I guess:

<%
dim EmployeeNumber
EmployeeNumber = Request.Form("EmployeeNumber")
if intEmployee <> "" then
'open connecton
TA-UPDATE.execute("INSERT INTO Register (EmployeeNumber) VALUES (" & EmployeeNumber & ")"
else
Response.write("No employee number indicated!")
end if
%>

I imagine there has to be an include file somewhere so that you can get at the TA-UPDATE connection.

FtB

ASKER

<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
      '==FrontPage Generated - startspan==
      Dim FrontPage_UrlVars(7)
      '--Project Data Connection
            Application("IntranetLiasons_ConnectionString") = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/IntranetLiasons.mdb"
            FrontPage_UrlVars(0) = "IntranetLiasons_ConnectionString"
            Application("IntranetLiasons_ConnectionTimeout") = 15
            Application("IntranetLiasons_CommandTimeout") = 30
            Application("IntranetLiasons_CursorLocation") = 3
            Application("IntranetLiasons_RuntimeUserName") = ""
            Application("IntranetLiasons_RuntimePassword") = ""
      '--Project Data Connection
            Application("Links_ConnectionString") = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/links.mdb"
            FrontPage_UrlVars(1) = "Links_ConnectionString"
            Application("Links_ConnectionTimeout") = 15
            Application("Links_CommandTimeout") = 30
            Application("Links_CursorLocation") = 3
            Application("Links_RuntimeUserName") = ""
            Application("Links_RuntimePassword") = ""
      '--Project Data Connection
            Application("calendar_ConnectionString") = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=cal/calendar.mdb"
            FrontPage_UrlVars(2) = "calendar_ConnectionString"
            Application("calendar_ConnectionTimeout") = 15
            Application("calendar_CommandTimeout") = 30
            Application("calendar_CursorLocation") = 3
            Application("calendar_RuntimeUserName") = ""
            Application("calendar_RuntimePassword") = ""
      '--Project Data Connection
            Application("sql_ConnectionString") = "DRIVER={SQL Server};SERVER=15FF9099PC;DATABASE=CSSD;UID=sa;PWD=olot9864"
            Application("sql_ConnectionTimeout") = 15
            Application("sql_CommandTimeout") = 30
            Application("sql_CursorLocation") = 3
            Application("sql_RuntimeUserName") = "sa"
            Application("sql_RuntimePassword") = "olot9864"
      '--Project Data Connection
            Application("CSSDEmployee_ConnectionString") = "DRIVER={SQL Server};SERVER=15ff9099pc;DATABASE=cssd;UID=sa;PWD=olot9864"
            Application("CSSDEmployee_ConnectionTimeout") = 15
            Application("CSSDEmployee_CommandTimeout") = 30
            Application("CSSDEmployee_CursorLocation") = 3
            Application("CSSDEmployee_RuntimeUserName") = "sa"
            Application("CSSDEmployee_RuntimePassword") = "olot9864"
      '--Project Data Connection
            Application("collect_ConnectionString") = "DRIVER={SQL Server};SERVER=15ff9099pc;DATABASE=Collect;UID=sa;PWD=olot9864"
            Application("collect_ConnectionTimeout") = 15
            Application("collect_CommandTimeout") = 30
            Application("collect_CursorLocation") = 3
            Application("collect_RuntimeUserName") = "sa"
            Application("collect_RuntimePassword") = "olot9864"
      '--Project Data Connection
            Application("TA-UPDATE_ConnectionString") = "DRIVER={SQL Server};SERVER=15ff9099pc;DATABASE=CSSD;UID=sa;PWD=olot9864"
            Application("TA-UPDATE_ConnectionTimeout") = 15
            Application("TA-UPDATE_CommandTimeout") = 30
            Application("TA-UPDATE_CursorLocation") = 3
            Application("TA-UPDATE_RuntimeUserName") = "sa"
            Application("TA-UPDATE_RuntimePassword") = "olot9864"
      '--
      Application("FrontPage_UrlVars") = FrontPage_UrlVars
      '==FrontPage Generated - endspan==
End Sub
Sub Session_OnStart
      FrontPage_StartSession '==FrontPage Generated==
End Sub
Sub FrontPage_StartSession
      On Error Resume Next
      if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub

      sFile = "global.asa"
      sRootPath = Request.ServerVariables("APPL_PHYSICAL_PATH")
      if Left(sRootPath,1) = "/" then sSep = "/" else sSep = "\"
      if Right(sRootPath,1) <> sSep then sRootPath = sRootPath & sSep
      sRootPath = sRootPath & sFile

      ' discover the VRoot for the current page;
      ' walk back up VPath until we match VRoot
      Vroot = Request.ServerVariables("PATH_INFO")
      iCount = 0
      do while Len(Vroot) > 1
            idx = InStrRev(Vroot, "/")
            if idx > 0 then
                  Vroot = Left(Vroot,idx)
            else
                  ' error; assume root web
                  Vroot = "/"
            end if
            if Server.MapPath(Vroot & sFile) = sRootPath then exit do
            if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
            iCount = iCount + 1
            if iCount > 100 then
                  ' error; assume root web
                  Vroot = "/"
                  exit do
            end if
      loop
      ' map all URL= attributes in _ConnectionString variables
      Application.Lock
      if Len(Application("FrontPage_VRoot")) = 0 then
            Application("FrontPage_VRoot") = Vroot
            UrlVarArray = Application("FrontPage_UrlVars")
            for i = 0 to UBound(UrlVarArray)
                  if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
            next
      end if
      Application.Unlock
End Sub
Sub FrontPage_MapUrl(AppVarName)
      ' convert URL attribute in conn string to absolute file location
      strVal = Application(AppVarName)
      strKey = "URL="
      idxStart = InStr(strVal, strKey)
      If idxStart = 0 Then Exit Sub
      strBefore = Left(strVal, idxStart - 1)
      idxStart = idxStart + Len(strKey)
      idxEnd = InStr(idxStart, strVal, ";")
      If idxEnd = 0 Then
            strAfter = ""
            strURL = Mid(strVal, idxStart)
      Else
            strAfter = ";" & Mid(strVal, idxEnd + 1)
            strURL = Mid(strVal, idxStart, idxEnd - idxStart)
      End If
      strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") & strURL) & strAfter
      Application(AppVarName) = strOut
End Sub
</SCRIPT>
<head><title>Web Site Settings for Active Server Pages</title><html xmlns:mso="urn:schemas-microsoft-com:office:office" xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">

ASKER

Well if you need to do it without fp's autocode i need help with the ado connection. Lets start from the begining. The SQL server is 15ff9099pc the user id is sa the password is olot9864 ...

the database is named CSSD the employee table is employee with the important fields :

EmployeeNumber
FirstName
lastName
Email

the reg table is register with:

EmployeeNumber
FirstName
lastName
Email

Now how would I make these asp pages with this info forget the whole fp thing.

ASKER

OK,

I think I figured out how to make the connection. I have:

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Register For Class</title>
</head>

<body>

<form action="updateEmployee.asp" name="frmEmployee" method="POST">
<select name="intEmployee">
<option></option>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=15ff9099pc;UID=sa;PWD=olot9864;DATABASE=CSSD"
Conn.open DSNtest

'create and open your connecton
dim objRS, strSQL
set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT EmployeeNumber, strName FROM tblEmployees ORDER BY strName"
objRS.Open strSQL,objConnecton,3,3
do while not objRS.eof
%>
<option value="<%=objRS("intID")%>"><%=objRS("strName")%></option>
<%
objRS.MoveNext()
loop
%>
</select><br>
<INPUT TYPE="submit" value="Submit">
</form>
</body>

</html>

I am getting the error:

Technical Information (for support personnel)

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/cssd/prototype/TA/default.asp, line 22

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)

Page:
GET /cssd/prototype/TA/default.asp

What now?

ASKER

objRS.Open strSQL,objConnecton,3,3

ASKER

Now I have this:

<form action="updateEmployee.asp" name="frmEmployee" method="POST">
<select name="intEmployee">
<option></option>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=15ff9099pc;UID=sa;PWD=olot9864;DATABASE=CSSD"
Conn.open DSNtest

'create and open your connecton
dim objRS, strSQL
set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT EmployeeNumber, LastName FROM Employee ORDER BY LastName"
objRS.Open strSQL,objConnecton,3,3
do while not objRS.eof
%>
<option value="<%=objRS("EmployeeNumber")%>"><%=objRS("LastName")%></option>
<%
objRS.MoveNext()
loop
%>
</select><br>
<INPUT TYPE="submit" value="Submit">
</form>

I am getting an error on the line
objRS.Open strSQL,objConnecton,3,3

ASKER

OK fritz,

I got the first part (through trial and error):

<form action="updateEmployee.asp" name="frmEmployee" method="POST">
<select name="intEmployee">
<option></option>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=15ff9099pc;UID=sa;PWD=olot9864;DATABASE=CSSD"
Conn.open DSNtest
dim objRS, strSQL
set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT EmployeeNumber, LastName FROM Employee ORDER BY LastName"
objRS.Open strSQL,Conn,3,3
do while not objRS.eof
%>
<option value="<%=objRS("EmployeeNumber")%>"><%=objRS("LastName")%></option>
<%
objRS.MoveNext()
loop
%>
</select><br>
<INPUT TYPE="submit" value="Submit">
</form>

I just have to display the number instead of the nam ( I think I can figure it out) This is my first time not relying on fp to do it for me. the second part is giving me an error.

This is what I have for it so far:

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Update Employee</title>
</head>

<body>

<%
dim intEmployee
intEmployee = Request.Form("intEmployee")
if intEmployee <> "" then
'open connecton
Conn.execute("INSERT INTO REGISTER (intEmployee) VALUES (" & intEmployee & ")"
else
Response.write("No employee number indicated!")
end if
%>

</body>

</html>

ASKER

<body>

<%
dim intEmployee
intEmployee = Request.Form("intEmployee")
if intEmployee <> "" then
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=15ff9099pc;UID=sa;PWD=olot9864;DATABASE=CSSD"
Conn.open DSNtest
Conn.execute("INSERT INTO Register (intEmployee) VALUES (" & intEmployee & ")"
else
Response.write("No employee number indicated!")
end if
%>

</body>

</html>

I'm getting an error on this line

Conn.execute("INSERT INTO Register (intEmployee) VALUES (" & intEmployee & ")"

I haven;t read all the detail, there may be many other issues, but I think your immediate problem is just a missing )
try this
Conn.execute("INSERT INTO Register (intEmployee) VALUES (" & intEmployee & ")")

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fritz_the_blank

Well, I definitely messed up the parentheses...BillAn1 and acperkins have sharp eyes.

FtB

Anthony Perkins

>>Well, I definitely messed up the parentheses...BillAn1 and acperkins have sharp eyes.<<
Nah. I just like to nit-pick.

fritz_the_blank

Well, in the world of coding, nit-picking is a very valuable skill!

FtB

See my response here:

https://www.experts-exchange.com/questions/21085260/I-need-to-make-a-page-that-uses-one-table-in-SQL-to-update-another-ASAP.html

RCMB

ASKER

FtB,

I got your code working. Thank you very much.

rcmb thank you very much, I would like to be able to do it the way that you demonstrate as well.
I will give you points in the FP section if we can figure out the third page.

Everybody Else who helped. Thank you very much. I will give partial credit for any advice in reference to
my ealier comment:

Comment from dtolo
Date: 08/06/2004 12:28PM PDT
Your Comment

Also help full (but not required) is if the register table reaches a certain amount (say 100) user would geat a
message stating that the class is full please choos another class. (the table would not get upgdated) and be
redirected to a different page.

I also would like to make the first page show the employee numbers rather than last name. Once the number
is selected in the drop down then the first and last name info would show up. (So that the user knows that
they selected the correct employee number. Then they can hit the submit button confident that they selected
the correct number.

Any assists on this question will be greatly appreciated.

~David

fritz_the_blank

Okay, please post your code as you have it now, and I'll see what can be done about reaching the limit.

Once that is done, perhaps we can turn our attention to the remaining item.

You might also want to consider posting these as separate questions and close this one out.

FtB

fritz_the_blank

In any event, I am glad that we got this far!

FtB

ASKER

Thank You so much!

Here it is:

Page 1

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Register For Class</title>
</head>

<body>

<form action="updateEmployee.asp" name="frmEmployee" method="POST">
<select name="intEmployee">
<option></option>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=15ff9099pc;UID=sa;PWD=olot9864;DATABASE=CSSD"
Conn.open DSNtest
dim objRS, strSQL
set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT EmployeeNumber, LastName FROM Employee ORDER BY LastName"
objRS.Open strSQL,Conn,3,3
do while not objRS.eof
%>
<option value="<%=objRS("EmployeeNumber")%>"><%=objRS("LastName")%></option>
<%
objRS.MoveNext()
loop
%>
</select><br>
<INPUT TYPE="submit" value="Submit">
</form>
</body>

</html>

Page 2

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Update Employee</title>
</head>

<body>

<%
dim intEmployee
intEmployee = Request.Form("intEmployee")
if intEmployee <> "" then
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=15ff9099pc;UID=sa;PWD=olot9864;DATABASE=CSSD"
Conn.open DSNtest
Conn.execute("INSERT INTO REGISTER (EmployeeNumber) VALUES (" & intEmployee & ")")
else
Response.write("No employee number indicated!")
end if
%>
</body>

</html>

Page 2

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Update Employee</title>
</head>

<body>

<%
dim intEmployee
intEmployee = Request.Form("intEmployee")
if intEmployee <> "" then
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=15ff9099pc;UID=sa;PWD=olot9864;DATABASE=CSSD"
Conn.open DSNtest
Conn.execute("INSERT INTO REGISTER (EmployeeNumber) VALUES (" & intEmployee & ")")
else
Response.write("No employee number indicated!")
end if
%>
</body>

</html>

ASKER

Sorry,

I wrote page 2 twice :-)

fritz_the_blank

This code looks like it inserts twice--did you test it?

FtB

fritz_the_blank

Oh, okay.

Hang on a minute...

FtB

fritz_the_blank

<%
dim intEmployee
intEmployee = Request.Form("intEmployee")
if intEmployee <> "" then
Set Conn = Server.CreateObject("ADODB.Connection")
      DSNtest="DRIVER={SQL Server};SERVER=15ff9099pc;UID=sa;PWD=olot9864;DATABASE=CSSD"
      Conn.open DSNtest
      'determine how many people have already registered
      dim objRS
      set objRS = Conn.Execute("Select Count(*) AS intRecords FROM Register")
      'if less than 100 add record, otherwise notify user that class if full
      if objRS("intRecords") <100 then
            Conn.execute("INSERT INTO REGISTER (EmployeeNumber) VALUES (" & intEmployee & ")")
      else
            Response.write("Class full!")
      end if
      objRS.Close
      set objRS = Nothing
      Conn.close
      set Conn = nothing
else
Response.write("No employee number indicated!")
end if
%>

ASKER

Awesome!

Thank you fritz I will post them as a seperate question and add a comment here so that if you get a chance you can take a crack at it.

Thanks Again,
You are a great coder.
~David

fritz_the_blank

>>I also would like to make the first page show the employee numbers rather than last name. Once the number
is selected in the drop down then the first and last name info would show up. (So that the user knows that
they selected the correct employee number. Then they can hit the submit button confident that they selected
the correct number.<<

This part sounds like a completely different question and is really difficult. However, what about something like this:

Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=15ff9099pc;UID=sa;PWD=olot9864;DATABASE=CSSD"
Conn.open DSNtest
dim objRS, strSQL
set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT EmployeeNumber, Cstr(EmployeeNumber) + ': ' + LastName + ', ' + FirstName AS strName FROM Employee ORDER BY LastName"

objRS.Open strSQL,Conn,3,3
do while not objRS.eof
%>
<option value="<%=objRS("EmployeeNumber")%>"><%=objRS("strName")%></option>
<%
objRS.MoveNext()
loop
%>
</select><br>

fritz_the_blank

Once you give that a try, I have some suggestions about your connection code to make it more robust.

FtB

ASKER

Fritz I have continued this question Here:

https://www.experts-exchange.com/questions/21088141/Class-Registration-Page.html

I get an error on your new line of code here:

objRS.Open strSQL,Conn,3,3

It states:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]'Cstr' is not a recognized function name.