How do I insert a null date value into my database?

I have an application where I have 4 date fields. I am using mask edit boxes to make input easier. However, when one of the date fields is left blank, the mask edit box still sends "__/__/____" to be inserted. Can I covert "__/__/____" to a null value to be entered into the database? I'm using an access database. Thanks for your help.
khebert1Asked:
Who is Participating?
 
mani_saiCommented:
If you are using SQL Server Datetime DataType you will get an error.


datetime

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.


incaseof null or "" you can pass something like 1/1/1900 for e.g.

0
 
mani_saiCommented:
can you show your code where you populate mask edit box
0
 
khebert1Author Commented:
spdate = AxMaskEdBox2.CtlText
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
khebert1Author Commented:
strSQL = "INSERT INTO tblclient ([fname], [minit], [lname], [add1], [add2], [city], [state], [zip], [spouse], [cbday], [sbday], [anniv], [spday])" & _
                " VALUES ('" & TextBox33.Text & "','" & TextBox32.Text & "','" & TextBox31.Text & "', '" & TextBox30.Text & "', '" & TextBox29.Text & "'," & _
                " '" & TextBox28.Text & "', '" & ComboBox3.Text & "'," & TextBox27.Text & ",'" & TextBox39.Text & "', #" & cbdate & "#, #" & spdate & "#, " & _
                " #" & andate & "#, #" & sddate & "#)"

I'm using variables to enter date into database. Works fine as long as the user enters date in the mask edit box.
0
 
mani_saiCommented:
can you do something like this



If AxMaskEdBox2.CtlText <> "" then  'here instead of blank you can check for empty data your masked tex box is returning
spdate = AxMaskEdBox2.CtlText
Else
spdate = ""
End IF


-----------------------------------------------------------------------
 you check for what ever you are getting if user dont enter anything in the masked box.
you can do a messagebox(AxMaskEdBox2.CtlText ) and see what you are getting if user did not enter anything.

then use the message box value here and then check for that data instead of blank.




0
 
khebert1Author Commented:
Cast from string "" to type 'Date' is not valid.

Get this error when trying to insert spdate as ""
0
 
heintalusCommented:
You better off using stored proceedures when working with SQL especially if your processing large amounts of data, you'll notice one heck of an increase in speed.

Your stored proceedure could be something like the following (I haven't included all you fields as this is just an example)

CREATE PROCEDURE AddClient
(
      @fname as varchar(40),
      @lname as varchar(40),
      @cbday as datetime,
)
AS
INSERT INTO tblclient (fname,  lname, cbday)
VALUES     (@fname, @lname, @cbday)
GO

Then to use this from within your program you'd have something like this

Private Sub AddClient()
Dim Conn as new SqlConnection("Your connection string")
Dim AddClCmd as new SqlCommand("AddClient",Conn)
AddClCmd.CommandType = CommandType.StoredProcedure

Dim FNParm as SqlParameter = AddClCmd.Parameters.Add("@fname", SqlDbType.VarChar, 40)
Dim LNParm as SqlParameter = AddClCmd.Parameters.Add("@lname", SqlDbType.VarChar, 40)
Dim CBParm as SqlParameter = AddClCmd.Parameters.Add("@cbday", SqlDbType.DateTime)

FNParm.Value = TextBox33.Text
LNParm .Value = TextBox31.Text

Dim dt as DateTime

Try
dt = DateTime.ParseExact( cbdate , "dd/MM/yyyy", new DateTimeFormatInfo())
CBParm.Value = dt
Catch
'Its failed so we'll use a null value for the parameter
CBParm.Value = System.DBNull.Value
End Try

Conn.Open()

Try
AddClCmd.ExecuteNonQuery()
MessageBox.Show("Client added successfully")
Catch ex as Exception
MessageBox.Show("Failed to add client" + vbcrlf + ex.Message)
Finally
Conn.Dispose()
End Try

End Sub

HTH
Andy
0
 
mani_saiCommented:
If AxMaskEdBox2.CtlText <> "" then  'here instead of blank you can check for empty data your masked tex box is returning
spdate = AxMaskEdBox2.CtlText
Else
spdate = "1/1/1900"
End IF
0
 
khebert1Author Commented:
The problem here mani sai is that if I populate the database with 1/1/1900, and I check for Birthday for instance, It will show up as 1/1 when it shouldn't show up at all.
The only thing that I can think of is to have multiple SQL statements. Which I really don't want to do.

0
 
mani_saiCommented:
if you want to store blank values in database then use varchar instead of datetime.

using varchar you can store both date values and blank values.

but using varchar instead of datetime datatype is not efficent either.

it is up to you to decide, just an alternative i thought


0
 
khebert1Author Commented:
mani sai,  I am going to accept your answer above. I'll store something like "1/1/9999" and exclude dates with this year when searching for Birthday's etc.
Kind of seems simple now! It helps a great deal to hear someone else's perspective.
thanks,

khebert1
0
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.

All Courses

From novice to tech pro — start learning today.