Updating a database

Gary_M · November 6th, 2003, 05:19 AM

Can anyone see where my error is? its to do with my INSERT INTO statement

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

<%@ Language=VBScript %>
<html>

<head>
<SCRIPT LANGUAGE = "JavaScript">
function Disab()
{
    frm=document.forms[0]
        if(frm.cbox.checked)
        {
            frm.Button1.disabled=false
            }
        else
            {
            frm.Button1.disabled=true
        }
}
</SCRIPT>
<meta http-equiv="Content-Language" content="en-gb">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>ECS</title>
</head>
<bodyhidden>

<table border="1" width="605" height="0" align = "center" cellspacing="0" cellpadding="0">
    <tr>
    <td width="202" height="36" align="center">
      <p align="center"><b>UK Number</b>
      <td width="188" height="36" align="center">
      <p align="center"><b>Forename</b>
      <td width="164" height="36" align="center">
      <b>Surname</b>
      <td width="174" height="36" align="center">
      <b>Department</b>
      <td width="215" height="36" align="center">
      <p align="center"><b>Image</b></tr>
   <%
    SQL = "SELECT * FROM Emps ORDER BY FirstName"

        set conn = server.createobject("ADODB.Connection")
        conn.ConnectionTimeout=5
        conn.CommandTimeout=5
        conn.open "ECS"
        set rs = conn.execute(SQL)

        rs.movefirst
        do while not rs.eof
            rs.movefirst
                do while not rs.eof
                    number = Request.ServerVariables("REMOTE_USER")
                       number = Right(number, 7)
                   if rs("UKNumber") = number then
                       FName = rs("FirstName")
                       SName = rs("Surname")
                       Dept = rs("Department")
                       ImageFileName = rs("Image")
                   end if
            rs.movenext
           loop
       %>
<tr>
<%Set fs = CreateObject("Scripting.FileSystemObject")%>
    <td width="202" height="36" align="center">
      <p align="center"><%=number%></td>
      <td width="188" height="36" align="center">
      <p align="center"><%=FName%></td>
      <td width="164" height="36" align="center">
      <p align="center"><%=SName%></td>
      <td width="174" height="36" align="center">
      <p align="center"><%=Dept%></td>
      <td width="215" height="36" align="center">
      <p align="center"><p>
      <%if fs.fileexists("d:\UKIntranet\telephone\images\empl oyees\" & ImageFileName) then
                                     'if there is a file then display it
                                        Response.Write "<IMG SRC=../../telephone/images/employees/"
                                        Response.Write ImageFileName
                                        Response.Write " width=50\>"
                                else
                                        response.write "no picture found"
                                end if
                                %></p>
      </tr>
      <%
            loop
            rs.close
            set rs = nothing
            conn.close
            set conn = nothing
        %>

</table>
<br>
<IFRAME NAME="POLICY" SRC="TEST.HTM" width="100%" height="400" scrolling="YES" frameborder="0"></IFRAME>
<br>
<form action="ecs.asp?action=Accept_Policy" method="post" >
    <p align="center">
<input name="cbox" type="checkbox" id="cbox" value="checkbox" onClick=Disab();>    
  Do you accept the policy
  <% 'Select Case Request.QueryString("action")
        'Case "Accept_Policy" 'saves status%>
    <%
    set conn = server.createobject("ADODB.Connection")
    conn.ConnectionTimeout=5
    conn.CommandTimeout=5
    conn.open "ecs"
    SQL = "SELECT * FROM ECSDetails;"
    SQL2 = "SELECT * FROM EmployeeVersion;"
    set rs = conn.execute(SQL)
    set rs2 = conn.execute(SQL2)
    number = Request.ServerVariables("REMOTE_USER")
    number = Right(number, 7)

    do while not rs.eof
           rs.moveFirst
               if rs2("UKNumber") = number then
                   EVers = rs("ECSVersion")
               end if
           rs.movenext
       loop

        baseSQL="INSERT into EmployeeVersion (UKNumber, ECSVersion)"
        valueSQL="VALUES (number, EVers) "

            addSQL=baseSQL + valueSQL
            'Response.write "<BR><BR>" & addSQL

            set conn = server.createobject("ADODB.Connection")
            conn.ConnectionTimeout=5
            conn.CommandTimeout=5
            conn.open "ecs"

            conn.execute(addSQL) %>
  <p align="center"><input type="submit" value="Button" name="Button1" language="JavaScript" onclick="self.location.href = 'ecs.asp?action=list'" disabled></p>
  </form>

</html>

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

The error i get is

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

/ict/ECS/ecs.asp, line 135

planoie · November 6th, 2003, 11:30 AM

I think you are looking for this:

        valueSQL="VALUES (" & number & ", " & EVers & ") "

Something else I noticed that concerns me...
This code would seem to cause an endless loop:
       do while not rs.eof
           rs.moveFirst
               if rs2("UKNumber") = number then
                   EVers = rs("ECSVersion")
               end if
           rs.movenext
       loop

I don't think rs.moveFirst should be where it is. Did you mean to put that before the do while? I am surprised you haven't encountered a problem with this.

Peter
------------------------------------------------------
Work smarter, not harder.

Gary_M · November 7th, 2003, 05:01 AM

every thing works well with that coding apart from all i am getting in my database now is the servervariables. it is not picking up the EVers. i tried moving the rs.movenext around a bit but no good.
any other help?

planoie · November 7th, 2003, 10:20 AM

recordset.MoveNext() should almost always be at the end. Can you check that you are get legitimate data from rs2("UKNumbers"). It sounds like that check might be failing.

Is this page dealing with only one user at a time? You have a lot of loops in your code, and from what I can tell you are looping to find a matching value for something coming from "REMOTE_USER".

Can you post a simple description of what this page does? I might be able to provide several suggestions to help you optimize the code which might also help to straighten out any problems you are having.

Peter
------------------------------------------------------
Work smarter, not harder.

Jonax · November 7th, 2003, 11:03 AM

Provided that the two db-fields in question are of type INT, try replacing the line:

Code:

valueSQL="VALUES (number, EVers) "

with

Code:

valueSQL="VALUES (" & number & ", " & EVers & ") "