Store decimal datatypes in SQL with VB.Net webapp

[Kai77]

Hi,

I have a datafield in my database that has the sql datatype decimal (with a precision of 10 and scale of 2).

When I try to add a something to this field through my vb.net webapp by using

Dim strValue as string
strValue = "4999.99"
Dim decValue as decimal = CDec(strValue)

And I would store decDecimal in the database, it would save it as 499999.

However if I enter the value of strValue using enterprise manager, it did save it correctly.

How am I suppose to save this?

 

[ca8msm]

And I would store decDecimal in the database

How are you doing this? If you are not using Parameters to add your data to the database then I would suggest doing so. Also a sample pice of code that you are using would help.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[Kai77]

like so:

.Parameters.Add("@dec_responsedecvalue", SqlDbType.Decimal, 9).Value = decValue

 

[Kai77]

The function in which the above linecode resides:

Public Shared Function Add(ByVal obj_nbo_AnswersDetails as nbo_AnswersDetails) As Integer
Dim obj_Connbo_surveyDB As New SqlConnection(ConfigurationSettings.AppSettings("str_ConnectionStringDAL2"))
Dim obj_Cmdnbo_surveyDB As New SqlCommand("spr_answers_Insert", obj_Connbo_surveyDB)
obj_Cmdnbo_surveyDB.CommandType = CommandType.StoredProcedure
obj_Connbo_surveyDB.Open()

With obj_Cmdnbo_surveyDB
.Parameters.Add("RETURN_VALUE", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue
.Parameters.Add("@int_userid", SqlDbType.Int, 4).Value = obj_nbo_AnswersDetails.int_Userid
.Parameters.Add("@int_quid", SqlDbType.Int, 4).Value = obj_nbo_AnswersDetails.int_Quid
.Parameters.Add("@int_squid", SqlDbType.Int, 4).Value = obj_nbo_AnswersDetails.int_Squid
.Parameters.Add("@int_optgroupid", SqlDbType.Int, 4).Value = obj_nbo_AnswersDetails.int_Optgroupid
.Parameters.Add("@int_optid", SqlDbType.Int, 4).Value = obj_nbo_AnswersDetails.int_Optid
.Parameters.Add("@int_validationid", SqlDbType.Int, 4).Value = obj_nbo_AnswersDetails.int_Validationid
.Parameters.Add("@int_columnid", SqlDbType.Int, 4).Value = obj_nbo_AnswersDetails.int_Columnid
.Parameters.Add("@int_surveyid", SqlDbType.Int, 4).Value = obj_nbo_AnswersDetails.int_Surveyid
.Parameters.Add("@str_responsecharvalue", SqlDbType.VarChar, 500).Value = obj_nbo_AnswersDetails.str_Responsecharvalue
.Parameters.Add("@dec_responsedecvalue", SqlDbType.Decimal, 9).Value = obj_nbo_AnswersDetails.dec_Responsedecvalue
.Parameters.Add("@bln_nonresponse", SqlDbType.Bit, 1).Value = obj_nbo_AnswersDetails.bln_Nonresponse

.ExecuteNonQuery() ' Execute query
End With
obj_Connbo_surveyDB.Close()
obj_Cmdnbo_surveyDB.Dispose()
Return DbCInt(obj_Cmdnbo_surveyDB.Parameters("RETURN_VALUE").Value())
End Function

 

[Kai77]

In the function you can see:

.Parameters.Add("@dec_responsedecvalue", SqlDbType.Decimal, 9).Value = obj_nbo_AnswersDetails.dec_Responsedecvalue

where
obj_nbo_AnswersDetails.dec_Responsedecvalue is basically decValue.

 

[ca8msm]

Hmm..I just did a simple example using:

        Dim MyCommand As New SqlCommand
        MyCommand.Parameters.Add("@dec_responsedecvalue", SqlDbType.Decimal, 9).Value = 4999.99

and it worked correctly. Have you stepped through your code and made sure that "obj_nbo_AnswersDetails.dec_Responsedecvalue" is the correct value (you could try hard-coding the value to 4999.99 whilst you are testing to see of that works).

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[Kai77]

Yes, i know hardcoding would work, I am guessing that a decimal in VB.net is not the same as decimal in MS SQL.

If you were to use

Dim MyCommand As New SqlCommand
MyCommand.Parameters.Add("@dec_responsedecvalue", SqlDbType.Decimal, 9).Value = cdec(4999.99)

You would see my problem. I could always just insert the value as a string.

 

[ca8msm]

If it inserts correctly by passing the string value then I would just go with that.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.