asked on

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

leonstryker

Which parameter is the one being changed?

vb9666

ASKER

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.

leonstryker

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?

vb9666

ASKER

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.

leonstryker

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

vb9666

ASKER

It's actually MS SQL not MySQL. How do I post a linking question?

SOLUTION

leonstryker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

leonstryker

This may be useful: http://www.webcoder.info/reference/MSSQLDataTypes.html

Leon

Guy Hengel [angelIII / a3]

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

Anthony Perkins

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.

Anthony Perkins

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)

vb9666

ASKER

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

leonstryker

Try it without the @ character in your VB code.

vb9666

ASKER

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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.

Anthony Perkins

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")

vb9666

ASKER

Finally got it working. Part of the problem was that I had the precision and scale values backwards....oops.

Thanks for the help everyone!

Anthony Perkins

>>Part of the problem was that I had the precision and scale values backwards<<
I know, I pointed that out to you.