Link to home
Start Free TrialLog in
Avatar of matthewdacruz
matthewdacruzFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Invalid precision value - ASP insert into Access DB

Hi

I am getting the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC Microsoft Access Driver]Invalid precision value
/hr_v1.00/wz_emp_paydet_ins_1.asp, line 40

This is from a page that was built using the Macromedia Record Update Form Wizard.  I created a recordset to select the appropriate record using a Session variable referencing the primary key of table EMP.  Then I selected the DB Table EMP and the appropriate fields to be updated in the wizard.  When the page tries to do the update I get the error above.  The EMP.PayRate and EMP.StdHours fields are currency fields in an access DB but I only get the option to return Numeric values in the wizard.  Please can anyone provide a possible solution/workaround?



CODE: appropriate head data and the body form

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/HRDB.asp" -->
<%
Dim MM_editAction
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

' boolean to abort record edit
Dim MM_abortEdit
MM_abortEdit = false
%>
<%
' IIf implementation
Function MM_IIf(condition, ifTrue, ifFalse)
  If condition = "" Then
    MM_IIf = ifFalse
  Else
    MM_IIf = ifTrue
  End If
End Function
%>
<%
If (CStr(Request("MM_update")) = "form1") Then
  If (Not MM_abortEdit) Then
    ' execute the update
    Dim MM_editCmd

    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_HRDB_STRING
    MM_editCmd.CommandText = "UPDATE EMP SET PayFreq = ?, PayMeth = ?, PayRate = ?, StdHours = ?, NiNo = ? WHERE EmpNo = ?"
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, -1, Request.Form("PayFreq")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, -1, Request.Form("PayMeth")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 5, 1, 6, MM_IIF(Request.Form("PayRate"), Request.Form("PayRate"), null)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 5, 1, 6, MM_IIF(Request.Form("StdHours"), Request.Form("StdHours"), null)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 202, 1, 9, Request.Form("NiNo")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 5, 1, -1, MM_IIF(Request.Form("MM_recordId"), Request.Form("MM_recordId"), null)) ' adDouble
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    ' append the query string to the redirect URL
    Dim MM_editRedirectUrl
    MM_editRedirectUrl = "wz_emp_paydet_ins_0.asp"
    If (Request.QueryString <> "") Then
      If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
        MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
      Else
        MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
      End If
    End If
    Response.Redirect(MM_editRedirectUrl)
  End If
End If
%>
<%
Dim rsEMPupdPaydet1__param
rsEMPupdPaydet1__param = "-1"
If (Session("EmpNumber") <> "") Then
  rsEMPupdPaydet1__param = Session("EmpNumber")
End If
%>
<%
Dim rsEMPupdPaydet1
Dim rsEMPupdPaydet1_cmd
Dim rsEMPupdPaydet1_numRows

Set rsEMPupdPaydet1_cmd = Server.CreateObject ("ADODB.Command")
rsEMPupdPaydet1_cmd.ActiveConnection = MM_HRDB_STRING
rsEMPupdPaydet1_cmd.CommandText = "SELECT EmpNo, TaxCode, PayFreq, PayMeth, PayRate, StdHours, NiNo FROM EMP WHERE EmpNo = ?"
rsEMPupdPaydet1_cmd.Prepared = true
rsEMPupdPaydet1_cmd.Parameters.Append rsEMPupdPaydet1_cmd.CreateParameter("param1", 5, 1, -1, rsEMPupdPaydet1__param) ' adDouble

Set rsEMPupdPaydet1 = rsEMPupdPaydet1_cmd.Execute
rsEMPupdPaydet1_numRows = 0
%>
</head>
<body bgcolor="#ffffff">

   <td><img src="gfx/spacer.gif" width="1" height="31" border="0" alt="" /></td>
  </tr>
  <tr><!-- row 3 -->
   <td rowspan="3" align="left" valign="top"><!-- InstanceBeginEditable name="EditContent" -->
     <form method="POST" action="<%=MM_editAction%>" name="form1">
       <table width="100%" border="0" cellspacing="8" cellpadding="0">
         <tr align="left" valign="top">
           <td colspan="3" class="form_header">Pay Details</td>
         </tr>
         <tr>
           <td width="25%" align="left" valign="top" class="form_subhead">Pay Frequency</td>
           <td width="25%" align="left" valign="top" class="form_subhead">Rate Indicator </td>
           <td width="50%" align="left" valign="top" class="form_subhead">Salary Details </td>
         </tr>
         <tr align="left" valign="top">
           <td align="left" valign="top"><table width="100%" border="0" cellspacing="1" cellpadding="0">
             <tr>
               <td><input type="radio" value="W" name="PayFreq" <%If ("W" = rsEMPupdPaydet1.Fields.Item("PayFreq").Value) Then Response.Write("checked='checked'") : Response.Write("")%> />
                 Weekly </td>
             </tr>
             <tr>
               <td><input type="radio" value="2" name="PayFreq" <%If ("2" = rsEMPupdPaydet1.Fields.Item("PayFreq").Value) Then Response.Write("checked='checked'") : Response.Write("")%> />
                 2-Weekly </td>
             </tr>
             <tr>
               <td><input type="radio" value="4" name="PayFreq" <%If ("4" = rsEMPupdPaydet1.Fields.Item("PayFreq").Value) Then Response.Write("checked='checked'") : Response.Write("")%> />
                 4-Weekly </td>
             </tr>
             <tr>
               <td><input type="radio" value="M" name="PayFreq" <%If ("M" = rsEMPupdPaydet1.Fields.Item("PayFreq").Value) Then Response.Write("checked='checked'") : Response.Write("")%> />
                 Monthly </td>
             </tr>
           </table></td>
           <td align="left" valign="top"><table width="100%" border="0" cellspacing="1" cellpadding="0">
             <tr>
               <td><input type="radio" value="H" name="PayMeth" <%If ("H" = rsEMPupdPaydet1.Fields.Item("PayMeth").Value) Then Response.Write("checked='checked'") : Response.Write("")%> />
                 Hourly </td>
             </tr>
             <tr>
               <td><input type="radio" value="W" name="PayMeth" <%If ("W" = rsEMPupdPaydet1.Fields.Item("PayMeth").Value) Then Response.Write("checked='checked'") : Response.Write("")%> />
                 Weekly </td>
             </tr>
             <tr>
               <td><input type="radio" value="A" name="PayMeth" <%If ("A" = rsEMPupdPaydet1.Fields.Item("PayMeth").Value) Then Response.Write("checked='checked'") : Response.Write("")%> />
                 Annual </td>
             </tr>
           </table></td>
           <td align="left" valign="top"><table width="100%" border="0" cellspacing="1" cellpadding="0">
             <tr valign="baseline">
               <td nowrap="nowrap" align="right">Pay Rate:</td>
               <td><input name="PayRate" type="text" value="<%=(rsEMPupdPaydet1.Fields.Item("PayRate").Value)%>" size="8" maxlength="6" /></td>
             </tr>
             <tr valign="baseline">
               <td nowrap="nowrap" align="right">Hours per week:</td>
               <td><input name="StdHours" type="text" value="<%=(rsEMPupdPaydet1.Fields.Item("StdHours").Value)%>" size="8" maxlength="6" /></td>
             </tr>
           </table></td>
         </tr>
         <tr class="form_subhead">
           <td colspan="2" align="left" valign="top">Tax Details </td>
           <td align="left" valign="top">N.I.Details</td>
         </tr>
         <tr>
           <td colspan="2" align="left" valign="top"><table width="100%" border="0" cellspacing="1" cellpadding="0">
             <tr valign="baseline">
               <td nowrap="nowrap" align="right">Tax Code:</td>
               <td><input name="TaxCode2" type="text" value="<%=(rsEMPupdPaydet1.Fields.Item("TaxCode").Value)%>" size="8" maxlength="6" /></td>
             </tr>
           </table></td>
           <td align="left" valign="top"><table width="100%" border="0" cellspacing="1" cellpadding="0">
             <tr valign="baseline">
               <td nowrap="nowrap" align="right">N.I. Number:</td>
               <td><input name="NiNo" type="text" value="<%=(rsEMPupdPaydet1.Fields.Item("NiNo").Value)%>" size="10" maxlength="9" /></td>
             </tr>
           </table></td>
         </tr>
         <tr>
           <td align="right" valign="top">&nbsp;</td>
           <td align="right" valign="top"><input name="btnBack" type="button" id="btnBack" value="Back" onclick="history.back()" /></td>
           <td align="left" valign="top"><input name="btnNext" type="submit" id="btnNext" value="Next" /></td>
         </tr>
       </table>
       <p><input type="hidden" name="MM_update" value="form1">
         <input type="hidden" name="MM_recordId" value="<%= rsEMPupdPaydet1.Fields.Item("EmpNo").Value %>">
</p>
      </form>
   <!-- InstanceEndEditable --></td>
   <td><div align="right"><img src="gfx/layout3_r3_c2.gif" alt="" name="layout3_r3_c2" width="36" height="52" border="0" align="bottom" id="layout3_r3_c2" /></div></td>
   <td valign="bottom" background="gfx/layout3_r3_c3.gif">&nbsp;</td>
   <td><div align="left"><img src="gfx/layout3_r3_c4.gif" alt="" name="layout3_r3_c4" width="24" height="52" border="0" align="bottom" id="layout3_r3_c4" /></div></td>
   <td><img src="gfx/spacer.gif" width="1" height="52" border="0" alt="" /></td>
  </tr>
  <tr><!-- row 4 -->
   <td background="gfx/layout3_r4_c2.gif">&nbsp;</td>
   <td align="left" valign="top"><!-- InstanceBeginEditable name="EditTips" -->Employee Number: <%= Session("EmpNumber") %><!-- InstanceEndEditable --></td>
   <td background="gfx/layout3_r4_c4.gif">&nbsp;</td>
   <td><img src="gfx/spacer.gif" width="1" height="380" border="0" alt="" /></td>
  </tr>
  <tr><!-- row 5 -->
   <td><div align="right"><img src="gfx/layout3_r5_c2.gif" alt="" name="layout3_r5_c2" width="36" height="36" border="0" align="top" id="layout3_r5_c2" /></div></td>
   <td valign="top" background="gfx/layout3_r5_c3.gif">&nbsp;</td>
   <td><div align="left"><img src="gfx/layout3_r5_c4.gif" alt="" name="layout3_r5_c4" width="24" height="36" border="0" align="top" id="layout3_r5_c4" /></div></td>
   <td><img src="gfx/spacer.gif" width="1" height="36" border="0" alt="" /></td>
  </tr>
  <tr><!-- row 6 -->
   <td colspan="4" valign="top"><p class="small" style="margin:0px">
     <script language="JavaScript1.2" src="script/date.js" type="text/javascript"></script>
   </p></td>
   <td><img src="gfx/spacer.gif" width="1" height="24" border="0" alt="" /></td>
  </tr>
<!--   This table was automatically created with Macromedia Fireworks   -->
<!--   http://www.macromedia.com   -->
</table>
<!--========================= STOP COPYING THE HTML HERE =========================-->
</body>
<!-- InstanceEnd --></html>
<%
rsEMPupdPaydet1.Close()
Set rsEMPupdPaydet1 = Nothing
%>
Avatar of jrram
jrram
Flag of United States of America image
A lot of code here... can you identify line 40?
Avatar of jrram
jrram
Flag of United States of America image
Ok, I think it has something to do with the paramters section of your query:

    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_HRDB_STRING
    MM_editCmd.CommandText = "UPDATE EMP SET PayFreq = ?, PayMeth = ?, PayRate = ?, StdHours = ?, NiNo = ? WHERE EmpNo = ?"
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, -1, Request.Form("PayFreq")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, -1, Request.Form("PayMeth")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 5, 1, 6, MM_IIF(Request.Form("PayRate"), Request.Form("PayRate"), null)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 5, 1, 6, MM_IIF(Request.Form("StdHours"), Request.Form("StdHours"), null)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 202, 1, 9, Request.Form("NiNo")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 5, 1, -1, MM_IIF(Request.Form("MM_recordId"), Request.Form("MM_recordId"), null)) ' adDouble
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close
Avatar of fritz_the_blank
fritz_the_blank
Flag of United States of America image
When you have numerics, you have to specify more parameters. Here is an example:

      Set prmNumNumberNeeded = objCommand.CreateParameter
      With prmNumNumberNeeded
            .Name="@numNumberNeeded"
            .Direction = adParamInput
            .NumericScale = 2
            .Precision = 10
            .Size = 19
            .Type = adNumeric
            .Value = Request.Form("numNumberNeeded")
      End With


FtB
Avatar of matthewdacruz
matthewdacruz
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Hi jrram

Line 40 is the MM_editCmd.Execute statement in the parameters section.




Hi Fritz the blank

I'm afraid ASP is far from my first language and I am not entirely sure how your code example fits in with my code.  Can you please elaborate.
ASKER CERTIFIED SOLUTION
Avatar of fritz_the_blank
fritz_the_blank
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial