Solved

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

Posted on 2006-06-28
20
6,792 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:vb9666
  • 6
  • 6
  • 6
  • +1
20 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 17004312
Which parameter is the one being changed?
0
 

Author Comment

by:vb9666
ID: 17004423
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 17004464
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
 

Author Comment

by:vb9666
ID: 17004477
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 17004511
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
 

Author Comment

by:vb9666
ID: 17004528
It's actually MS SQL not MySQL.  How do I post a linking question?
0
 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 150 total points
ID: 17004564
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 17004568
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17004753
>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17005540
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17005546
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
 

Author Comment

by:vb9666
ID: 17009058
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 17009166
Try it without the @ character in your VB code.
0
 

Author Comment

by:vb9666
ID: 17009232
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 17009283
<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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 17009450
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17009473
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17009513
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
 

Author Comment

by:vb9666
ID: 17009563
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17014116
>>Part of the problem was that I had the precision and scale values backwards<<
I know, I pointed that out to you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

705 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

22 Experts available now in Live!

Get 1:1 Help Now