Results 1 to 3 of 3

Thread: [RESOLVED] Null parameter values and date fields, Access

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Resolved [RESOLVED] Null parameter values and date fields, Access

    I would like to know why it is that I am unable to work with a date field/column from an Access database and a parameter.

    I know, from past attempts, that if I create a parameter to update a date field that if the parameter value is null then the query will fail to execute.

    Code:
    UPDATE MyTable SET column=@date
    i.e. if @date is null then the query will fail.

    I am weary with working around this problem. Can I do something to mitigate this issue. I have looked for answers, but have been unable to find anything that addresses this.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Null parameter values and date fields, Access

    When you assign the parameter's value, pass either DBNull.Value or the date value depending on if the value is null.

    For example:
    Code:
    Dim someDateValue As DateTime
    command.Parameters.Add("@date", OleDbType.VarChar, 100).Value = If(someDateValue Is Nothing, DBNull.Value, someDateValue)
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Null parameter values and date fields, Access

    It took me awhile, but I followed the suggestion and the following code passed the null value.

    Code:
            If Not String.IsNullOrWhiteSpace(txtEnd.Text) Then
                MasterBase.AddParam("@end", txtEnd.Text)
            Else
                MasterBase.AddParam("@end", Nothing)
            End If
    Nice to have an occasional easy fix. Additionally, followed the link in the other suggestion and that also provided excellent information. Thanks all.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width