inserting "null" value into a database from vb6 front end.

[pavan_kumard]

hi,
i am facing an unusual problem when I am trying to insert a "null" value into SQL Server database.suppose I have a table named X and in that tables i have three columns a,b,c.suppose i say a is varchar datatype,b is numeric and it allows nulls,c is numeric
now as b allows nulls,i want to insert a null "null" into column b.One way of coming around this problem is in the front end using a querydef i use the insert statement as follows.insert into x(a,c)values(& a &,& c &).ie i ommit the column that i want to enter a null.but i feel this to be a crude solution.i want to use an insert statement in which i use all values and if i am not including any thing in the front end ie not sending any value then a "null" should be entered into the database.
My second problem is i manually inserted a null<null>(ie i left a particular numeric column empty while entering a record into a particular table with the help of Sql Server enterprise manager.now when i am trying to retrieve this record i am getting an error.
i can come around this problem by mentioning on error resume next on the top.Can any one give me a better solution than this.

[Ianpbaker]

Hello pavan_kumard

When you create the SQL string for the insert string try using an inline if statement. Eg.

strSQl = "INSERT into x(a,b,c)values(" & text1 & "," & iif(text2 = "",Null,text2) & "," & text3 & ")"

This might sort ou your problem

hope this helps

Ian

[honeybee]

You can work around your second problem by including some code which checks for null values in the fields of the tables. You can either make use of VB statements like

if len(trim(<fieldname>.value)) = 0 then
'this condition is true if the field is null




Perhaps you may need to adapt the code to work with numeric and text data types.

Hope it helps.