Unterminated string constant

Hi.....

I need a little bit of help.  I am trying to get a page to output inofmation on employee lieu time.

this is my htm page, the user come to this page after selecting from a menu, then the user will type in his employee id and the year that he is looking at the report name is LieuReport this after inputing employee id and the year will query the database and bring back his information.  The asp code is below  the reportcode.htm.  I am not sure what I am doing wrong.  If you could help that would be great

Thanks

Denise:)

<html>
<head>
<title>LieuReport
</title>

</head>

<BODY>


<H1><FONT style=FONT-SIZE:30pt Face="Times New Roman" Color=#000080><B>Lieu Time Report</FONT></B></H1>

<form name='LieuDays' method='post' action='lieudays.asp'>

<br>
<br>

<table border="0" cellpadding="0" cellspacing="0" align=center>
    <tr>
            <td ALIGN=right height="21"><font size=-1 color="#000000">EMPLOYEE ID:</font></td>
            <td><input type='text' name='cs_empid' size=20></td>
         </tr>
    <tr>
            <td ALIGN=right height="25"><font size=-1 color="#000000">YEAR:</font></td>
            <td><input type='year' name='cs_year' size=20></td>
        </tr>
    <tr>
            <td ALIGN=CENTER VALIGN=BOTTOM COLSPAN="2">
             <input type='submit' value='SUBMIT'></td>
      </tr>
</table>
<BR>
<BR>


<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >
<TR HEIGHT=18 >
      <TD WIDTH=4  ALIGN=LEFT > <BR></TD>
      <TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE="Times New Roman" COLOR=#000080>ID</FONT></B></I></TD>
      <TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>First Name</FONT></B></I></TD>
      <TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>Last Name</FONT></B></I></TD>
      <TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>Years of Service</FONT></B></I></TD>
      <TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>Date Worked</FONT></B></I></TD>
      <TD WIDTH=50  ALIGN=LEFT > <BR></TD>
      <TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>Absent Code</FONT></B></I></TD>
      <TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>Lieu Time Hours Used</FONT></B></I></TD>
</TR>
</TABLE>
<BR>
<br>
<BR>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >
      <TR HEIGHT=18 >
      <TD WIDTH=468  ALIGN=LEFT > <BR></TD><TD WIDTH=176  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>Accumulated Lieu Time</FONT></B></I></TD>
</TR>
</TABLE>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >
      <TR HEIGHT=18 >
      <TD WIDTH=468  ALIGN=LEFT > <BR></TD><TD WIDTH=196  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>Lieu Time Left(in hours)</FONT></B></I></TD>
</TR>
</TABLE>

</form>
</body>
</html>
______________________________
ASP code

<%@ Language=VBScript %>
<% Option Explicit %>
<%      
     Dim objRS
     Dim objConn
     Dim strSQL
     Dim rs
     Dim current_datetime

     set objConn = Server.CreateObject("ADODB.Connection")
        objConn.ConnectionString= "DSN=timesheet.dsn"
     objConn.Open
     
     response.expires = 0
     set rs = server.createobject("adodb.recordset")
     current_datetime = formatdatetime(date(), 1)

%>

<html>

<head>
<title>LieuDays</title>
<base target="_top">
</head>

<%
     

     strSQL = "SELECT [Employee Info].ID, [Employee Info].First_Name, [Employee Info].Last_Name, [Employee Info].[Years of Service], timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed, Year([date_worked]) As Exp1
     FROM [Employee Info] INNER JOIN timeperiod ON [Employee Info].ID = timeperiod.id
     WHERE (((timeperiod.Absent_code)=7) AND ((Year([date_worked]))=[Year]));"
      
           

     Set objRS = Server.CreateObject("ADODB.RecordSet")
     objRS.Open "timeperiod", objConn,3,3
     Do While Not objRS.EOF
          Response.Write "<TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE="Times New Roman" COLOR=#000080>" & (objRS("id") & "</FONT></B></I></TD>"
        Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>" & (objRS("First_Name") & "</FONT></B></I></TD>"      
        Response.Write "<TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>" & (objRS("Last_Name") & "</FONT></B></I></TD>"
        Response.Write "<TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>" & (objRS("Years of Service") & "</FONT></B></I></TD>"
        Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>" & (objRS("Date_worked") & "</FONT></B></I></TD>"
        Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>" & (objRS("Absent_code") & "</FONT></B></I></TD>"
        Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080>" & (objRS("LieuTimeused") & "</FONT></B></I></TD></td>      
          objRS.MoveNext
     Loop

     objRS.Close
     Set objRs = Nothing
     objConn.Close
     Set objConn = Nothing
     
%>
</html>

neicy62Asked:
Who is Participating?
 
aprestoCommented:
Sorry in the code i posted above ignore the line about it not liking something, i copied and pasted the wrong one i think,

change your sql to this and it should work:

strSQL = "SELECT EmployeeInfo.ID, EmployeeInfo.First_Name, EmployeeInfo.Last_Name, EmployeeInfo.YearsofService, timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed FROM EmployeeInfo INNER JOIN timeperiod ON EmployeeInfo.ID = timeperiod.id WHERE ((timeperiod.Absent_code)=7) AND employeeInfo.ID = '"&usersrch&"';"

It doesnt look much different but the usersrch variable has single quoted around it to indicate that it is a text field:

You might want to remember the following as its important to know:

When refering to a TEXT value in a variable in SQL it should look like this                          ' " & Variable & " '  
                 
When refering to a NUMERICAL value in a variable in SQL it should look like this                  " & Variable & "        

When refering to a DATE or TIME value in a variable in SQL it should look like this               # " Variable " #          

Just for future reference because this got my a few times when i was starting out
0
 
ColosseoCommented:
Hi

do you get a line number when you get the Unterminated string constant error?

for a start on the lines where you have

Response.Write "<TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE="Times New Roman" COLOR=#000080>" & (objRS("id") & "</FONT></B></I></TD>"

try changing FACE="Times New Roman" to FACE=""Times New Roman""

Cheers

Scott
0
 
neicy62Author Commented:
Hi

The place were I am getting the unterminated string constant error is in the sql line 30, column 228

the sql...which I took from my access database from which I am doing the query to

strSQL = "SELECT [Employee Info].ID, [Employee Info].First_Name, [Employee Info].Last_Name, [Employee Info].[Years of Service], timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed,
Year([date_worked]) As Exp1---------------IT DOES NOT LIKE THIS-----------
FROM [Employee Info] INNER JOIN timeperiod ON [Employee Info].ID = timeperiod.id
WHERE (((timeperiod.Absent_code)=7) AND ((Year([date_worked]))=[Year]));"
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gladxmlCommented:
try to use this instead in your do while loop          

Response.Write "<TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("id") & "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("First_Name") & "</FONT></B></I></TD>"    
       Response.Write "<TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Last_Name") & "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Years of Service") & "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Date_worked") & "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Absent_code") & "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("LieuTimeused") & "</FONT></B></I></TD></td>"    
0
 
ColosseoCommented:
Plus your last response.write is missing a " at the end

Also

objRS.Open "timeperiod", objConn,3,3

should this not be

objRS.Open strSQL , objConn,3,3

so that it is using the SELECT statement that you are creating

Your SELECT statement doesnt appear to be using the employee id and year from the submitting form but we can look at that once we get this error resolved

Also need to add the <table> and <tr> tags to your loop

Cheers

Scott
0
 
gladxmlCommented:
in your asp code page try to use this line... I make the code an html and only the dynamic values is being response.write...

==== just cut and paste
<%@ Language=VBScript %>
<% Option Explicit %>
<%      
     Dim objRS
     Dim objConn
     Dim strSQL
     Dim rs
     Dim current_datetime

     set objConn = Server.CreateObject("ADODB.Connection")
        objConn.ConnectionString= "DSN=timesheet.dsn"
     objConn.Open
     
     response.expires = 0
     set rs = server.createobject("adodb.recordset")
     current_datetime = formatdatetime(date(), 1)

%>

<html>

<head>
<title>LieuDays</title>
<base target="_top">
</head>

<%
     

     strSQL = "SELECT [Employee Info].ID, [Employee Info].First_Name, [Employee Info].Last_Name, [Employee Info].[Years of Service], timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed, Year([date_worked]) As Exp1
     FROM [Employee Info] INNER JOIN timeperiod ON [Employee Info].ID = timeperiod.id
     WHERE (((timeperiod.Absent_code)=7) AND ((Year([date_worked]))=[Year]));"
     
         
     Set objRS = Server.CreateObject("ADODB.RecordSet")
     objRS.Open "timeperiod", objConn,3,3
     Do While Not objRS.EOF
%>
          <TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE="Times New Roman" COLOR=#000080><%=(objRS("id")%></FONT></B></I></TD>
       <TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=(objRS("First_Name")%></FONT></B></I></TD>    
       <TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=(objRS("Last_Name")%></FONT></B></I></TD>
       <TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=(objRS("Years of Service")%></FONT></B></I></TD>
       <TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=(objRS("Date_worked")%></FONT></B></I></TD>
       <TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=(objRS("Absent_code")%></FONT></B></I></TD>
       <TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=(objRS("LieuTimeused")%></FONT></B></I></TD></td>
<%    
          objRS.MoveNext
     Loop

     objRS.Close
     Set objRs = Nothing
     objConn.Close
     Set objConn = Nothing
     
%>
</html>
0
 
gladxmlCommented:
try to use this instead on the do while loop... There is an extra ( before the synamic values which also can cause error...

     
===
<TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE="Times New Roman" COLOR=#000080><%=objRS("id")%></FONT></B></I></TD>
       <TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("First_Name")%></FONT></B></I></TD>    
       <TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("Last_Name")%></FONT></B></I></TD>
       <TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("Years of Service")%></FONT></B></I></TD>
       <TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("Date_worked")%></FONT></B></I></TD>
       <TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("Absent_code")%></FONT></B></I></TD>
       <TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("LieuTimeused")%></FONT></B></I></TD></td>
0
 
neicy62Author Commented:
Hi there.....

I changed the things you said and it keep coming back to the unterminated string constan with the sql statment and I am not sure what to do

Denise:)
0
 
gladxmlCommented:
try to use this instead. HTH...

=====
 strSQL = "SELECT [Employee Info].ID, [Employee Info].First_Name, [Employee Info].Last_Name, [Employee Info].[Years of Service], timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed, Year([date_worked]) As Exp1  FROM [Employee Info] INNER JOIN timeperiod ON [Employee Info].ID = timeperiod.id WHERE (((timeperiod.Absent_code)=7) AND ((Year([date_worked]))=[Year]));"

     Set objRS = Server.CreateObject("ADODB.RecordSet")
     objRS.Open "timeperiod", objConn,3,3
     Do While Not objRS.EOF
%>
<TABLE>
<TR>
          <TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE="Times New Roman" COLOR=#000080><%=objRS("id")%></FONT></B></I></TD>
       <TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("First_Name")%></FONT></B></I></TD>    
       <TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("Last_Name")%></FONT></B></I></TD>
       <TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("Years of Service")%></FONT></B></I></TD>
       <TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("Date_worked")%></FONT></B></I></TD>
       <TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("Absent_code")%></FONT></B></I></TD>
       <TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("LieuTimeused")%></FONT></B></I></TD></td>
</TR>
</TABLE>
<%    
          objRS.MoveNext
     Loop

     objRS.Close
     Set objRs = Nothing
     objConn.Close
     Set objConn = Nothing
     
%>
</html>


0
 
gladxmlCommented:
if still my comment does not work sorry I am going now... Colosseo will help you...

Happy programming...
0
 
neicy62Author Commented:
Hi Colosseo....

I have done the things you said this is what my asp look like now....  thanks denise

<%@ Language=VBScript %>
<% Option Explicit %>
<%      
     Dim objRS
     Dim objConn
     Dim strSQL
     Dim rs
     Dim current_datetime

     set objConn = Server.CreateObject("ADODB.Connection")
     objConn.ConnectionString= "DSN=timesheet.dsn"
     objConn.Open
     
     response.expires = 0
     set rs = server.createobject("adodb.recordset")
     current_datetime = formatdatetime(date(), 1)

%>

<html>

<head>
<title>LieuDays</title>
<base target="_top">
</head>

<%
     

     strSQL = "SELECT [Employee Info].ID, [Employee Info].First_Name, [Employee Info].Last_Name, [Employee Info].[Years of Service], timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed, Year([date_worked]) As Exp1
     FROM [Employee Info] INNER JOIN timeperiod ON [Employee Info].ID = timeperiod.id
     WHERE (((timeperiod.Absent_code)=7) AND ((Year([date_worked]))=[Year]));"
      
           

     Set objRS = Server.CreateObject("ADODB.RecordSet")
     objRS.Open strSQL, objConn,3,3
     Do While Not objRS.EOF

          Response.Write "<table><TR><TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("id") & "</FONT></B></I></TD>"
        Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("First_Name") & "</FONT></B></I></TD>"      
        Response.Write "<TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Last_Name") & "</FONT></B></I></TD>"
        Response.Write "<TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Years of Service") & "</FONT></B></I></TD>"
        Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Date_worked") & "</FONT></B></I></TD>"
        Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Absent_code") & "</FONT></B></I></TD>"
        Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("LieuTimeused") & "</FONT></B></I></TD></TR></Table>"      
          objRS.MoveNext
     Loop

     objRS.Close
     Set objRs = Nothing
     objConn.Close
     Set objConn = Nothing
     
%>
</html>
0
 
ajitanandCommented:
The problem since begining is that you are splitting the sql string in multiple lines, whch is not allowed in vbscript/asp. To correct it use the foll code:
----------------------------------------------------------------
<%@ Language=VBScript %>
<% Option Explicit %>
<%      
     Dim objRS
     Dim objConn
     Dim strSQL
     Dim rs
     Dim current_datetime

     set objConn = Server.CreateObject("ADODB.Connection")
     objConn.ConnectionString= "DSN=timesheet.dsn"
     objConn.Open
     
     response.expires = 0
     set rs = server.createobject("adodb.recordset")
     current_datetime = formatdatetime(date(), 1)

%>

<html>

<head>
<title>LieuDays</title>
<base target="_top">
</head>

<%
     

     strSQL = "SELECT [Employee Info].ID, [Employee Info].First_Name, [Employee Info].Last_Name, [Employee Info].[Years of Service], timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed, Year([date_worked]) As Exp1 " & _
            "   FROM [Employee Info] INNER JOIN timeperiod ON [Employee Info].ID = timeperiod.id " & _
            "      WHERE (((timeperiod.Absent_code)=7) AND ((Year([date_worked]))=[Year]));"
     
         

     Set objRS = Server.CreateObject("ADODB.RecordSet")
     objRS.Open strSQL, objConn,3,3
     Do While Not objRS.EOF

          Response.Write "<table><TR><TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("id") & "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("First_Name") & "</FONT></B></I></TD>"    
       Response.Write "<TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Last_Name") & "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Years of Service") & "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Date_worked") & "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Absent_code") & "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("LieuTimeused") & "</FONT></B></I></TD></TR></Table>"    
          objRS.MoveNext
     Loop

     objRS.Close
     Set objRs = Nothing
     objConn.Close
     Set objConn = Nothing
     
%>
</html>

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

rgds,
Ajit Anand
0
 
neicy62Author Commented:
Hi there...

This is what the code looks like now after i copied yours and now I am getting Microsoft VBScript compilation(800A03EA) Syntax error
/lieudays.asp line 40 column 144

I am not sure what is wrong now.  Thanks for the help

<%@ Language=VBScript %>
<% Option Explicit %>
<%      
     Dim objRS
     Dim objConn
     Dim strSQL
     Dim rs
     Dim current_datetime

     set objConn = Server.CreateObject("ADODB.Connection")
     objConn.ConnectionString= "DSN=timesheet.dsn"
     objConn.Open
     
     response.expires = 0
     set rs = server.createobject("adodb.recordset")
     current_datetime = formatdatetime(date(), 1)

%>

<html>

<head>
<title>LieuDays</title>
<base target="_top">
</head>

<%
     

     strSQL = "SELECT [Employee Info].ID, [Employee Info].First_Name, [Employee Info].Last_Name, [Employee Info].[Years of Service], timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed, Year([date_worked]) As Exp1 " & _
          "   FROM [Employee Info] INNER JOIN timeperiod ON [Employee Info].ID = timeperiod.id " & _
          "     WHERE (((timeperiod.Absent_code)=7) AND ((Year([date_worked]))=[Year]));"
     
         

     Set objRS = Server.CreateObject("ADODB.RecordSet")
     objRS.Open strSQL, objConn,3,3
     Do While Not objRS.EOF

       Response.Write "<table><TR><TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("id") & 
       "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("First_Name") & 
      "</FONT></B></I></TD>"    
       Response.Write "<TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Last_Name") & 
      "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Years of Service") & 
      "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Date_worked") & 
      "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Absent_code") & 
      "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("LieuTimeused") & 
      "</FONT></B></I></TD></TR></Table>"    
       objRS.MoveNext
     Loop

     objRS.Close
     Set objRs = Nothing
     objConn.Close
     Set objConn = Nothing
     
%>
</html>
0
 
aprestoCommented:
make sure that this:

       Response.Write "<table><TR><TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("id") & "</FONT></B></I></TD>"

is on the same line in your code and there isnt a new line between any &'s and other code such as this:

       Response.Write "<table><TR><TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("id") & >>>>>>>>>>>This gap
       "</FONT></B></I></TD>"

Just a guess really, thats where i would assume the fault was
0
 
aprestoCommented:
Your original code was fine, here is where the problem was, you werent closing with a finishing double quote:

COLOR=#000080>" & (objRS("LieuTimeused") & "</FONT></B></I></TD></td>
          objRS.MoveNext
     Loop

     objRS.Close
     Set objRs = Nothing
     objConn.Close
     Set objConn = Nothing
     
%>
</html>


SHOULD BE:

COLOR=#000080>" & (objRS("LieuTimeused") & "</FONT></B></I></TD></td>" >>>>>>>>>CORRECT VERSION
          objRS.MoveNext
     Loop

     objRS.Close
     Set objRs = Nothing
     objConn.Close
     Set objConn = Nothing
     
%>
</html>
0
 
aprestoCommented:
I just re-read your second post stating that the prob was in line 3 of the orig code so maybe not a good one that last post - oops!i thought thats where the prob was -

anything yet?
0
 
neicy62Author Commented:
Hi apresto...

I have closed all the spaces and I am getting the same error

This is what the code looks like now after i copied yours and now I am getting Microsoft VBScript compilation(800A03EA) Syntax error
/lieudays.asp line 40 column 160
which is this line here

Response.Write "<table><TR><TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("id") & "</FONT></B></I></TD>"

this apprear all on one line and no space, not sure
0
 
neicy62Author Commented:
Hi apresto....

I figured it out now I did not have enough bracket about (objRS("id")  i needed to add a bracket at the end.  but now I am getting an error of

Microsoft OLE DB Provider for ODBC Drivers (0X80040E10)
[Microsoft][ODBC Microsoft Access driver] Too few parameter Expected 1
/lieudays.asp, line 37

Can you give me help

Thanks
Denise:)
0
 
aprestoCommented:
try this

Response.Write "<table><TR><TD WIDTH='112'  ALIGN='LEFT' ><B><I><FONT style='FONT-SIZE:12pt' FACE='Times New Roman' COLOR='#000080'>" & (objRS("id") & "</FONT></B></I></TD>"

im not sure i have got the <FONT style='FONT-SIZE:12pt' bit right but if the error changes we are getting closer....or even further away
0
 
aprestoCommented:
what line 37
0
 
neicy62Author Commented:
hI APRESTO

Sorry here is the whole code I have marked line 34 I am not sure what is wrong with it??

Thanks

Denise:)


<%@ Language=VBScript %>
<% Option Explicit %>
<%      
     Dim objRS
     Dim objConn
     Dim strSQL
     Dim rs
     Dim current_datetime

     set objConn = Server.CreateObject("ADODB.Connection")
     objConn.ConnectionString= "DSN=timesheet.dsn"
     objConn.Open
     
     response.expires = 0
     set rs = server.createobject("adodb.recordset")
     current_datetime = formatdatetime(date(), 1)

%>

<html>

<head>
<title>LieuDays</title>
<base target="_top">
</head>

<%
 
     strSQL = "SELECT [Employee Info].ID, [Employee Info].First_Name, [Employee Info].Last_Name, [Employee Info].[Years of Service], timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed, Year([date_worked]) As Exp1 " & _
          "   FROM [Employee Info] INNER JOIN timeperiod ON [Employee Info].ID = timeperiod.id " & _
          "     WHERE (((timeperiod.Absent_code)=7) AND ((Year([date_worked]))=[Year]));"
   

     Set objRS = Server.CreateObject("ADODB.RecordSet")
     objRS.Open strSQL, objConn,3,3
     Do While Not objRS.EOF------------THIS WOULD BE LINE 34

       Response.Write "<table><TR><TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("id") & 
       "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("First_Name") & 
      "</FONT></B></I></TD>"    
       Response.Write "<TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Last_Name") & 
      "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Years of Service") & 
      "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Date_worked") & 
      "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Absent_code") & 
      "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("LieuTimeused") & 
      "</FONT></B></I></TD></TR></Table>"    
       objRS.MoveNext
     Loop

     objRS.Close
     Set objRs = Nothing
     objConn.Close
     Set objConn = Nothing
     
%>
</html>
0
 
aprestoCommented:
Try using this instead:

If NOT rs.EOF Then
    Do While Not objRS.EOF
       Response.Write "<table><TR><TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("id") & 
       "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("First_Name") & 
     "</FONT></B></I></TD>"    
       Response.Write "<TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Last_Name") & 
     "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Years of Service") & 
     "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Date_worked") & 
     "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Absent_code") & 
     "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("LieuTimeused") & 
     "</FONT></B></I></TD></TR></Table>"    
       objRS.MoveNext
    wend
End If
0
 
aprestoCommented:
sorry change this

If not rs.EOF

wit this

If NOt objRS.eof
0
 
neicy62Author Commented:
Hi Apresto...

That seemed to work except one small thing what does wend mean it does not like that

Thanks

Denise:)
0
 
aprestoCommented:
Its like the end of the While statement, If uses End If, Wend (as far as i am aware is the end of the while, but sadly i never asked the person who suggested it to me, it worked so no questions asked.  Try replacing it with loop.
0
 
aprestoCommented:
aaahh sorry got my wires crossed you as using DO WHILE i mistook it for WHILE.  Try this:

If NOT rs.EOF Then
    While Not objRS.EOF
       Response.Write "<table><TR><TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("id") & 
       "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("First_Name") & 
     "</FONT></B></I></TD>"    
       Response.Write "<TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Last_Name") & 
     "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Years of Service") & 
     "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Date_worked") & 
     "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("Absent_code") & 
     "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>" & (objRS("LieuTimeused") & 
     "</FONT></B></I></TD></TR></Table>"    
       objRS.MoveNext
    wend
End If
0
 
aprestoCommented:
Sorry to keep re-posting but  i think i am pasting you the wrong code as again it looks as though you arent using enough brackets in some of the statements:

(objRS("id") - (objRS("id"))

(objRS("First_Name") - (objRS("First_Name")) etc
0
 
neicy62Author Commented:
Hi apresto...

That worked one last problem if you will now it does not like my objConn.Open

Error Type:
Provider(08X00004005)
Unspecified error
/lieudays.asp line 12

<%@ Language=VBScript %>
<% Option Explicit %>
<%      
     Dim objRS
     Dim objConn
     Dim strSQL
     Dim rs
     Dim current_datetime

     set objConn = Server.CreateObject("ADODB.Connection")
     objConn.ConnectionString= "DSN=timesheet.dsn"
     objConn.Open-----This would be line 12
     
     response.expires = 0
     set rs = server.createobject("adodb.recordset")
     current_datetime = formatdatetime(date(), 1)

%>
0
 
aprestoCommented:
whats your database called? Is this your whole database connection?

     set objConn = Server.CreateObject("ADODB.Connection")
     objConn.ConnectionString= "DSN=timesheet.dsn"
     objConn.Open-----This would be line 12

0
 
gladxmlCommented:
neicy62,

Have you follow and  tried the comments that I had posted... Just asking....

for your question at hand

replace this lines

     set objConn = Server.CreateObject("ADODB.Connection")
     objConn.ConnectionString= "DSN=timesheet.dsn"
     objConn.Open-----This would be line 12

with this

     set objConn = Server.CreateObject("ADODB.Connection")
     objConn.Open "DSN=timesheet.dsn"

Now putting all my comment this would be...

===just cut and paste...

<%@ Language=VBScript %>
<% Option Explicit %>
<%      
     Dim objRS
     Dim objConn
     Dim strSQL
     Dim rs
     Dim current_datetime

     set objConn = Server.CreateObject("ADODB.Connection")
     objConn.Open "DSN=timesheet.dsn"
     
     
     response.expires = 0
     set rs = server.createobject("adodb.recordset")
     current_datetime = formatdatetime(date(), 1)

%>

<html>

<head>
<title>LieuDays</title>
<base target="_top">
</head>

<%
     strSQL = "SELECT [Employee Info].ID, [Employee Info].First_Name, [Employee Info].Last_Name, [Employee Info].[Years of Service], timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed, Year([date_worked]) As Exp1 FROM [Employee Info] INNER JOIN timeperiod ON [Employee Info].ID = timeperiod.id WHERE (((timeperiod.Absent_code)=7) AND ((Year([date_worked]))=[Year]));"
         
     Set objRS = Server.CreateObject("ADODB.RecordSet")
     objRS.Open "timeperiod", objConn,3,3

     Do While Not objRS.EOF
%>
<table><tr>
       <TD WIDTH=112  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:12pt FACE="Times New Roman" COLOR=#000080><%=objRS("id")%></FONT></I></B></TD>
       <TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("First_Name")%></FONT></I></B></TD>    
       <TD WIDTH=104  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("Last_Name")%></FONT></I></B></TD>
       <TD WIDTH=104  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("Years of Service")%></FONT></I></B></TD>
       <TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("Date_worked")%></FONT></I></B></TD>
       <TD WIDTH=108  ALIGN=LEFT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("Absent_code")%></FONT></I></B></TD>
       <TD WIDTH=148  ALIGN=RIGHT ><B><I><FONT style=FONT-SIZE:11pt FACE="Times New Roman" COLOR=#000080><%=objRS("LieuTimeused")%></FONT></I></B></TD></td>
</tr></table>
<%    
          objRS.MoveNext
     Loop

     objRS.Close
     Set objRs = Nothing
     objConn.Close
     Set objConn = Nothing
%>
</html>
0
 
aprestoCommented:
If that doesnt work try replacing your current connection wit this one (f you are using Access):

Dim strdb, conn, strcon

strDB = "YOUR DATABASE PATH"
Set Conn = Server.CreateObject("ADODB.Connection")
strCon = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=opensaysme; DBQ=" & Server.MapPath(strAccessDB)
Conn.Open strCon

Looks like in your connection you were missing the db driver
0
 
neicy62Author Commented:
Hi gladxml  

I tried that and now I get an error Item cannot be found in the collection corresponding to the requested name or ordinal
/lieudays.asp line 37

Denise:)
0
 
neicy62Author Commented:
Hey there

could it be my select statment that I am using that could be causing all these problems.
I don't think my SELECT statement is using the employee id and year from the submitting form

Denise:)
0
 
aprestoCommented:
its not liking this part of your code if its line 37 of the code gladxml gave you:

<%=objRS("First_Name")%>

Is this the name of your field in the database, doublecheck it?

also you shouldnt really have spaces inbetween your database fields, try changing them to this:  Employee_info rather than "Employee info" and change them in the asp - a guess i suppose but it could sort it out
0
 
neicy62Author Commented:
Hi there....

I have checked the names in my database and they do match I have taken out the spaces.
Here is what I have now any suggestions???

this is the ERROR
I tried that and now I get an error Item cannot be found in the collection corresponding to the requested name or ordinal
/lieudays.asp line 43


<%@ Language=VBScript %>
<% Option Explicit %>
<%  


     'open up a connection to access database  
     Dim objRS
     Dim objConn
     Dim strSQL
     Dim rs
     Dim current_datetime

     set objConn = Server.CreateObject("ADODB.Connection")
     objConn.ConnectionString= "DSN=timesheet.dsn"
     objConn.Open
     
     response.expires = 0
     set rs = server.createobject("adodb.recordset")
     current_datetime = formatdatetime(date(), 1)

%>

<html>

<head>
<title>LieuDays</title>
<base target="_top">
</head>

<%      

     strSQL = "SELECT [EmployeeInfo].ID, [EmployeeInfo].First_Name, [EmployeeInfo].Last_Name, [EmployeeInfo].[YearsofService], timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed, Year([date_worked]) As Exp1 " & _
          "   FROM [EmployeeInfo] INNER JOIN timeperiod ON [EmployeeInfo].ID = timeperiod.id " & _
          "     WHERE (((timeperiod.Absent_code)=7) AND ((Year([date_worked]))=[Year]));"
     
     'create recordset object             
     Set objRS = Server.CreateObject("ADODB.RecordSet")
     objRS.Open "timeperiod", objConn,3,3

     If NOT objRs.EOF Then
       While Not objRs.EOF      
       Response.Write "<TABLE><TR><TD WIDTH=112 ALIGN=LEFT><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman""COLOR=#000080>"&(objRS("id"))&"</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>"&(objRS("First_Name"))& "</FONT></B></I></TD>"    
       Response.Write "<TD WIDTH=104  ALIGN=LEFT><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>"&(objRS("Last_Name"))& "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=104  ALIGN=RIGHT><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>"&(objRS("YearsofService"))&"</FONT></B></I></TD>"---IT DOES NOT LIKE THIS LINE--------------------
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>"&(objRS("Date_worked"))&"</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>"&(objRS("Absent_code"))&"</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>"&(objRS("LieuTimeused"))&"</FONT></B></I></TD></TR></Table>"    
       objRS.MoveNext
       wend
     End If

     'clean up ADO objects      
     objRS.Close
     Set objRs = Nothing
     objConn.Close
     Set objConn = Nothing
     
%>
</html>


0
 
aprestoCommented:
by the looks of things you havent assigned your sql to the objRS recordset:

objRS.Open "timeperiod", objConn,3,3

Whats Time period, try changing timeperiod to strSQL

objRS.Open strSQL, objConn,3,3
0
 
neicy62Author Commented:
Hi apresto

Thanks for all your help as you see I am a newbie to asp.  I have changed the code back to
objRS.Open strSQL, objConn,3,3

and getting error again of Too few parameters. Expected 1
/lieudays.asp, line 37

'create recordset object            
     Set objRS = Server.CreateObject("ADODB.RecordSet")
     objRS.Open "timeperiod", objConn,3,3 ----this is line 37

Thanks

Denise:)
0
 
neicy62Author Commented:
Hi presto

my line 37 actually reads

objRS.open strSQL, objConn,3,3

I pressed the submit button too soon

denise:)
0
 
aprestoCommented:
Too few parameterts usually means its not picking up or recognising one of your fields specified:

http://support.microsoft.com/default.aspx?scid=kb;en-us;216425

This may help, in th emean time im gonna look into your SQL and see if i can spot the prob
0
 
aprestoCommented:
sorry if this is a silly question but i know you removed the spaces in the sql but did you do it in the database aswell?
0
 
neicy62Author Commented:
Hi apresto

Nothing is a silly question alway better to double check, yes I removed all the spaces in sql and the data base
I am double checking again.

Denise:)
0
 
aprestoCommented:
Year([date_worked]) - what does this bit mean in your sql?
0
 
aprestoCommented:
Try using this as your WHERE clause instead of what you have already, if im correct because you are using a Join you dont want to specify a source table, let the join work it out - if you get an error post back:

WHERE (((Absent_code)=7) AND ((Year([date_worked]))=[Year]));"
0
 
neicy62Author Commented:
Hi apresto...

I tried your suggestion still same error..

What I did was take my sql from my database.  What happened was that when you opened up the lieutime report it asked for a year which the user enter 2004 or whatever year they where querying and this statement would bring up all the employees that used lieu time.  Now I am trying to get it to work so that when they go to the page it ask for employee id and the year.  

so the where looked like this ---
WHERE (((timeperiod.Absent_code)=7) AND ((Year([date_worked]))=[Please enter Year));"

so that is what that was doing

Denise
0
 
aprestoCommented:
AHHH that doesnt work here! You will need to incorporate some kind of search in that case:  Try this...

     strSQL = "SELECT EmployeeInfo.ID, EmployeeInfo.First_Name, EmployeeInfo.Last_Name, EmployeeInfo.YearsofService, timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed FROM EmployeeInfo INNER JOIN timeperiod ON EmployeeInfo.ID = timeperiod.id WHERE ((timeperiod.Absent_code)=7);"

This should work try it and we will get your Year thingy sorted after
0
 
neicy62Author Commented:
Hi apresto.....

Yeah that worked you are a genious!!!!! now i have a question for you how do I get to only do one employee at a time .  If you can help me with that that would be great.  What is suppose to happen is the employee enters there id and the year they are looking at and it will show them all this infromation there id, first name, last name etc

Thanks once again for sticking with me and helping me out thanks


Denise:)

0
 
aprestoCommented:
Ok there are two things you need a HTML page with a textbox called userId, it should look like this:

<TABLE>
<FORM method="post" action="YOURASPPAGE">
<TR>
<TD><INPUT TYPE="text" name="userid"></td>
<td><INPUT TYPE="Submit" Value="Submit"></td>
</tr>
</form>
</table>

And you want your normal tags. in the asp page put this near the top of your page:

Dim userID
userID = request.form("userID)

and change your SQL to this:

strSQL = "SELECT EmployeeInfo.ID, EmployeeInfo.First_Name, EmployeeInfo.Last_Name, EmployeeInfo.YearsofService, timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed FROM EmployeeInfo INNER JOIN timeperiod ON EmployeeInfo.ID = timeperiod.id WHERE ((timeperiod.Absent_code)=7) AND employeeInfo.ID = "&usersrch&";"

Let me know how it goes
0
 
aprestoCommented:
and Make a back up before you mess with it - just incase
0
 
aprestoCommented:
sorry i got that abit wrong, but this is what the two WHOLE pages should look like, good luck. (I am presuming the ID is a number):

Page1.htm (below)

*******************************************************

<HTML>
<BODY>
<TABLE>
<FORM method="post" action="page2.asp">
<TR>
<TD><INPUT TYPE="text" name="userid"></TD>
<TD><INPUT TYPE="Submit" Value="Submit"></TD>
</TR>
</FORM>
</TABLE>

*******************************************************

Page2.asp (below)

*******************************************************
<%@ Language=VBScript %>
<% Option Explicit %>
<%  


     'open up a connection to access database  
     Dim objRS
     Dim objConn
     Dim strSQL
     Dim rs
     Dim current_datetime

     set objConn = Server.CreateObject("ADODB.Connection")
     objConn.ConnectionString= "DSN=timesheet.dsn"
     objConn.Open
     
     response.expires = 0
     set rs = server.createobject("adodb.recordset")
     current_datetime = formatdatetime(date(), 1)

    Dim usersrch
    usersrch = request.form("userid")

%>

<html>

<head>
<title>LieuDays</title>
<base target="_top">
</head>

<%      

strSQL = "SELECT EmployeeInfo.ID, EmployeeInfo.First_Name, EmployeeInfo.Last_Name, EmployeeInfo.YearsofService, timeperiod.date_worked, timeperiod.Absent_code, timeperiod.LieuTimeUsed FROM EmployeeInfo INNER JOIN timeperiod ON EmployeeInfo.ID = timeperiod.id WHERE ((timeperiod.Absent_code)=7) AND employeeInfo.ID = "&usersrch&";"
     
     'create recordset object            
     Set objRS = Server.CreateObject("ADODB.RecordSet")
     objRS.Open strSQL, objConn,3,3

     If NOT objRs.EOF Then
       While Not objRs.EOF    
       Response.Write "<TABLE><TR><TD WIDTH=112 ALIGN=LEFT><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman""COLOR=#000080>"&(objRS("id"))&"</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>"&(objRS("First_Name"))& "</FONT></B></I></TD>"    
       Response.Write "<TD WIDTH=104  ALIGN=LEFT><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>"&(objRS("Last_Name"))& "</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=104  ALIGN=RIGHT><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>"&(objRS("YearsofService"))&"</FONT></B></I></TD>"---IT DOES NOT LIKE THIS LINE--------------------
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>"&(objRS("Date_worked"))&"</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=108  ALIGN=LEFT><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>"&(objRS("Absent_code"))&"</FONT></B></I></TD>"
       Response.Write "<TD WIDTH=148  ALIGN=RIGHT><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>"&(objRS("LieuTimeused"))&"</FONT></B></I></TD></TR></Table>"    
       objRS.MoveNext
       wend
     End If

     'clean up ADO objects    
     objRS.Close
     Set objRs = Nothing
     objConn.Close
     Set objConn = Nothing
     
%>
</html>
*****************************************

If it doesnt work just post again, ill be back on tomoro im off to sleep - got a fever
0
 
neicy62Author Commented:
Hi apresto

Sorry that you are not feeling well hope that you are better today.

I have changed my code and I am getting an error
Data type mismatch in criteria expression
/lieu days.asp line 42

this is line 42
 If NOT objRs.EOF Then
       While Not objRs.EOF    
       Response.Write "<TABLE><TR><TD WIDTH=112 ALIGN=LEFT><B><I><FONT style=FONT-SIZE:12pt FACE=""Times New Roman""COLOR=#000080>"&(objRS("id"))&"</FONT></B></I></TD>"

Now in my table in access I have id as text field does that make any difference??

Thanks
0
 
neicy62Author Commented:
Hey apresto

I would like to thank you very much for sticking with me and my problems.  Everything is working now hope you are feeling better.

Have a good one

Denise:)
0
 
aprestoCommented:
Thats cool! I enjoy it, why else would i come on with a fever ;o)

Glad its working for ya!

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.