Solved

Invalid precision value - ASP insert into Access DB

Posted on 2006-06-14
7
2,329 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
Comment Utility
A lot of code here... can you identify line 40?
0
 
LVL 13

Expert Comment

by:jrram
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 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

9 Experts available now in Live!

Get 1:1 Help Now