Solved

Invalid precision value - ASP insert into Access DB

Posted on 2006-06-14
7
2,337 Views
Last Modified: 2008-01-09
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
%>
0
Comment
Question by:matthewdacruz
  • 2
  • 2
7 Comments
 
LVL 13

Expert Comment

by:jrram
ID: 16903948
A lot of code here... can you identify line 40?
0
 
LVL 13

Expert Comment

by:jrram
ID: 16904015
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
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 16905730
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
0
 

Author Comment

by:matthewdacruz
ID: 16910401
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.
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 500 total points
ID: 16911536
Sure. If your parameter is an integer, then your parameter looks pretty simple:

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter.CreateParameter("@intsomeIntegerField",adInteger,adParamInput ,,varSomeIntegerValue)


However, if your value is a non-integer numeric, then you need to specify further parameters like this:

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

So, the first step is to look at your table and determine the data type of your fields involved in this operation, and then supply the necessary parameter details for those fields that are not integer.

FtB
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can not run ASP pages Windows 10 Edge browser. 5 88
Adding Datediff to staistics page 2 57
Microsoft SQL ADO Conn Issue 6 48
ASP/VB email question 4 35
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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