Problem inserting in SQL Server db with null numeric field

, ,
1

I am having a problem inserting into a table where I have a numeric field that is sometimes null. How do you put a null into a numeric field

For example my sql stmt might be:
insert into t1 (f1,f2,f3) values(?,?,?). If f2 is a numeric field and the corresponding value in my $dbParamValues list is empty, I get an error converting a VARCHAR to numeric. Character values are ok and the db has a null inserted (as desired). I have not figured out how to get around this other than by building the SQL dynamically. (I am using SQL Server as the DB).

Does anyone have a suggestion? Thanks in advance for any help.

Bill

2

Hi Bill, I am interested in knowing how you implemented this with dynamically. Thanks!

3

Well, its a hack, but what i did was to branch on the value of the field that could be null and built the sql differently i.e. one branch had a the value NULL in the SQL:

insert into t1 (f1,f2,f3) values(?,NULL,?)

the other substituted the value of the variable i.e.
insert into t1 (f1,f2,f3) values(?,%V1%,?)

Its a hack, but it works.