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?