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
LVL 2
dtoloAsked:
Who is Participating?
 
fritz_the_blankConnect With a Mentor Commented:
First page (you will need to change some of this to match your environment):

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

<BODY>
<form action="updateEmployee.asp" name="frmEmployee" method="POST">
<select name="intEmployee">
      <option></option>
      <%
      'create and open your connecton
      dim objRS, strSQL
      set objRS = Server.CreateObject("ADODB.Recordset")
      strSQL = "SELECT intID, 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>
0
 
dtoloAuthor Commented:
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.
0
 
dtoloAuthor Commented:
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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
fritz_the_blankCommented:
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>
0
 
fritz_the_blankCommented:
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
0
 
joeposter649Commented:
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.
0
 
dtoloAuthor Commented:
Can I include the join in the asp?
0
 
fritz_the_blankCommented:
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
0
 
dtoloAuthor Commented:
Yes, but it has to run by itself.  I won't be at the company much longer.
0
 
dtoloAuthor Commented:
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>
      <!--webbot bot="DatabaseRegionStart" s-columnnames="EmployeeNumber,FirstName,LastName,ThirdName,FourthName,Title,Address1,Address2,Town,Unit,Region,AreaCode,PhoneNumber,PhoneExtension,Email,Fax,GA" s-columntypes="202,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200" s-dataconnection="TA-UPDATE" b-tableformat="FALSE" b-menuformat="TRUE" s-menuchoice="EmployeeNumber" s-menuvalue="EmployeeNumber" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="Employee" s-displaycolumns="EmployeeNumber" s-criteria s-order s-sql="SELECT * FROM Employee" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" preview=" &lt;span style=&quot;color: rgb(0,0,0); background-color: rgb(255,255,0)&quot;&gt;Database&lt;/span&gt; " startspan --><!--#include file="../_fpclass/fpdblib.inc"-->
<% 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
%>
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="55638" --><select NAME="EmployeeNumber" SIZE="1">
      <!--webbot bot="AspInclude" clientside u-incfile="../_fpclass/fpdbrgn1.inc" startspan --><!--#include file="../_fpclass/fpdbrgn1.inc"--><!--webbot bot="AspInclude" endspan i-checksum="52766" -->
      <option><%=FP_FieldHTML(fp_rs,"EmployeeNumber")%></option>
      <!--webbot bot="AspInclude" clientside u-incfile="../_fpclass/fpdbrgn2.inc" startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"--><!--webbot bot="AspInclude" endspan i-checksum="52830" -->
      </select><!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="TRUE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside preview=" &lt;span style=&quot;color: rgb(0,0,0); background-color: rgb(255,255,0)&quot;&gt;Results&lt;/span&gt; " startspan --><!--webbot bot="DatabaseRegionEnd" endspan --></nobr></form>

</body>

</html>
0
 
fritz_the_blankCommented:
You have to do this in frontpage?

Ftb
0
 
dtoloAuthor Commented:
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
 

0
 
dtoloAuthor Commented:
The site has frontpage extensions installed on it.
0
 
fritz_the_blankCommented:
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

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

FtB
0
 
dtoloAuthor Commented:
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 --><!--webbot bot="DatabaseRegionEnd" endspan --></nobr></form>


Am I leaving any thing out?
0
 
fritz_the_blankCommented:
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
0
 
dtoloAuthor Commented:
<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">
<!--[if gte mso 9]><xml>
<mso:CustomDocumentProperties>
<mso:connectionstatus msdt:dt="string">IntranetLiasons=1 Links=1 calendar=1 sql=1 CSSDEmployee=1 collect=1 TA-UPDATE=1</mso:connectionstatus>
</mso:CustomDocumentProperties>
</xml><![endif]-->
0
 
dtoloAuthor Commented:
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.

0
 
dtoloAuthor Commented:
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?
0
 
dtoloAuthor Commented:
objRS.Open strSQL,objConnecton,3,3
0
 
dtoloAuthor Commented:
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
0
 
dtoloAuthor Commented:
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>
0
 
dtoloAuthor Commented:
<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 & ")"
0
 
BillAn1Commented:
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 & ")")
0
 
Anthony PerkinsConnect With a Mentor Commented:
Or alternatively one too many :)

Conn.execute "INSERT INTO Register (intEmployee) VALUES (" & intEmployee & ")"
0
 
fritz_the_blankCommented:
Well, I definitely messed up the parentheses...BillAn1 and acperkins have sharp eyes.

FtB
0
 
Anthony PerkinsCommented:
>>Well, I definitely messed up the parentheses...BillAn1 and acperkins have sharp eyes.<<
Nah.  I just like to nit-pick.
0
 
fritz_the_blankCommented:
Well, in the world of coding, nit-picking is a very valuable skill!

FtB
0
 
dtoloAuthor Commented:
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
 
0
 
fritz_the_blankCommented:
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
0
 
fritz_the_blankCommented:
In any event, I am glad that we got this far!

FtB
0
 
dtoloAuthor Commented:
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>
0
 
dtoloAuthor Commented:
Sorry,

I wrote page 2 twice :-)
0
 
fritz_the_blankCommented:
This code looks like it inserts twice--did you test it?

FtB
0
 
fritz_the_blankCommented:
Oh, okay.

Hang on a minute...

FtB
0
 
fritz_the_blankCommented:
<%
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
%>
0
 
dtoloAuthor Commented:
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
0
 
fritz_the_blankCommented:
>>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>



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

FtB
0
 
dtoloAuthor Commented:
Fritz I have continued this question Here:

http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21088141.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.
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.