Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

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

Posted on 2006-06-28
20
Medium Priority
?
6,875 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 450 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 143

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
 
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 450 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 600 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

647 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