none
how to insert null in DateTime Field with Access Driver RRS feed

  • Question

  • In Microsoft Access Driver , to add a DateTime Field from C#  using sql statment, it should be put between two '#'  like this (#10/10/1999#) , well ,

    what can I do if i want to add null DateTime???

     

    if i passed in sql statment (##)  --> Access Driver reply with Syntax error in INSERT INTO Statment '##'.

     

    if i passed any date without '#' --> Access Driver automatically add the default value of date (min) without looking to the date i passed.

     

    I use .Net frame work 2.0

     

    finally , how can i add null value in DateTime field???

    Friday, October 5, 2007 9:31 AM

Answers

  • You need to use parameterized SQL statement and pass all the values as parameters where you could assign DbNull.Value to your date parameter. The code would be like

     

    Dim connection as OleDbConnection

    ......

    Dim command As OleDbCommand = New OleDbCommand ( _

    "INSERT INTO MyTable (MyDateFieldNameHere) VALUES(?)", connection )

    Dim myDate As OleDbParameter = command.Parameters.Add ("MyDate", OleDbType.Date)

    myDate.Value=Assign your date value here
    OR

    myDate.Value=DbNull.Value  'if you need to assign NULL
    command.ExecuteNonQuery

    See next links for more details about how to use parameterized SQL statements

     

    http://support.microsoft.com/kb/308049/en-us

    Friday, October 5, 2007 10:43 AM
    Moderator

All replies

  • You need to use parameterized SQL statement and pass all the values as parameters where you could assign DbNull.Value to your date parameter. The code would be like

     

    Dim connection as OleDbConnection

    ......

    Dim command As OleDbCommand = New OleDbCommand ( _

    "INSERT INTO MyTable (MyDateFieldNameHere) VALUES(?)", connection )

    Dim myDate As OleDbParameter = command.Parameters.Add ("MyDate", OleDbType.Date)

    myDate.Value=Assign your date value here
    OR

    myDate.Value=DbNull.Value  'if you need to assign NULL
    command.ExecuteNonQuery

    See next links for more details about how to use parameterized SQL statements

     

    http://support.microsoft.com/kb/308049/en-us

    Friday, October 5, 2007 10:43 AM
    Moderator
  • Thanks WMazur

    using OleDbParameter and DBNull.Value works properly

     

    I also found that 

    OleDbCommand command=new OleDbCommand("INSERT INTO MyTable VALUES(null)",connection);

    also add null to the database .............. thanks

    Saturday, October 6, 2007 11:28 AM
  •  

    Hi,

     

    I have the same problem, but with different approach. I hope you could help me out here.

     

    By the way, here's the approach I'm using:

     

    cmd.CommandText = "INSERT INTO [MS Access;Database=test.mdb].[tableName]

    SELECT * FROM [Sheet1$] WHERE [Sheet1$].[datefield] = (NULL)"

    cmd.ExecuteNonQuery()

     

    This doesn't work. Even the DBNULL.Value doesn't work.

     

    How can I still Insert data with NULL or blank datefield on Access?

     

    Thanks in advance!

    Wednesday, October 17, 2007 8:34 AM
  •  

    At first ,

     

    the statment ( "INSERT INTO [MS Access;Database=test.mdb].[tableNameSELECT * FROM [Sheet1$] WHERE [Sheet1$].[datefield] = (NULL)") isn't a correct sql statment , sure you get an OleDbException after (cmd.ExecuteNonQuery())

     

    excuse me , i cant help you more because I dont know what you want to do !!

    Wednesday, October 17, 2007 8:46 AM
  • Hello,

     

    Sorry, I already figured it out.

    Anyway, I inserting all records from an excel sheet, and I want to insert only those records with NULL on datefield.

    What I did is, I used IS NULL instead.

     

    INSERT INTO [MS Access;Database=test.mdb].[tableName

    SELECT * FROM [Sheet1$] WHERE [Sheet1$].[datefield] IS NULL

     

    Thanks

     

    Wednesday, October 17, 2007 9:17 AM
  •  

    I didn't work before with excel sheet by sql statments ,

    but try :

    WHERE [Sheet1$].[datefield] = null         or

    WHERE [Sheet1$].[datefield] = ""            or

    WHERE [Sheet1$].[datefield] = ?

    and use OleDbParameter class and set parameter value =DbNull.Value

    Wednesday, October 17, 2007 9:30 AM
  • Excellent!!! Worked like charm Smile Thanks a ton.

    Tuesday, October 14, 2008 7:53 AM
  • I have problem to insert null value in date field

    I'm currently use this code:


        sql = "INSERT INTO TempKrediti (DataOD, DataDO, Iznos, Valuta, OsigOD1, OsigDO1, ObezDefault, User, Vreme)" &  _
              "SELECT '" & #" & Format([txtDataOD], "MM-DD-YYYY") & "# AS DataOD, " & _
              "#" & Format([txtDataDO], "MM-DD-YYYY") & "# AS DataDO, " & _
              "'" & [txtIznos] & "' AS Iznos, '" & [cboValuta] & "' AS Valuta, " & _
    "#" & IIf(IsNull([datOS1]) Or Len([datOS1]) = 0, "01-01-1900", Format([datOS1], "MM-DD-YYYY")) & "# AS OsigOD1, #" & IIf(IsNull([datOSdo1]) Or Len([datOSdo1]) = 0, "01-01-1900", Format([datOSdo1], "MM-DD-YYYY")) & "# AS OsigDO1, '" & ObezDef & "' AS ObezDefault, " & _
              "'" & PCUser & "' As User, '" & PCVreme & "'As Vreme"
        DoCmd.RunSQL (sql)


    DataOD and DataDO are date fields and they are reqired, here isn't the problem

    but

    OsigOD1 and  OsigDO1
    are date fields too, but the are not required fields

    datOS1  is form field for OsigOD1
    datOSdo1 is form field for OsigDO1

    My solution was when these (
    datOS1,datOSdo1) are empty to insert 01-01-1900 in that date field


    Can I insert Null value to data fields instead
    01-01-1900 ????


    Please reply
    gsivakov@mail.com.mk

    Sunday, November 16, 2008 10:10 PM
  • You should be able to insert NULL if it is not required field. Can you post the code that inserts NULL values?

     

    Monday, November 17, 2008 11:39 AM
    Moderator
  • I'don't know how to insert NULL value, because it is Date field.
    I don't know the code.
    previous I use this code
    "#" & Format([txtOsigOD], "MM-DD-YYYY") & "# AS OsigOD,

    If the FORM date field is empty, when I try to use sql INSERT INTO
    I have conversion error if value is NULL, something like this

    Thanks
    Wednesday, November 19, 2008 12:11 PM
  • Inserting NULL into any field, which allows NULLs would be like

     

    INSERT INTO MyTable (FieldName) VALUES (NULL)

     

    What you could do ius to create parameterized query, something like

     

    INSERT INTO MyTable (FieldName) VALUES (?)

     

    And then pass DBNull.Value (in a case if theer is no date value) as a parameter value from your code. Here is short sample (but not tested)

     

    Dim queryString As String = "INSERT INTO MyTable (FieldName) VALUES (?)"

    Using connection As New OleDbConnection(connectionString)
    Dim command As New OleDbCommand(queryString, connection)

     

    command.Parameters.Add( "@MyDateParam", OleDbType.Date).Value = DBNull.Value

    connection.Open()
    command.ExecuteNonQuery()

     

    End Using

     

     

    Thursday, November 20, 2008 11:26 AM
    Moderator
  • First I use this code

        sql = "INSERT INTO TempKrediti (BDS, BrojNaDogovor, TipNaDogovor, Partija , Konto, DateOD, DateDO, Iznos , Valuta, Nadomest, Obezbeduvanje , ProcenetaVrednost, OsigOD, OsigDO , PoslednaProcenkaData, Referent, Mesec, Godina )" & _
              "SELECT '" & [txtBDS1] & "' AS BDS, " & _
              "'" & [txtBrojNaDogovor] & "' AS BrojNaDogovor, " & _
              "'" & [cboTipNaDogovor] & "' AS TipNaDogovor, " & _
              "'" & [txtPartija] & "' AS Partija, " & _
              "'" & [txtKonto] & "' AS Konto, " & _
              "'" & [datDateOD] & "' AS DateOD, " & _
              "'" & [datDateDO] & "' AS DateDO, " & _
              "'" & [txtIznos] & "' AS Iznos, " & _
              "'" & [cboValuta] & "' AS Valuta, " & _
              "'" & [txtNadomest] & "' AS Nadomest, " & _
              "'" & [cboObezbeduvanje] & "' AS Obezbeduvanje, " & _
              "'" & [txtProcenetaVrednost] & "' AS ProcenetaVrednost, " & _
              "'" & [txtOsigOD] & "' AS OsigOD, " & _
              "'" & [txtOsigDO] & "' AS OsigDO, " & _
              "'" & [txtPoslednaProcenkaData] & "' AS PoslednaProcenkaData, " & _
              "'" & [cboReferent] & "' AS Referent, " & _
              lMesecOd & " AS Mesec, " & _
              lFor & " AS Godina"

    But when datDateOD  or datDateDO are empty there is a error NULL value in date field (DateOD; Date DO),
    How can I format these fields or change this code to enter null value in these fileld
    if
    datDateOD  or datDateDO fields on the form are empty.

    Sunday, November 23, 2008 1:25 AM
  • You should pass values as parameters instead of using concatenation, at least for the dates. I also do not understand why you are using SELECT in your SQL statgement if you do not select anything from other table.

     

    You code should look something like (I do not know what datDateOD is, but assuming that it returns value in Text property)

     

        sql = "INSERT INTO TempKrediti (BDS, BrojNaDogovor, TipNaDogovor, Partija , Konto, DateOD, DateDO, Iznos , Valuta, Nadomest, Obezbeduvanje , ProcenetaVrednost, OsigOD, OsigDO , PoslednaProcenkaData, Referent, Mesec, Godina ) VALUES (" & _
              [txtBDS1] & "' , " & _
              "'" & [txtBrojNaDogovor] & "' , " & _
              "'" & [cboTipNaDogovor] & "' , " & _
              "'" & [txtPartija] & "' , " & _
              "'" & [txtKonto] & "' , " & _
              "? , ?, " & _
              "'" & [txtIznos] & "' , " & _
              "'" & [cboValuta] & "' , " & _
              "'" & [txtNadomest] & "' , " & _
              "'" & [cboObezbeduvanje] & "' , " & _
              "'" & [txtProcenetaVrednost] & "' , " & _
              "'" & [txtOsigOD] & "' , " & _
              "'" & [txtOsigDO] & "' , " & _
              "'" & [txtPoslednaProcenkaData] & "' , " & _
              "'" & [cboReferent] & "' , " & _
              lMesecOd & " , " & _
              lFor & " )"

    Using connection As New OleDbConnection(connectionString)
    Dim command As New OleDbCommand(sql, connection)

     

    command.Parameters.Add( "@DateOD", OleDbType.Date)

     

    If String.IsNullOrEmpty(datDateOD) Then

    command.Parameters("@DateOD").Value = DBNull.Value

    Else

    command.Parameters("@DateOD").Value = Convert.ToDate(datDateOD.Text)

    End If

     

    If String.IsNullOrEmpty(datDateDO) Then

    command.Parameters("@DateDO").Value = DBNull.Value

    Else

    command.Parameters("@DateDO").Value = Convert.ToDate(datDateDO.Text)

    End If

     

    connection.Open()
    command.ExecuteNonQuery()

     

    End Using


     

    Monday, November 24, 2008 11:39 AM
    Moderator
  • I'm using Microsoft Access 2003 and VB 6.5
    I can't install any new software because I'm working on workstation in network


    datDateOD on the form is textbox with date format (FORMAT. ... Short Date)

    But the problem here is
    when I paste the code in VB editor



    Using connection As New OleDbConnection(connectionString)
    Dim command As New OleDbCommand(sql, connection)

    command.Parameters.Add( "@DateOD", OleDbType.Date)

    If String.IsNullOrEmpty(datDateOD) Then


        command.Parameters("@DateOD").Value = DBNull.Value

    Else

        command.Parameters("@DateOD").Value = Convert.ToDate(datDateOD.Text)

        End If
     If String.IsNullOrEmpty(datDateDO) Then

            command.Parameters("@DateDO").Value = DBNull.Value

        Else

            command.Parameters("@DateDO").Value = Convert.ToDate(datDateDO.Text)

            End If

     

    connection.Open()
    command.ExecuteNonQuery()


    End Using








    My temporable solution is to enter 01-01-1900 in the table fields(DateOD; DateDO) when datDateOD or datDateDO are empty.

    If(IsNull([datDateOD]) Or Len([datDateOD]) = 0, "01-01-1900", Format([datDateOD], "MM-DD-YYYY")) & "# AS DateOD, #" & IIf(IsNull([datDateDO]) Or Len([datDateDO]) = 0, "01-01-1900", Format([datDateDO], "MM-DD-YYYY")) & "# AS DateDO,


    But I need to leave the field empty or enter null value to date fields instead 01-01-1900
    Monday, November 24, 2008 10:11 PM
  • This forum is for .NET only and does not cover outdated technology. If you need to get support for VB6, you need to post your question in Microsoft newsgroups. You can find appropriate newsgroup here

     

    http://www.microsoft.com/communities/newsgroups/default.mspx

    Tuesday, November 25, 2008 11:24 AM
    Moderator
  • This approach worked.....
    Wednesday, December 28, 2011 9:13 AM