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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

679 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