Error converting data type nvarchar to decimal Error?

Posted on 2014-07-29
Last Modified: 2014-08-01
I had another post similar to this and closed prematurely. I really don't know what I'm doing wrong, so...I've posted the Stored Procedure and the VB.Net Code. Not sure how to debug this either. When I run the app in debug mode, the Function "AddUpdate_TEST_HEADER_Load" runs all the way thru until the ExecuteNonQuery statement is executed at which point it throws the above exception. Here is the code:

    Public Function AddUpdate_TEST_HEADER_Load(ByVal iMain_ID As Integer, ByRef dtHEADER_Load As DataTable, ByRef tran As SqlTransaction, ByRef cnn As SqlConnection, ByRef EH As ErrorHandling.ErrorHandler)
            EH.Success = False

            Dim row As DataRow = dtHEADER_Load.Rows(0)

            cmd = New SqlCommand("", cnn)
            cmd.Transaction = tran
            cmd.Parameters.AddWithValue("@main_ID", iMain_ID)
            cmd.Parameters.AddWithValue("@seqNO", CInt(row("seqNO")))
            cmd.Parameters.AddWithValue("@channel_ID", CInt(row("channel_ID")))
            cmd.Parameters.AddWithValue("@direction_ID", CInt(row("direction_ID")))
            cmd.Parameters.AddWithValue("@angle_ID", CInt(row("angle_ID")))
            cmd.Parameters.AddWithValue("@numberOfRuns", row("numberOfRuns")).Value = If(Not DBNull.Value.Equals(row("numberOfRuns")), CInt(row("numberOfRuns")), DBNull.Value)
            cmd.Parameters.AddWithValue("@load_ID", row("load_ID")).Value = If(Not DBNull.Value.Equals(row("load_ID")), 0, DBNull.Value)
            cmd.Parameters.AddWithValue("@loadUnitType_ID", row("loadUnitType_ID")).Value = If(Not DBNull.Value.Equals(row("loadUnitType_ID")), CInt(row("loadUnitType_ID")), DBNull.Value)
            cmd.Parameters.AddWithValue("@loadUnit_ID", row("loadUnit_ID")).Value = If(Not DBNull.Value.Equals(row("loadUnit_ID")), CInt(row("loadUnit_ID")), DBNull.Value)
            cmd.Parameters.AddWithValue("@loadDecimalPlaces", row("loadDecimalPlaces")).Value = If(Not DBNull.Value.Equals(row("loadDecimalPlaces")), CInt(row("loadDecimalPlaces")), DBNull.Value)
            cmd.Parameters.AddWithValue("@loadingPoints", row("loadingPoints")).Value = If(Not DBNull.Value.Equals(row("loadingPoints")), CInt(row("loadingPoints")), DBNull.Value)
            cmd.Parameters.AddWithValue("@outputUnitType_ID", row("outputUnitType_ID")).Value = If(DBNull.Value.Equals(row("outputUnitType_ID")), CInt(row("outputUnitType_ID")), DBNull.Value)
            cmd.Parameters.AddWithValue("@outputUnit_ID", row("outputUnit_ID")).Value = If(Not DBNull.Value.Equals(row("outputUnit_ID")), CInt(row("outputUnit_ID")), DBNull.Value)
            cmd.Parameters.AddWithValue("@outputDecimalPlaces", row("outputDecimalPlaces")).Value = If(Not DBNull.Value.Equals(row("outputDecimalPlaces")), CInt(row("outputDecimalPlaces")), DBNull.Value)
            cmd.Parameters.AddWithValue("@analog", row("analog")).Value = If(Not DBNull.Value.Equals(row("analog")), CInt(row("analog")), DBNull.Value)
            cmd.Parameters.AddWithValue("@analogUnit_ID", row("analogUnit_ID")).Value = If(Not DBNull.Value.Equals(row("analogUnit_ID")), CInt(row("analogUnit_ID")), DBNull.Value)
            cmd.Parameters.AddWithValue("@analogDecimalPlaces", row("analogDecimalPlaces")).Value = If(Not DBNull.Value.Equals(row("analogDecimalPlaces")), CInt(row("analogDecimalPlaces")), DBNull.Value)
            cmd.Parameters.AddWithValue("@conversion", row("conversion")).Value = If(Not DBNull.Value.Equals(row("conversion")), CInt(row("conversion")), DBNull.Value)
            cmd.Parameters.AddWithValue("@conversionUnit_ID", row("conversionUnit_ID")).Value = If(Not DBNull.Value.Equals(row("conversionUnit_ID")), CInt(row("conversionUnit_ID")), DBNull.Value)
            cmd.Parameters.AddWithValue("@conversionDecimalPlaces", row("conversionDecimalPlaces")).Value = If(Not DBNull.Value.Equals(row("conversionDecimalPlaces")), CInt(row("conversionDecimalPlaces")), DBNull.Value)
            cmd.Parameters.AddWithValue("@hysteresis", row("hysteresis")).Value = If(Not DBNull.Value.Equals(row("hysteresis")), CInt(row("hysteresis")), DBNull.Value)
            cmd.Parameters.AddWithValue("@amplified", row("amplified")).Value = If(Not DBNull.Value.Equals(row("amplified")), CInt(row("amplified")), DBNull.Value)
            cmd.Parameters.AddWithValue("@tare", row("tare")).Value = If(Not DBNull.Value.Equals(row("tare")), CInt(row("tare")), DBNull.Value)
            cmd.Parameters.AddWithValue("@includeOffset", row("includeOffset")).Value = If(Not DBNull.Value.Equals(row("includeOffset")), CInt(row("includeOffset")), DBNull.Value)
            cmd.Parameters.AddWithValue("@printOnCert", row("printOnCert")).Value = If(Not DBNull.Value.Equals(row("printOnCert")), CInt(row("printOnCert")), DBNull.Value)
            cmd.Parameters.AddWithValue("@temperature", row("temperature")).Value = If(Not DBNull.Value.Equals(row("temperature")), row("temperature"), DBNull.Value)
            cmd.Parameters.AddWithValue("@humidity", row("humidity")).Value = If(Not DBNull.Value.Equals(row("humidity")), row("humidity"), DBNull.Value)
            cmd.Parameters.AddWithValue("@startTime", row("startTime")).Value = If(Not DBNull.Value.Equals(row("startTime")), CDate(row("startTime")), DBNull.Value)
            cmd.Parameters.AddWithValue("@endTime", row("endTime")).Value = If(Not DBNull.Value.Equals(row("endTime")), CDate(row("endTime")), DBNull.Value)
            cmd.Parameters.AddWithValue("@createdBy_ID", row("createdBy_ID")).Value = If(Not DBNull.Value.Equals(row("createdBy_ID")), CInt(row("createdBy_ID")), DBNull.Value)
            cmd.Parameters.AddWithValue("@updatedBy_ID", row("updatedBy_ID")).Value = If(Not DBNull.Value.Equals(row("updatedBy_ID")), CInt(row("updatedBy_ID")), DBNull.Value)
            cmd.Parameters.AddWithValue("@load", row("load")).Value = If(Not DBNull.Value.Equals(row("load")), row("load"), DBNull.Value)
            cmd.Parameters.AddWithValue("@outputFirstZero", row("outputFirstZero")).Value = If(Not DBNull.Value.Equals(row("outputFirstZero")), row("outputFirstZero"), DBNull.Value)
            cmd.Parameters.AddWithValue("@outputFinalZero", row("outputFinalZero")).Value = If(Not DBNull.Value.Equals(row("outputFinalZero")), row("outputFinalZero"), DBNull.Value)
            cmd.Parameters.AddWithValue("@outputZeroReturn", row("outputZeroReturn")).Value = If(Not DBNull.Value.Equals(row("outputZeroReturn")), row("outputZeroReturn"), DBNull.Value)
            cmd.Parameters.AddWithValue("@analogFirstZero", row("analogFirstZero")).Value = If(Not DBNull.Value.Equals(row("analogFirstZero")), row("analogFirstZero"), DBNull.Value)
            cmd.Parameters.AddWithValue("@analogFinalZero", row("analogFinalZero")).Value = If(Not DBNull.Value.Equals(row("analogFinalZero")), row("analogFinalZero"), DBNull.Value)
            cmd.Parameters.AddWithValue("@analogZeroReturn", row("analogZeroReturn")).Value = If(Not DBNull.Value.Equals(row("analogZeroReturn")), row("analogZeroReturn"), DBNull.Value)
            cmd.Parameters.AddWithValue("@nominalExcitation", row("nominalExcitation")).Value = If(Not DBNull.Value.Equals(row("nominalExcitation")), row("nominalExcitation"), DBNull.Value)
            cmd.Parameters.AddWithValue("@measuredExcitation", row("measuredExcitation")).Value = If(Not DBNull.Value.Equals(row("measuredExcitation")), row("measuredExcitation"), DBNull.Value)
            cmd.Parameters.AddWithValue("@maxOutput", row("maxOutput")).Value = If(Not DBNull.Value.Equals(row("maxOutput")), row("maxOutput"), DBNull.Value)
            cmd.Parameters.AddWithValue("@maxNonLinearity", row("maxNonLinearity")).Value = If(Not DBNull.Value.Equals(row("maxNonLinearity")), row("maxNonLinearity"), DBNull.Value)
            cmd.Parameters.AddWithValue("@maxHysteresis", row("maxHysteresis")).Value = If(Not DBNull.Value.Equals(row("maxHysteresis")), row("maxHysteresis"), DBNull.Value)
            cmd.Parameters("@load_ID").Direction = ParameterDirection.Output
            cmd.CommandText = "spAddUpdate_TEST_HEADER_Load"
            cmd.CommandType = CommandType.StoredProcedure
            EH.integerType = cmd.Parameters("@load_ID").Value
            EH.Success = True

            EH.ErrorMessage = ""

        Catch ex As Exception
            EH.ErrorMessage = "CalDataIO/AddUpdate_TEST_HEADER_Load() - " & ex.Message & "...Contact Engineering!" & "~E"
        End Try

        Return EH
    End Function

Open in new window

Here is my Stored Procedure:

	@main_ID				INT=NULL,
    @seqNO					INT=NULL,
    @channel_ID				INT=NULL,
    @direction_ID				INT=NULL,
    @angle_ID					INT=NULL,
    @numberOfRuns			INT=NULL,
    @load_ID					INT OUTPUT,
    @load						DECIMAL(18,9)=NULL,
    @loadUnitType_ID			INT=NULL,
    @loadUnit_ID				INT=NULL,
    @loadDecimalPlaces			INT=NULL,
    @loadingPoints				INT=NULL,
    @outputUnitType_ID			INT=NULL,
    @outputUnit_ID				INT=NULL,
    @outputDecimalPlaces		INT=NULL,
    @outputFirstZero			DECIMAL(18,9)=NULL,
    @outputFinalZero			DECIMAL(18,9)=NULL,
    @outputZeroReturn			DECIMAL(18,9)=NULL,
    @analog					INT=NULL,
    @analogUnit_ID				INT=NULL,
    @analogDecimalPlaces		INT=NULL,
    @analogFirstZero			DECIMAL(18,9)=NULL,
    @analogFinalZero			DECIMAL(18,9)=NULL,
    @analogZeroReturn			DECIMAL(18,9)=NULL,
    @conversion				INT=NULL,
    @conversionUnit_ID			INT=NULL,
    @conversionDecimalPlaces	INT=NULL,
    @nominalExcitation			DECIMAL(18,9)=NULL,
    @measuredExcitation		DECIMAL(18,9)=NULL,
    @maxOutput				DECIMAL(18,9)=NULL,
    @maxNonLinearity			DECIMAL(18,9)=NULL,
    @maxHysteresis				DECIMAL(18,9)=NULL,
    @hysteresis				INT=NULL,
    @amplified					INT=NULL,
    @tare						INT=NULL,
    @includeOffset				INT=NULL,
    @printOnCert				INT=NULL,
    @temperature				DECIMAL(18,9)=NULL,
    @humidity					DECIMAL(18,9)=NULL,
    @startTime					DATETIME=NULL,
    @endTime					DATETIME=NULL,
    @createdBy_ID				INT=NULL,
    @updatedBy_ID				INT=NULL

	IF NOT EXISTS(SELECT main_ID FROM TEST_HEADER_Load WHERE main_ID = @main_ID AND seqNO = @seqNO AND channel_ID = @channel_ID AND direction_ID = @direction_ID AND angle_ID = @angle_ID)
			SET @load_ID = SCOPE_IDENTITY()	 

Open in new window

Question by:BlakeMcKenna
  • 7
  • 4
  • 3

Expert Comment

ID: 40226989

Looking at the Stored Procedure it is looking for Decimal data for @load, @outputFirstZero, @outputFinalZero, @outputZeroReturn, @analogFirstZero, @analogFinalZero, @analogZeroReturn, @nominalExcitation, @measuredExcitation, @maxOutput, @maxNonLinearity, @maxHysteresis, @temperature and @humidity

So one of these must contain something that is outside of the decimal format, you should be able to add a break line in your debugger and then run through the code line by line. If you do this you can check each one of these to see which one it is.

Alternatively because the Stored Procedure sets them to Null if no value is passed you can comment out all of these lines and run your code to see if it is these causing the issue.

Author Comment

ID: 40227022's either going to be a decimal value or NULL. I guess the question is can a NULL value be stored in a Decimal Data Type because the way I have declared my variables are the way they are defined in the Database Table.

Expert Comment

ID: 40227038
You can add Null values to the database type Decimal, they should be specified in uppercase however.

Author Comment

ID: 40227042
Syntactically, how would I do that?


Expert Comment

ID: 40227051
Because your Stored Procedure sets default values if no data is passed you should be able to get away with not sending anything to it as a value for those parameters, so you don't even need to check that the data contains anything before setting the parameters.

Hope that makes sense
LVL 25

Expert Comment

by:Ron M
ID: 40227060
I see you are using Cint().. why not use Cdec()  ??

Author Comment

ID: 40227093
Because in most cases, some of the fields that are defined as Decimal in the DB will have a value of "".
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.


Author Comment

ID: 40227098
I actually just tried using Decimal.Parse instead of CDec and got the error message: "Input string was not in a correct format"...
LVL 25

Accepted Solution

Ron M earned 500 total points
ID: 40227102
I'm kinda confused how you're doing this...

Here's how I do it..
cmd.Parameters.AddWithValue("@Income", IIf(txtIncome.Text <> "", Cdbl(txtIncome.Text), System.DBNull.Value))

Here's how you're doing it....
  cmd.Parameters.AddWithValue("@nominalExcitation", row("nominalExcitation")).Value = If(Not DBNull.Value.Equals(row("nominalExcitation")), row("nominalExcitation"), DBNull.Value) for example.. try this..

cmd.Parameters.AddWithValue("@load", IIf(row("load")).Value.Tostring <> "", CDbl(row("load").Value), System.DBNull.Value)

Author Comment

ID: 40227172
The way I'm trying this was suggested from other technical people. I know their is always more than one way to do most things. I'll give your try a shot.

Author Comment

ID: 40227177
I copied your code verbatim and got this syntax error...see attachment!
LVL 25

Expert Comment

by:Ron M
ID: 40227183
Sorry, I had an extra parenthesis ...IIf(row("load")).Value.Tostring <> "",    ...and missed one on the end.

..use this.

 cmd.Parameters.AddWithValue("@load", IIf(row("load").Value.Tostring <> "", CDbl(row("load").Value), System.DBNull.Value))
LVL 25

Expert Comment

by:Ron M
ID: 40227186
The iif() is just testing if the value.tostring.. is "string empty"...  if not, it uses the value, if so.. it uses system.dbnull.value.

Author Closing Comment

ID: 40234910
Thanks xuserx2000...your method worked well for me!

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now