Passing variable with DBNull via INSERT INTO

[AWLWales]

I use VB .NET 2002 (academic version)

I have created an MS Access database to be populated with vaules extracted from dBase files. Some numerical values can be null - ie where no data was recorded in the dBase file.

Relevant field properties in MS Access mdb table are set to "Not Required" as follows - dt0 is the name in code of the Access table "AllData":

With dt0.Columns
                .Append("Date", DataTypeEnum.adDate)

                colTest.Name = "Ch1Deg"
                colTest.Type = DataTypeEnum.adDouble
                colTest.Attributes = ColumnAttributesEnum.adColNullable
                .Append(colTest)
End With

Using an INSERT INTO command I can send a null value to the field:

SQLMdb = "INSERT INTO [AllData]"  " ([Date],[Ch1Deg])" & _
                 " VALUES ('" & dDate1 & "',null)

My problem is how to pass a variable which sometimes takes the value System.DBNull.

If I use

SQLMdb = "INSERT INTO [AllData]"  " ([Date],[Ch1Deg])" & _
                 " VALUES ('" & dDate1 & "','" & dDeg & "')

then when dDeg is numeric then there is no problem and the table is populated. When data is missing and dDeg takes the value System.DBNull then it's a no go! The error message is "Data type mismatch in criteria expression."

Is there a way round this with VB .NET 2002 or do I need to upgrade to a later version which has nullable data types, eg nullable integers and doubles etc?

[kvprajapati]

You may exclude Ch1Deg column,

SQLMdb = "INSERT INTO [AllData] ([Date]) VALUES ('" & dDate1 & "')"

OR

Use parameter query to assign DBNull.Value.

cmd=new OledbCommand("INSERT INTO [ALLDATA] VALUES (@mdate,@mch1deg)"
cmd.Parameters.Add("@mdate",OleDbType.Date,8).Value = dDate1
cmd.Parameters.Add("@mch1deg",OleDbType.Varchar,40).Value=DBNull.Value
...
..

[AWLWales]

You may exclude Ch1Deg column,

SQLMdb = "INSERT INTO [AllData] ([Date]) VALUES ('" & dDate1 & "')"

OR

Use parameter query to assign DBNull.Value.

cmd=new OledbCommand("INSERT INTO [ALLDATA] VALUES (@mdate,@mch1deg)"
cmd.Parameters.Add("@mdate",OleDbType.Date,8).Value = dDate1
cmd.Parameters.Add("@mch1deg",OleDbType.Varchar,40).Value=DBNull.Value
...
..

Thank you for the swift suggestions:

Alas! I cannot exclude any field as over the entire set of rows some values may not be null and need to be processed.

Also, the real database has about 30 fields with around 20 of them possibly being nulls in any combination!

The second suggestion looks very promising and now I have to check it out with all the fields and more rows of data.

This I hope to do shortly and will post final reply soon.

Yours very gratefully, AWLWales

PS - if another message appears before this one about an unhandled exception, please ignore it completely: it was me being silly and mis-typing a line in changing from one test code situation to another. Sorry!

[AWLWales]

Many thanks to adatapost - The second suggestion above has been applied to the full databse of 30 odd fields with around 20 potentially null in some records.

It works beautifully.

Many thanks indeed.

AWLWales