Insert NULL values


I have a script that inserts values into a table in a db.

Dim ProgUpConn As SqlConnection
        Dim ProgUpCom As SqlCommand
        Dim ProgObj As SqlDataReader
        Dim connectionString3 As String = ConfigurationManager.ConnectionStrings("IRISConnectionString").ConnectionString

        ProgUpConn = New SqlConnection(connectionString3)

        ProgUpCom = New SqlCommand()

        ProgUpCom.Connection = ProgUpConn

        ProgUpCom.CommandType = CommandType.StoredProcedure

        ProgUpCom.CommandText = "INSERT_Claim"

        ProgUpCom.Parameters.Add("@IVRECORDID", SqlDbType.Int).Value = 12346
        ProgUpCom.Parameters.Add("@DATECLAIMED", SqlDbType.DateTime).Value = txtDateClaimed.Text.Trim()
        ProgUpCom.Parameters.Add("@STAFFID", SqlDbType.Int).Value = 11
        ProgUpCom.Parameters.Add("@DATERECEIVED", SqlDbType.DateTime).Value = txtDateReceived.Text.Trim()
        ProgUpCom.Parameters.Add("@UNIT01ID", SqlDbType.Int).Value = ddlUNIT01ID1.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT02ID", SqlDbType.Int).Value = ddlUNIT02ID1.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT03ID", SqlDbType.Int).Value = ddlUNIT03ID1.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT04ID", SqlDbType.Int).Value = ddlUNIT04ID1.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT05ID", SqlDbType.Int).Value = ddlUNIT05ID1.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT06ID", SqlDbType.Int).Value = ddlUNIT06ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT07ID", SqlDbType.Int).Value = ddlUNIT07ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT08ID", SqlDbType.Int).Value = ddlUNIT08ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT09ID", SqlDbType.Int).Value = ddlUNIT09ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT10ID", SqlDbType.Int).Value = ddlUNIT10ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT11ID", SqlDbType.Int).Value = ddlUNIT11ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT12ID", SqlDbType.Int).Value = ddlUNIT12ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT13ID", SqlDbType.Int).Value = ddlUNIT13ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT14ID", SqlDbType.Int).Value = ddlUNIT14ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT15ID", SqlDbType.Int).Value = ddlUNIT15ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT16ID", SqlDbType.Int).Value = ddlUNIT16ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT17ID", SqlDbType.Int).Value = ddlUNIT17ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT18ID", SqlDbType.Int).Value = ddlUNIT18ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT19ID", SqlDbType.Int).Value = ddlUNIT19ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT20ID", SqlDbType.Int).Value = ddlUNIT20ID0.SelectedValue.Trim()
        ProgUpCom.Parameters.Add("@UNIT21ID", SqlDbType.Int).Value = ddlUNIT21ID0.SelectedValue.Trim()

        ProgUpCom.Parameters.Add("@CEUS", SqlDbType.Decimal).Value = 0.1

        ProgUpCom.Parameters.Add("@DATEOFRESULT", SqlDbType.DateTime).Value = 

        ProgUpCom.Parameters.Add("@AWRDTYPE", SqlDbType.NVarChar).Value = "1"
        ProgUpCom.Parameters.Add("@PERIODCLAIMED", SqlDbType.DateTime).Value = 

        ProgUpCom.Connection = ProgUpConn




Open in new window

in some cases the values will be NULL


This will always be NULL on the insert

ProgUpCom.Parameters.Add("@DATEOFRESULT", SqlDbType.DateTime).Value = 

Open in new window

But if I leave it blank then I get this from the Stored Procedure

Exception Details: System.FormatException: String was not recognized as a valid DateTime.

Whats the best way to insert lnulls into a datetime  field?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lokesh B RDeveloperCommented:

2 ways

Dim nullDate As System.Data.SqlTypes.SqlDateTime

ProgUpCom.Parameters.Add("@DATEOFRESULT", SqlDbType.DateTime).Value = nullDate

Open in new window


ProgUpCom.Parameters.Add("@DATEOFRESULT", SqlDbType.DateTime).Value = CType(Nothing, System.Nullable(Of DateTime))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EdAuthor Commented:
Jacques Bourgeois (James Burger)PresidentCommented:
I would personally use DBNull.Value. This is the standard way of passing a Null to a database, and it works without having to declare a dummy variable or calling CType. It is also universal, it will work with any type of fields, not only dates.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.