Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error converting data type nvarchar to decimal Error?

Posted on 2014-07-29
14
Medium Priority
?
472 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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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