vb9666
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("@Loan ID") = MV.LoanID
spInsert.Parameters("@Tran sactionNum ber") = MV.TransactionNumber
spInsert.Parameters("@Requ isitionNum ber") = IIf(MV.RequisitionNumber = "", Null, MV.RequisitionNumber)
If IsEmpty(MV.TransactionDate ) Then
spInsert.Parameters("@Tran sactionDat e") = Null
Else
spInsert.Parameters("@Tran sactionDat e") = FixSQLDate(MV.TransactionD ate)
End If
spInsert.Parameters("@Tran sactionTyp eID") = MV.TransactionTypeID
spInsert.Parameters("@Tran sactionAmt ") = MV.TransactionAmt
spInsert.Parameters("@Clea red") = MV.Cleared
spInsert.Parameters("@Note s") = MV.Notes
spInsert.Parameters("@Entr yDate") = vntEntryDate
spInsert.Parameters("@Entr yUser") = QDQ(UserInitials)
spInsert.Parameters("@Last SavedDate" ) = vntEntryDate
spInsert.Parameters("@Last SavedUser" ) = QDQ(UserInitials)
spInsert.Execute , , adExecuteNoRecords
MV.TransactionID = spInsert.Parameters("@Tran sactionID" )
Else
strFields = "LoanID = '" & MV.LoanID & "', " & _
"TransactionNumber = '" & MV.TransactionNumber & "', " & _
"RequisitionNumber = '" & NZI(MV.RequisitionNumber) & "', " & _
"TransactionDate = " & FixSQLDate(MV.TransactionD ate, 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("@Transa ctionNumbe r", adSmallInt, adParamInput)
x.Parameters.Append x.CreateParameter("@Requis itionNumbe r", adSmallInt, adParamInput)
x.Parameters.Append x.CreateParameter("@Transa ctionDate" , adDate, adParamInput)
x.Parameters.Append x.CreateParameter("@Transa ctionTypeI D", adSmallInt, adParamInput)
x.Parameters.Append x.CreateParameter("@Transa ctionAmt", adDouble, adParamInput)
x.Parameters.Item("@Transa ctionAmt") .Precision = 2
x.Parameters.Append x.CreateParameter("@Cleare d", adBoolean, adParamInput)
x.Parameters.Append x.CreateParameter("@Notes" , adVarChar, adParamInput, 255)
x.Parameters.Append x.CreateParameter("@EntryD ate", adDate, adParamInput)
x.Parameters.Append x.CreateParameter("@EntryU ser", adVarChar, adParamInput, 3)
x.Parameters.Append x.CreateParameter("@LastSa vedDate", adDate, adParamInput)
x.Parameters.Append x.CreateParameter("@LastSa vedUser", adVarChar, adParamInput, 3)
x.Parameters.Append x.CreateParameter("@Transa ctionID", adInteger, adParamOutput)
Set usp_InsertCFTransaction = x
End Function
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("@Loan
spInsert.Parameters("@Tran
spInsert.Parameters("@Requ
If IsEmpty(MV.TransactionDate
spInsert.Parameters("@Tran
Else
spInsert.Parameters("@Tran
End If
spInsert.Parameters("@Tran
spInsert.Parameters("@Tran
spInsert.Parameters("@Clea
spInsert.Parameters("@Note
spInsert.Parameters("@Entr
spInsert.Parameters("@Entr
spInsert.Parameters("@Last
spInsert.Parameters("@Last
spInsert.Execute , , adExecuteNoRecords
MV.TransactionID = spInsert.Parameters("@Tran
Else
strFields = "LoanID = '" & MV.LoanID & "', " & _
"TransactionNumber = '" & MV.TransactionNumber & "', " & _
"RequisitionNumber = '" & NZI(MV.RequisitionNumber) & "', " & _
"TransactionDate = " & FixSQLDate(MV.TransactionD
"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
x.Parameters.Append x.CreateParameter("@Transa
x.Parameters.Append x.CreateParameter("@Requis
x.Parameters.Append x.CreateParameter("@Transa
x.Parameters.Append x.CreateParameter("@Transa
x.Parameters.Append x.CreateParameter("@Transa
x.Parameters.Item("@Transa
x.Parameters.Append x.CreateParameter("@Cleare
x.Parameters.Append x.CreateParameter("@Notes"
x.Parameters.Append x.CreateParameter("@EntryD
x.Parameters.Append x.CreateParameter("@EntryU
x.Parameters.Append x.CreateParameter("@LastSa
x.Parameters.Append x.CreateParameter("@LastSa
x.Parameters.Append x.CreateParameter("@Transa
Set usp_InsertCFTransaction = x
End Function
Which parameter is the one being changed?
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.
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?
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.
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
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
ASKER
It's actually MS SQL not MySQL. How do I post a linking question?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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.
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.
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.
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.
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)
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("@Loan ID") = MV.LoanID
spInsert.Parameters("@Tran sactionNum ber") = MV.TransactionNumber
spInsert.Parameters("@Tran sactionAmt ") = 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("@Transa ctionNumbe r", adSmallInt, adParamInput)
x.Parameters.Append x.CreateParameter("@Transa ctionAmt", adDecimal, adParamInput)
' x.Parameters.Item("@Transa ctionAmt") .Precision = 2
Set usp_InsertCFTransaction = x
End Function
SQL SVR Stored Procedure:
--CFTransactions - usp_InsertCFTransaction
CREATE PROC dbo.usp_InsertCFTransactio n(@LoanID SmallInt,
@TransactionNumber SmallInt,
@TransactionAmt Decimal(19,2))
AS
INSERT INTO CFTransactions
(LoanID,
TransactionNumber,
TransactionAmt)
VALUES
(@LoanID,
@TransactionNumber,
@TransactionAmt)
SET @TransactionID = scope_identity()
GO
Public Function Save() As String
Set spInsert = usp_InsertCFTransaction
spInsert.Parameters("@Loan
spInsert.Parameters("@Tran
spInsert.Parameters("@Tran
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
x.Parameters.Append x.CreateParameter("@Transa
x.Parameters.Append x.CreateParameter("@Transa
' x.Parameters.Item("@Transa
Set usp_InsertCFTransaction = x
End Function
SQL SVR Stored Procedure:
--CFTransactions - usp_InsertCFTransaction
CREATE PROC dbo.usp_InsertCFTransactio
@TransactionNumber SmallInt,
@TransactionAmt Decimal(19,2))
AS
INSERT INTO CFTransactions
(LoanID,
TransactionNumber,
TransactionAmt)
VALUES
(@LoanID,
@TransactionNumber,
@TransactionAmt)
SET @TransactionID = scope_identity()
GO
Try it without the @ character in your VB code.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
@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.
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("@Transa ctionAmt")
x.Parameters("@Transaction Amt")
x.Parameters.Item("@Transa
x.Parameters("@Transaction
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!
Thanks for the help everyone!
>>Part of the problem was that I had the precision and scale values backwards<<
I know, I pointed that out to you.
I know, I pointed that out to you.