Insert emptry string or null into Numeric datatype in SQL Table

[Guest_imported]

I'm having problems inserting empty strings, which come from empty form fields, into the Numeric columns in my SQL table. I keep getting this error message:

"Error converting data type varchar to numeric."

I need to know how I can insert NULL values for those empty strings. Is this even possible? I don't want to insert '0' (zero) because shows a value when returned.

I know that this problem could be easily solved if I used another data type, but I need to use Numeric in serveral instances.

Thanks

 

[yahve]

Hi,

Have you tried converting the empty strings to the string "NULL" and inserting that as the value for the numeric field (without quotes)?

Bye.

 

[chappi]

You really shouldn't put NULL into your database. It screws you when you wanna do computations. If you really want to though, make sure that your table accepts NULL values and then don't convert anything to empty strings, it should automatically post a NULL value.

J

 

[Guest_imported]

Thanks for your tips. After some more research, it seems that strings set to NULL cannot be Inserted into a numeric datatype column. The strings have to have a numeric value.

The problem was that even when I set the strings to NULL, I would still get the error message. I have the numeric column default to NULL if there are is no data inserted, but the ASP page I'm using isn't smart enough yet to check for empty strings and use another Insert statement to compensate accordingly.

Instead I checked for empty strings and set them to zero before I did the Insert. Then later when I Select the data, I checked for the zeros and set the variables assigned those entries to empty strings ("&quot instead. This seems to work well for now.

Again, thanks for the help.

 

[yahve]

Hi,

I'm sorry, I guess I did not explain myself very well. What I ment is that NULL is a valid value for a numeric field and can be use in the insert command to mean that there is no value for that field. A NULL value doesn't screw up computation on numeric at all. This is the way it was design to work.

Bye.