Allow Null values to be saved in database

Question

Im using Visual Studio .NET 2003 the add button works fine when all textbox, combobox fields are filled with data, but upon testing not filling the fields with data leaving it NULL it returns error saying "String was not recognized as a valid DateTime"

I have a textbox named txtPurchasedDate

My Stored procedure

 CREATE PROCEDURE AddOfficeEquipmentProfile
    (
    @OE_ID      varchar(11)     =   NULL,
    @OE_Category        char(3)         =   NULL,
    @OE_SubCategory char(3)         =   NULL,
    @OE_Name        varchar(35)     =   NULL,
    @OE_User        varchar(35)     =   NULL,
    @OE_Brand       varchar(15)     =   NULL,
    @OE_Model       varchar(35)     =   NULL,
    @OE_Specs       varchar(1000)       =   NULL,
    @OE_SerialNo        varchar(35)     =   NULL,
    @OE_PropertyNo  varchar(35)     =   NULL,
    @OE_MacAddress  varchar(100)        =   NULL,   
    @OE_Static_IP       varchar(15)     =   NULL,
    @OE_Vendor      varchar(35)     =   NULL,
    @OE_PurchaseDate    smalldatetime       =   NULL,
    @OE_WarrantyInclusiveYear   int     =   NULL,
    @OE_WarrantyStatus  char(2)         =   NULL,
    @OE_Status      varchar(15)     =   NULL,
    @OE_Dept_Code   char(3)         =   NULL,
    @OE_Location_Code   char(8)         =   NULL,
    @OE_Remarks     varchar(1000)       =   NULL
    )
    AS

    INSERT INTO tblOfficeEquipmentProfile (OE_ID, OE_Category, OE_SubCategory, OE_Name, OE_User, OE_Brand, OE_Model, OE_Specs, OE_SerialNo,
    OE_PropertyNo, OE_MacAddress, OE_Static_IP, OE_Vendor, OE_PurchaseDate, OE_WarrantyInclusiveYear, OE_WarrantyStatus, OE_Status, OE_Dept_Code,
    OE_Location_Code, OE_Remarks ) 
    VALUES (@OE_ID, @OE_Category, @OE_SubCategory, @OE_Name, @OE_User, @OE_Brand, @OE_Model, 
    @OE_Specs, @OE_SerialNo, @OE_PropertyNo, @OE_MacAddress, @OE_Static_IP, @OE_Vendor, @OE_PurchaseDate, @OE_WarrantyInclusiveYear, @OE_WarrantyStatus,
    @OE_Status, @OE_Dept_Code, @OE_Location_Code, @OE_Remarks)

    IF @@ERROR<>0
        BEGIN
            ROLLBACK TRANSACTION
            RETURN 0
        END 
    ELSE
        BEGIN
            COMMIT TRANSACTION
            RETURN 1
        END
    GO

My Vb.net Add Button Code

 Dim cmd As SqlCommand = sqlconn.CreateCommand
        sqlconn.Open()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "AddOfficeEquipmentProfile"

    cmd.Parameters.Add("@OE_ID", SqlDbType.VarChar, 11, "oeq-su-999")
    cmd.Parameters.Add("@OE_Category", SqlDbType.Char, 3, "COM")
    cmd.Parameters.Add("@OE_SubCategory", SqlDbType.Char, 3, "SU")
    cmd.Parameters.Add("@OE_Name", SqlDbType.VarChar, 35, "adminpmis01")
    cmd.Parameters.Add("@OE_User", SqlDbType.VarChar, 35, "Ivan")
    cmd.Parameters.Add("@OE_Brand", SqlDbType.VarChar, 15, "DELL")
    cmd.Parameters.Add("@OE_Model", SqlDbType.VarChar, 35, "optiplex")
    cmd.Parameters.Add("@OE_Specs", SqlDbType.VarChar, 1000, "dualcore")
    cmd.Parameters.Add("@OE_SerialNo", SqlDbType.VarChar, 35, "sgh5960")
    cmd.Parameters.Add("@OE_PropertyNo", SqlDbType.VarChar, 35, "j7h7h6g6f2")
    cmd.Parameters.Add("@OE_MacAddress", SqlDbType.VarChar, 100, "j7h7:h6g6f2")
    cmd.Parameters.Add("@OE_Static_IP", SqlDbType.VarChar, 15, "192.168.1.5")
    cmd.Parameters.Add("@OE_Vendor", SqlDbType.VarChar, 35, "ADWAYS")

    cmd.Parameters.Add("@OE_PurchaseDate", SqlDbType.SmallDateTime)
    cmd.Parameters.Add("@OE_WarrantyInclusiveYear", SqlDbType.Int)
    cmd.Parameters.Add("@OE_WarrantyStatus", SqlDbType.Char, 2, "IN")
    cmd.Parameters.Add("@OE_Status", SqlDbType.VarChar, 15, "Good")
    cmd.Parameters.Add("@OE_Dept_Code", SqlDbType.Char, 3, "ADM")
    cmd.Parameters.Add("@OE_Location_Code", SqlDbType.Char, 8, "ADM_OFC")
    cmd.Parameters.Add("@OE_Remarks", SqlDbType.VarChar, 1000, "ACTIVE")
    cmd.Parameters("@OE_ID").Value = txtOEID.Text
    cmd.Parameters("@OE_Category").Value = cmbCategory.Text
    cmd.Parameters("@OE_SubCategory").Value = cmbSubCategory.Text
    cmd.Parameters("@OE_Name").Value = txtName.Text
    cmd.Parameters("@OE_User").Value = txtUser.Text
    cmd.Parameters("@OE_Brand").Value = cmbBrand.Text
    cmd.Parameters("@OE_Model").Value = cmbModel.Text
    cmd.Parameters("@OE_Specs").Value = txtSpecs.Text
    cmd.Parameters("@OE_SerialNo").Value = txtSerialNo.Text
    cmd.Parameters("@OE_PropertyNo").Value = txtPropertyNo.Text
    cmd.Parameters("@OE_MacAddress").Value = txtMacAddress.Text
    cmd.Parameters("@OE_Static_IP").Value = txtStaticIp.Text
    cmd.Parameters("@OE_Vendor").Value = txtVendor.Text
    cmd.Parameters("@OE_PurchaseDate").Value = txtPurchaseDate.Text
    cmd.Parameters("@OE_WarrantyInclusiveYear").Value = txtWarrantyInclusiveYear.Text
    cmd.Parameters("@OE_WarrantyStatus").Value = txtWarrantyStatus.Text
    cmd.Parameters("@OE_Status").Value = txtStatus.Text
    cmd.Parameters("@OE_Dept_Code").Value = cmbDeptCode.Text
    cmd.Parameters("@OE_Location_Code").Value = cmbLocationCode.Text
    cmd.Parameters("@OE_Remarks").Value = txtRemarks.Text
    cmd.ExecuteNonQuery()
    MsgBox("Successfully Added Equipment Profile")
    sqlconn.Close()

if you are passing an null date in, you need to be sure that your database is setup to allow null values. Each column can be set to allow nulls. — Jason, May 02 '13 at 01:08
@Jason yes sir it allow nulls ive checked my database what should i do then — ivandinglasan, May 02 '13 at 01:13
@Jason, he is doing that part correctly. He is passing his PurchaseDate parameter as a smalldatetime making format irrelevant. The only time format matters for dates and times is when you are displaying them. — Dan Bracuk, May 02 '13 at 01:27
How many times do you plan on asking the same (or similar question)? http://stackoverflow.com/q/16232173/745969, http://stackoverflow.com/q/16291644/745969, http://stackoverflow.com/q/16293543/745969, this one...? — Tim, May 02 '13 at 06:46
Just out of curiosity, **why** are you using VS 2003? Are you supporting a 1.1 app that management/client refuses to allow you to upgrade to a newer version of .NET? You can get free (express) editions of 2010 and 2012. — Tim, May 02 '13 at 06:57
@ivandinglasan - I'm talking about Visual Studio **EXPRESS*, which *is* free. Take a look at http://www.microsoft.com/visualstudio/eng/downloads and scroll down to "Visual Studio Express 2012" and download whichever one(s) suit your needs. You are severely limiting yourself and your opportunities to learn by using VS 2003. — Tim, May 02 '13 at 16:40

score 2 · Answer 1 · answered May 02 '13 at 01:03

2

If you want to store null values, you have to use conditional logic in your vb code to either not send those parameters to your stored procedure, or send a value of dbnull. Even your char fields are storing empty strings, which might not be your intent.

answered May 02 '13 at 01:03

Dan Bracuk

20,699
4
26
43

how would be the syntax and logic sir? – ivandinglasan May 02 '13 at 01:05
@JohnFx I wouldn't bother to ask question if I haven't ran out of ideas, already tried dbNULL. I try different things first and researching google before posting – ivandinglasan May 02 '13 at 03:41
he already told you the answer, now you are asking him to type it out for out. – JohnFx May 02 '13 at 03:56
well maybe im not just so genius like you sir @JohnFx that you can understand every little thing – ivandinglasan May 02 '13 at 05:07
Calling you out on lack of effort, not lack of understanding. – JohnFx May 02 '13 at 05:15
how would you even know im not exerting effort, when you dont even see my workspace and design view of vb – ivandinglasan May 02 '13 at 06:11

Ryan · Answer 2 · 2013-05-02T01:34:27.377

0

Use

If (Not String.IsNullOrEmpty(txtPurchaseDate.Text)) Then
    cmd.Parameters("@OE_PurchaseDate").Value = txtPurchaseDate.Text
End If

edited May 02 '13 at 01:34

answered May 02 '13 at 01:13

Ryan

3,924
6
46
69

The IsNull(txtPurchaseDate.Text) check may not be strictly necessary - I think the txtPurchaseDate.Text will be empty string, "" if not set. You could check this by inspecting the value at runtime. Though it's probably safer than not including the check. – Ryan May 02 '13 at 01:18
IsNull is not declared sir – ivandinglasan May 02 '13 at 01:19
Sorry, the code I gave was VBA. You can use String.IsNullOrEmpty(txtPurchaseDate.Text) as the check instead, for VB. – Ryan May 02 '13 at 01:33
`String.IsNullOrEmpty` is not available in 1.1 - OP is using VS 2003, per the question's tags. – Tim May 02 '13 at 06:55
Then use txtPurchaseDate.Text <> "" – Ryan May 02 '13 at 14:59

Edper · Answer 3 · 2013-05-02T06:39:36.727

Why not use DateTimePicker for your PurchaseDate instead of a textbox in txtPurchaseDate.Text you could name your control dtpPurchaseDate then you have:

cmd.Parameters("@OE_PurchaseDate").Value = dtpPurchaseDate.Value

The Default Value (or the Minimum Value) for DateTime Picker is 1/1/1980 so you could initialize the value of dtpPurchaseDate to 1/1/1980 like:

dtpPurchaseDate = System.DateTime.Parse("1/1/1980")

So, which means by the way when stored in your database it has the value of 1/1/1980 and not NULL.

If you want to purse with the Textbox then you could check if empty then assign a minimum date value which is actually 1/1/0001 like this:

If (If (String.IsNullOrEmpty(txtPurchaseDate.Text)) Then
   cmd.Parameters("@OE_PurchaseDate").Value = DBNull.Value  
Else
   Dim tempPurchaseDate as DateTime

   System.DateTime.TryParse(txtPurchaseDate.Text, tempPurchaseDate)
   cmd.Parameters("@OE_PurchaseDate").Value = tempPurchaseDate         
End If

But again it has a stored value in your database as 1/1/0001

You could display it empty in your TextBox like: Given you have a DataRow named dr

If (dr["OE_PurchaseDate"] = System.DateTime.Parse("1/1/0001")) Then
   txtPurchaseDate.Text = ""
Else
   txtPurchaseDate.Text = dr["OE_PurchaseDate"]
End If

upon using date time picker what if i leave it alone not changing anything what would be the default date? — ivandinglasan, May 02 '13 at 01:48

Allow Null values to be saved in database

3 Answers3