INSERT record into SQL table using VB CreateParameter is saving data incorrectly

I have some VB code that is using the CreateParameter method  and the Excecute command method to insert records into a MSSQL table. The record inserts okay however a float value is changed.  The number entered is 286162.80 but it gets saved as 286162.8125.  It's rounding up even though the correct value is being passed to the database.  Some how the number is being modified.  This only happens some of the time.  Does anyone know what is causing the number to be modified?

Here's some of the code below.  It's the TransactionAmt field that is being modified.  I have tried various data types for the table as well for the parameter but nothing seems to resolve the issue.  Any suggestions anyone?  HELP!


Public Function Save() As String
    ' This function saves a Transaction that is either new or existing.
    ' If the record is new a stored procedure is used to return the new TransactionID.
    Dim strSQL As String
    Dim strFields As String
    Dim strWhere As String
    Dim spInsert As ADODB.Command
    Dim vntEntryDate As Variant
   
    On Error GoTo LoadError
    vntEntryDate = Now
   
    If mblnNew = True Then  
        ' Use a stored procedure
        ' Returns the new TransactionID
        Set spInsert = usp_InsertCFTransaction
        spInsert.Parameters("@LoanID") = MV.LoanID
        spInsert.Parameters("@TransactionNumber") = MV.TransactionNumber
        spInsert.Parameters("@RequisitionNumber") = IIf(MV.RequisitionNumber = "", Null, MV.RequisitionNumber)
        If IsEmpty(MV.TransactionDate) Then
            spInsert.Parameters("@TransactionDate") = Null
        Else
            spInsert.Parameters("@TransactionDate") = FixSQLDate(MV.TransactionDate)
        End If
        spInsert.Parameters("@TransactionTypeID") = MV.TransactionTypeID
        spInsert.Parameters("@TransactionAmt") = MV.TransactionAmt
        spInsert.Parameters("@Cleared") = MV.Cleared
        spInsert.Parameters("@Notes") = MV.Notes
        spInsert.Parameters("@EntryDate") = vntEntryDate
        spInsert.Parameters("@EntryUser") = QDQ(UserInitials)
        spInsert.Parameters("@LastSavedDate") = vntEntryDate
        spInsert.Parameters("@LastSavedUser") = QDQ(UserInitials)
       
        spInsert.Execute , , adExecuteNoRecords
        MV.TransactionID = spInsert.Parameters("@TransactionID")
    Else
        strFields = "LoanID = '" & MV.LoanID & "', " & _
                    "TransactionNumber = '" & MV.TransactionNumber & "', " & _
                    "RequisitionNumber = '" & NZI(MV.RequisitionNumber) & "', " & _
                    "TransactionDate = " & FixSQLDate(MV.TransactionDate, True) & ", " & _
                    "TransactionTypeID = '" & MV.TransactionTypeID & "', " & _
                    "TransactionAmt = " & MV.TransactionAmt & ", " & _
                    "Cleared = '" & Abs(MV.Cleared) & "', " & _
                    "Notes = '" & QDQ(MV.Notes) & "', " & _
                    "LastSavedDate = '" & vntEntryDate & "', " & _
                    "LastSavedUser = '" & QDQ(UserInitials) & "' "
   
        strWhere = "WHERE TransactionID = '" & MV.TransactionID & "' "
       
        strSQL = "UPDATE CFTransactions SET " & strFields & strWhere
       
        SQLSVRHEFA.Execute strSQL
    End If
    Save = MV.TransactionID
    mblnNew = False
    mblnChanged = False
    Exit Function
   
LoadError:
    Save = ""
End Function

Public Function usp_InsertCFTransaction() As ADODB.Command
    ' This function is used to insert a new Transaction record into the table CFTransactions
    ' The Transaction for the new record is returned.  The stored procedure
    ' usp_InsertCFTransaction is used.
    Dim x As New ADODB.Command
   
    InitializeCommand x, "usp_InsertCFTransaction"
    x.Parameters.Append x.CreateParameter("@LoanID", adInteger, adParamInput)
    x.Parameters.Append x.CreateParameter("@TransactionNumber", adSmallInt, adParamInput)
    x.Parameters.Append x.CreateParameter("@RequisitionNumber", adSmallInt, adParamInput)
    x.Parameters.Append x.CreateParameter("@TransactionDate", adDate, adParamInput)
    x.Parameters.Append x.CreateParameter("@TransactionTypeID", adSmallInt, adParamInput)
    x.Parameters.Append x.CreateParameter("@TransactionAmt", adDouble, adParamInput)
    x.Parameters.Item("@TransactionAmt").Precision = 2
    x.Parameters.Append x.CreateParameter("@Cleared", adBoolean, adParamInput)
    x.Parameters.Append x.CreateParameter("@Notes", adVarChar, adParamInput, 255)
    x.Parameters.Append x.CreateParameter("@EntryDate", adDate, adParamInput)
    x.Parameters.Append x.CreateParameter("@EntryUser", adVarChar, adParamInput, 3)
    x.Parameters.Append x.CreateParameter("@LastSavedDate", adDate, adParamInput)
    x.Parameters.Append x.CreateParameter("@LastSavedUser", adVarChar, adParamInput, 3)
    x.Parameters.Append x.CreateParameter("@TransactionID", adInteger, adParamOutput)

    Set usp_InsertCFTransaction = x
End Function
vb9666Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
<superflues content removed by angelIII>

Public Function usp_InsertCFTransaction() As ADODB.Command
    ' This function is used to insert a new Transaction record into the table CFTransactions
    ' The Transaction for the new record is returned.  The stored procedure
    ' usp_InsertCFTransaction is used.
    Dim x As New ADODB.Command
    dim p as adodb.parameter
   
    InitializeCommand x, "usp_InsertCFTransaction"
    x.Parameters.Append x.CreateParameter("@LoanID", adSmallint, adParamInput)
    x.Parameters.Append x.CreateParameter("@TransactionNumber", adSmallInt, adParamInput)
    set p = x.CreateParameter("@TransactionAmt", adDecimal, adParamInput)
    p.Scale = 19
    p.Precision = 2
    x.Parameters.Append p

    Set usp_InsertCFTransaction = x
End Function
0
 
leonstrykerCommented:
Which parameter is the one being changed?
0
 
vb9666Author Commented:
It's the @TransactionAmt.  I added the Precision in an attempt to fix the problem but that' didn't seem to help!  I've tried different data types as well adSingle and adCurrency as well.  Neither worked.  So I'm not sure if the problem is on the VB or database side.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
leonstrykerCommented:
If you are sure its being passed correctly from VB, then the problem is in the database.  That is the datatype declared as in the database?
0
 
vb9666Author Commented:
There is no double data type in SQL.  It's set as float but I had it as money at one time.  Neither of these seem to work.
0
 
leonstrykerCommented:
Sometimes the best datatype to use in the database is varchar, even for numbers.

I am not familiar enough with MySQL. Try posting a linking questino in that area. Someone may know if this is a known issue.

Leon
0
 
vb9666Author Commented:
It's actually MS SQL not MySQL.  How do I post a linking question?
0
 
leonstrykerConnect With a Mentor Commented:
Sorry, I don't know where I got MySQL from.  Have you tried decimal data type?

To post a linking question, you just post a questino in adifferent area for 20 points with a link to this one.  Once this question is answered you delete the linking question.

Leon
0
 
leonstrykerCommented:
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>a float value is changed
DON'T USE FLOATS !!! They are only useful in high-precision math calculations, but not for storing amounts like that.

use decimal/numeric instead, and the problem will go away.
0
 
Anthony PerkinsCommented:
Or money/smallmoney.  To repeat angelIII  suggestion in case it was not clear:
Never use float or real they are approximate values.  This is analogous to using double and single in VB in the sense that both sets of data types use the IEEE numeric format and are approximate values at best and subject to the same problems you are witnessing.
0
 
Anthony PerkinsCommented:
If you do use Decimal (or numerc) instead of money or small money make sure to set the precision and numeric scale.  This does not apply to money or smallmoney (or float or real for that matter)
0
 
vb9666Author Commented:
I changed this to use Decimal with Precision = 2 and now on the save it's saying the "Precision is invalid."  I had commended out the VB code setting the precision and when I added it back in I got an error that says "Invalid character value for cast specification."  The code (shortened a bit to make it easier to follow) is below including the stored procedure.  Can anyone tell me what I am doing wrong?

Public Function Save() As String  
        Set spInsert = usp_InsertCFTransaction
        spInsert.Parameters("@LoanID") = MV.LoanID
        spInsert.Parameters("@TransactionNumber") = MV.TransactionNumber
        spInsert.Parameters("@TransactionAmt") = CDec(MV.TransactionAmt)

        spInsert.Execute , , adExecuteNoRecords
End Function

Public Function usp_InsertCFTransaction() As ADODB.Command
    ' This function is used to insert a new Transaction record into the table CFTransactions
    ' The Transaction for the new record is returned.  The stored procedure
    ' usp_InsertCFTransaction is used.
    Dim x As New ADODB.Command
   
    InitializeCommand x, "usp_InsertCFTransaction"
    x.Parameters.Append x.CreateParameter("@LoanID", adInteger, adParamInput)
    x.Parameters.Append x.CreateParameter("@TransactionNumber", adSmallInt, adParamInput)
    x.Parameters.Append x.CreateParameter("@TransactionAmt", adDecimal, adParamInput)
'    x.Parameters.Item("@TransactionAmt").Precision = 2

    Set usp_InsertCFTransaction = x
End Function

SQL SVR Stored Procedure:

--CFTransactions - usp_InsertCFTransaction
CREATE PROC dbo.usp_InsertCFTransaction(@LoanID SmallInt,
                              @TransactionNumber SmallInt,
                              @TransactionAmt Decimal(19,2))
AS
INSERT INTO CFTransactions
      (LoanID,
      TransactionNumber,
      TransactionAmt)
VALUES
      (@LoanID,
      @TransactionNumber,
      @TransactionAmt)
SET @TransactionID = scope_identity()
GO
0
 
leonstrykerCommented:
Try it without the @ character in your VB code.
0
 
vb9666Author Commented:
I'm not sure which specific @ you are referring to.  If I take them out in the save, it says "Item cannot be found in the collection corresponding to the requested name or ordinal.  And if I take out of the usp_InsertCFTransaction function as well then the stored procedure won't.  Please explain more.
0
 
Anthony PerkinsConnect With a Mentor Commented:
Try it this way:

Public Function usp_InsertCFTransaction() As ADODB.Command
    ' This function is used to insert a new Transaction record into the table CFTransactions
    ' The Transaction for the new record is returned.  The stored procedure
    ' usp_InsertCFTransaction is used.
    Dim x As ADODB.Command
   
    Set x = New ADODB.Command
   
    InitializeCommand x, "usp_InsertCFTransaction"
    x.Parameters.Append x.CreateParameter("@LoanID", adSmallInt, adParamInput)
    x.Parameters.Append x.CreateParameter("@TransactionNumber", adSmallInt, adParamInput)
    x.Parameters.Append x.CreateParameter("@TransactionAmt", adDecimal, adParamInput)
    x.Parameters(@TransactionAmt").Precision = 19
    x.Parameters(@TransactionAmt").NumericScale = 2
      
    Set usp_InsertCFTransaction = x
End Function
0
 
Anthony PerkinsCommented:
So that it is clear this:
@TransactionAmt Decimal(19,2))

Means that @TransactionAmt has a Precision of 19 and a Scale of 2


I should also add I have no idea what the function InitializeCommand actually does.  I presume just set the ActiveConnection, CommandText and CommandType.
0
 
Anthony PerkinsCommented:
P.S. and sorry for the multiple posts.  Since Item is the default property for the Parameters collection, the following are equivalent:
x.Parameters.Item("@TransactionAmt")
x.Parameters("@TransactionAmt")
0
 
vb9666Author Commented:
Finally got it working.  Part of the problem was that I had the precision and scale values backwards....oops.

Thanks for the help everyone!
0
 
Anthony PerkinsCommented:
>>Part of the problem was that I had the precision and scale values backwards<<
I know, I pointed that out to you.
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.