Casting Help

Can someone please help me with this? I'm losing all my hair trying to figure it out. I've tried all that I can think of. I am getting the Error converting data type varchar to numeric.

Thanks
Sam "O"

cmdInsert = New SqlCommand( "INSERT INTO tmp_blank (sessionid, ssn,job,sun,mon,tue,wed,thu,fri,sat,totcol)
SELECT '" & Session.SessionId & "', ssn,job,sun,mon,tue,wed,thu,fri,sat,
(cast(sun as numeric(4,2)) + cast(mon as numeric(4,2)) +
cast(tue as numeric(4,2)) + cast(wed as numeric(4,2)) +
cast(thu as numeric(4,2)) + cast(fri as numeric(4,2)) +
cast(sat as numeric(4,2))) as totcol from
" & strTableName, conTimeCard)
intUpdateCount = cmdInsert.ExecuteNonQuery()

What are the values for sun, etc.? Could they be null? Could they be non-numeric, like 'fred'? If they are numeric, why are you using a varchar field?|||Thank you for your quick reply. Here are the answers to your questions.

yes values could be null
They will never be non-numeric
I am using an exisiting table and I am not able to modify the datatypes (yet)

Thanks
Sam "O"|||I am not certain the NULL is the issue, but I would try using IsNull(), like so:

CAST(IsNull(@.v,'0') as Numeric(4,2))

Even if it does not throw the exception, if the variable is null, null+anything will return null.

I would also check and see that all values are null rather than a single space. For instance, this will give you the casting error:

DECLARE @.v as varchar(20)
SET @.v=''
SELECT CAST(IsNull(@.v,'0') as Numeric(4,2))

If the values that do not appear obviously to be numbers are not NULL, but are rather empty strings or ' ', then perhaps you will want to run a query to change the "missing" values to null, or create a view with case statements to fix up the values. This is why using one type when you want another is a bad idea.|||I did try the IsNull Prior to posting and still was thrown the exception so it makes sense to me now what you are saying about the empty string possibilities.

Thank you for your input!