Solved

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

Posted on 2004-08-06
42
299 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:dtolo
  • 20
  • 17
  • 2
  • +3
42 Comments
 
LVL 2

Author Comment

by:dtolo
ID: 11738658
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
 
LVL 2

Author Comment

by:dtolo
ID: 11738716
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
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 450 total points
ID: 11738717
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11738834
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11738845
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
 
LVL 15

Expert Comment

by:joeposter649
ID: 11738873
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
 
LVL 2

Author Comment

by:dtolo
ID: 11738910
Can I include the join in the asp?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11738921
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
 
LVL 2

Author Comment

by:dtolo
ID: 11738958
Yes, but it has to run by itself.  I won't be at the company much longer.
0
 
LVL 2

Author Comment

by:dtolo
ID: 11738969
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11738976
You have to do this in frontpage?

Ftb
0
 
LVL 2

Author Comment

by:dtolo
ID: 11739006
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
 
LVL 2

Author Comment

by:dtolo
ID: 11739015
The site has frontpage extensions installed on it.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11739041
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
 
LVL 46

Expert Comment

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

FtB
0
 
LVL 2

Author Comment

by:dtolo
ID: 11739074
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11739119
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
 
LVL 2

Author Comment

by:dtolo
ID: 11739146
<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
 
LVL 2

Author Comment

by:dtolo
ID: 11739490
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
 
LVL 2

Author Comment

by:dtolo
ID: 11739750
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
 
LVL 2

Author Comment

by:dtolo
ID: 11739840
objRS.Open strSQL,objConnecton,3,3
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Author Comment

by:dtolo
ID: 11739844
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
 
LVL 2

Author Comment

by:dtolo
ID: 11739955
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
 
LVL 2

Author Comment

by:dtolo
ID: 11740013
<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
 
LVL 17

Expert Comment

by:BillAn1
ID: 11742515
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 11743698
Or alternatively one too many :)

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

Expert Comment

by:fritz_the_blank
ID: 11746644
Well, I definitely messed up the parentheses...BillAn1 and acperkins have sharp eyes.

FtB
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11747205
>>Well, I definitely messed up the parentheses...BillAn1 and acperkins have sharp eyes.<<
Nah.  I just like to nit-pick.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11747314
Well, in the world of coding, nit-picking is a very valuable skill!

FtB
0
 
LVL 12

Expert Comment

by:rcmb
ID: 11751296
0
 
LVL 2

Author Comment

by:dtolo
ID: 11756616
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11756803
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11756813
In any event, I am glad that we got this far!

FtB
0
 
LVL 2

Author Comment

by:dtolo
ID: 11757077
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
 
LVL 2

Author Comment

by:dtolo
ID: 11757091
Sorry,

I wrote page 2 twice :-)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11757094
This code looks like it inserts twice--did you test it?

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11757106
Oh, okay.

Hang on a minute...

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11757149
<%
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
 
LVL 2

Author Comment

by:dtolo
ID: 11757252
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11757293
>>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
 
LVL 46

Expert Comment

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

FtB
0
 
LVL 2

Author Comment

by:dtolo
ID: 11761362
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now