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.
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

773 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