Results 1 to 3 of 3

Thread: [RESOLVED] Cannot pass a DBNULL parameter using OLEDB

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2009
    Posts
    17

    Resolved [RESOLVED] Cannot pass a DBNULL parameter using OLEDB

    Hello,
    I hope this is the right forum for my question.
    I am using VB in an ASP.NET application connecting to an MS Access db (Access 2010) using a stored parametrized query
    I want to be able to assign NULL to one of the parameter so that the query will return all the rows (kind of like using a wildcard, except that is how it works with Access)
    I use the following code in a Private Function:

    Dim constring As String = ConfigurationManager.ConnectionStrings("dbPN_ConnectionString").ConnectionString
    Using con As OleDbConnection = New OleDbConnection(constring)
    Using cmd As OleDbCommand = New OleDbCommand(strQuery, con)
    cmd.CommandType = Data.CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("Project", sProject)
    cmd.Parameters.AddWithValue("Category", sCat)
    If sSubcat = "" Then
    cmd.Parameters.Add(DBNull.Value)
    Else
    cmd.Parameters.AddWithValue("SubCategory", sSubcat)
    End If
    con.Open()

    When I try to run this, I get the following error message: the Oledbparametercollection only accepts non null oledbparameter type objects, not string objects

    Obviously I am not using the correct approach but with an Access db, I am not sure what other path I could follow.
    Any help/insight would be appreciated
    Thanks
    Olivier

  2. #2
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: Cannot pass a DBNULL parameter using OLEDB

    Try this
    Code:
    If sSubcat = "" Then
     cmd.Parameters.AddWithValue("SubCategory", DBNull.Value)
     Else
     cmd.Parameters.AddWithValue("SubCategory", sSubcat)
     End If
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2009
    Posts
    17

    Re: Cannot pass a DBNULL parameter using OLEDB

    Thank you Wild-bill, this worked!
    I had thought I needed to use the .Add method instead of the .AddWithValues method to pass this parameter, based on another post I read earlier.
    Much appreciated
    Olivier

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