• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

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.
0
khebert1
Asked:
khebert1
  • 5
  • 5
1 Solution
 
mani_saiCommented:
can you show your code where you populate mask edit box
0
 
khebert1Author Commented:
spdate = AxMaskEdBox2.CtlText
0
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 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:
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now