Problem inserting sql query into database float datatype field using SQL Transaction

I have this problem of inserting my query into database field. My code is as of below.

The @.AVERAGESCORE parameter is derived from

Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0)

and the value returned is (averagescore.toString("0.00"))

However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem?

Try Dim iAs Integer For i = 0To arraySql.Count - 1 myCommand =New SqlCommandDim consolidatedobjitemAs ConsolidatedObjItem = arraySql(i) myCommand.CommandText = sqlStr myCommand.Connection = myConnection myCommand.Transaction = myTransWith myCommand.Parameters .Add(New SqlParameter("@.AVERAGESCORE", consolidatedobjitem.getaveragescore))End With myCommand.ExecuteNonQuery()Next myTrans.Commit() myConnection.Close()Catch exAs Exception Console.Write(ex.Message) myTrans.Rollback() myConnection.Close()End Try

In your code I did not where you creat the transaction. I use the code below, it works fine. I guess you shoul put the code in to a try catch block. if you want to use it

Dim averageAs Decimal = 1.23Dim mycommandAs SqlCommand =New SqlCommand()Dim myconnAs SqlConnection =New SqlConnection(ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString) mycommand.CommandText ="Insert into Table1 values(1, @.Average)" myconn.Open() mycommand.Connection = myconnDim transAs SqlTransaction = myconn.BeginTransaction("new transaction") mycommand.Transaction = trans mycommand.Parameters.Add(New SqlParameter("@.Average", average)) mycommand.ExecuteNonQuery() mycommand.Transaction.Commit() mycommand.Connection.Close()

Hope this help|||

Thanks for replying. My problem lies with the equation I got my averagescore from.

Dim averagescoreAs Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0)average = average.toString("0.00)average =Decimal.Round(average, 2)average = Convert.toSingle(average)average = Math.round(average, 2)

I've in vain to convert the average score to a value which has 2 decimal places using various methods.

The value that I've gotten is 1.68. But I believe the value that is being inserted into the database is not that as I have

various errors. This is because I have tried hardcoding the inserted value like you did and it works.

Is there any other way to resolve this problem?

|||

The error that I've got most of the times are,

System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 18 ("@.AVERAGESCORE"): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Hope this gives you a clearer idea, my guess is probably a rounding off error, but I just do not have any ideas left. I have even tried changing the database field datatype to decimal(10,2) to no effect.

|||

You could use SQL Server aggregate function Average with Decimal so you can set precision and scale. Try the link below for T-SQL Average aggregate function. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms173454.aspx

|||I afraid the AVG aggregate function woudn't help much in my case. Thanks anyway.|||

Can you print the type of averagescore. It might be become a decimal number after those manipulations, I do not think decimal can fit into float in sql server.

Hope this help

alienated:

The error that I've got most of the times are,

System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 18 ("@.AVERAGESCORE"): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Hope this gives you a clearer idea, my guess is probably a rounding off error, but I just do not have any ideas left. I have even tried changing the database field datatype to decimal(10,2) to no effect.

|||Your error message is saying you are passing the wrong data type to the data protocol Tabular Data Stream. You have to convert Float to Decimal before passing it to SQL Server because there is only implicit conversion between Decimal and Numeric and Float and Real, any data passing between both pairs must be explict conversion. The reason is you can set precision and scale with the former and not the later. Hope this helps.