Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

Null Dates in an Access Database

How do you programmatically (using Visual Basic or C#) enter a blank value into a date field inside of an Access Database?
0
Ray Turner
Asked:
Ray Turner
2 Solutions
 
Patrick MatthewsCommented:
Pass a null.  For example:

INSERT INTO SomeTable (xDate) VALUES (Null)

Open in new window


That assumes, of course, that in the table design that that column was defined as nullable.
0
 
dqmqCommented:
In an SQL insert, use the keyword:  NULL

In a VB , use the the constant:   VBNULL.value

I expect it's the same in C#, but not positive.
0
 
Jeffrey CoachmanMIS LiasonCommented:
If this is in a Form in Access you can do this too, (on a button click event, or whatever...):

    Me.YourDateField=Null
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
OP_ZaharinCommented:
- as pointed by other expert, use NULL for blank input. if its an update statement also use the following, you can also specify a WHERE clause if not it will update the whole record to null.:

UPDATE tblname SET datecolumn = NULL

Open in new window


UPDATE tblname SET datecolumnname = NULL WHERE columname = value

Open in new window

0
 
kumaresan2011Commented:
Hi RgTurner,

use my sample code

   in this code u can assign null value in date field for insert or update method

cn.Execute ("INSERT INTO tblPatient ( DXDate) " _
                     & "VALUES( '" & IIf(TDBDateDX.Value = vbNullString, "", TDBDateDX.Value ) "), , adCmdText)

OR

cn.Execute ("UPDATE tblPatient SET & "tblPatient.DXDate = " & IIf(TDBDateDX.ValueIsNull, Null, "#" & TDBDateDX.Value & "#")& "WHERE tblPatient.MRNumber = " & sOldMRN), , adCmdText

try this... i hope it will help full for u

regards
kumaresan


Dim Mycn As OleDbConnection 
Dim Command As OleDbCommand 
Dim icount As Integer 
Dim SQLstr As String 

Private Sub ButtonAdd_Clic 
Try 
  Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestInsertDataVB\DataBaseTestInsertVB.mdb;") 
  Mycn.Open() 
            
  SQLstr = ("INSERT INTO tblPatient (MRNumber, COG_ID, " _
	    & "DOB, Gender, PMD, " _
            & "CRA, Diagnosis, DXDate, " _
            & "FirstName, lastname) " _
            & "VALUES('" & tdbMRN.Value & "', '" & TDBCOG.Text & "', '" & TDBDOB.Value & "' " _
	    & ", '" & cboGender.Text & "', '" & TDBPMD.Text & "', '" & TDBCRA.Text & "' " _
            & ", '" & tdbDiag.Text & "', '" & IIf(TDBDateDX.Value = vbNullString, "", TDBDateDX.Value ) & "', '" & TDBFname.Text & "' " _
	    & ", '" & TDBLname.Text & "'); 

  Command = New OleDbCommand(SQLstr, Mycn) 
  icount = cmd.ExecuteNonQuery 
  MessageBox.Show(icount) 

  Catch ex As Exception 
  MessageBox.Show("could not insert record") 
  Mycn.Close() 
End Try 
end Sub

Open in new window

0
 
Ray TurnerSenior ConsultantAuthor Commented:
Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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