Insert NULL values

Hi

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

        ProgUpConn.Open()

        ProgUpCom.ExecuteNonQuery()

        ProgUpConn.Close()

Open in new window



in some cases the values will be NULL

Eg

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?


Thanks
EdAsked:
Who is Participating?
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:
Hi,

2 ways

Dim nullDate As System.Data.SqlTypes.SqlDateTime

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

Open in new window


OR


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

Open in new window

0

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:
Perfect
0
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.
0
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
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.