Solved

Error converting data type nvarchar to decimal Error?

Posted on 2014-07-29
14
458 Views
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)
        Try
            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
            cmd.ExecuteNonQuery()
            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:

ALTER PROCEDURE [dbo].[spAddUpdate_TEST_HEADER_Load]
	@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
AS
BEGIN

	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)
		BEGIN
			INSERT INTO TEST_HEADER_Load
				(main_ID
				 ,seqNO
				 ,channel_ID
				 ,direction_ID
				 ,angle_ID
				 ,numberOfRuns
				 ,[load]
				 ,loadUnitType_ID
				 ,loadUnit_ID
				 ,loadDecimalPlaces
				 ,loadingPoints
				 ,outputUnitType_ID
				 ,outputUnit_ID
				 ,outputDecimalPlaces
				 ,outputFirstZero
				 ,outputFinalZero
				 ,outputZeroReturn
				 ,analog
				 ,analogUnit_ID
				 ,analogDecimalPlaces
				 ,analogFirstZero
				 ,analogFinalZero
				 ,analogZeroReturn
				 ,conversion
				 ,conversionUnit_ID
				 ,conversionDecimalPlaces
				 ,nominalExcitation
				 ,measuredExcitation
				 ,maxOutput
				 ,maxNonlinearity
				 ,maxHysteresis
				 ,hysteresis
				 ,amplified
				 ,tare
				 ,includeOffset
				 ,printOnCert
				 ,startTime
				 ,endTime
				 ,temperature
				 ,humidity
	  			 ,createdBy_ID
	  			 ,dateCreated
	  			 ,updatedBy_ID
	  			 ,dateUpdated)
			 VALUES
				(@main_ID
				 ,@seqNO
				 ,@channel_ID
				 ,@direction_ID
				 ,@angle_ID
				 ,@numberOfRuns
				 ,@load
				 ,@loadUnitType_ID
				 ,@loadUnit_ID
				 ,@loadDecimalPlaces
				 ,@loadingPoints
				 ,@outputUnitType_ID
				 ,@outputUnit_ID
				 ,@outputDecimalPlaces
				 ,@outputFirstZero
				 ,@outputFinalZero
				 ,@outputZeroReturn
				 ,@analog
				 ,@analogUnit_ID
				 ,@analogDecimalPlaces
				 ,@analogFirstZero
				 ,@analogFinalZero
				 ,@analogZeroReturn
				 ,@conversion
				 ,@conversionUnit_ID
				 ,@conversionDecimalPlaces
				 ,@nominalExcitation
				 ,@measuredExcitation
				 ,@maxOutput
				 ,@maxNonlinearity
				 ,@maxHysteresis
				 ,@hysteresis
				 ,@amplified
				 ,@tare
				 ,@includeOffset
				 ,@printOnCert
				 ,@startTime
				 ,@endTime
				 ,@temperature
				 ,@humidity
	  			 ,@createdBy_ID
	  			 ,GETDATE()
	  			 ,@updatedBy_ID
	  			 ,GETDATE())
			  	 
			SET @load_ID = SCOPE_IDENTITY()	 
		END
END

Open in new window

0
Comment
Question by:BlakeMcKenna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
14 Comments
 
LVL 8

Expert Comment

by:jawa29
ID: 40226989
Hi

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.
0
 

Author Comment

by:BlakeMcKenna
ID: 40227022
Well...it'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.
0
 
LVL 8

Expert Comment

by:jawa29
ID: 40227038
You can add Null values to the database type Decimal, they should be specified in uppercase however.
0
Technology Partners: 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

by:BlakeMcKenna
ID: 40227042
Syntactically, how would I do that?

      DBNull.Value?
0
 
LVL 8

Expert Comment

by:jawa29
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
0
 
LVL 25

Expert Comment

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

Author Comment

by:BlakeMcKenna
ID: 40227093
Because in most cases, some of the fields that are defined as Decimal in the DB will have a value of "".
0
 

Author Comment

by:BlakeMcKenna
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"...
0
 
LVL 25

Accepted Solution

by:
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)


...so for example.. try this..

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

Author Comment

by:BlakeMcKenna
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.
0
 

Author Comment

by:BlakeMcKenna
ID: 40227177
I copied your code verbatim and got this syntax error...see attachment!
Screenshot.jpg
0
 
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))
0
 
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.
0
 

Author Closing Comment

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

Featured Post

Technology Partners: 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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

623 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