-
Jan 27th, 2013, 09:57 PM
#1
Thread Starter
Junior Member
[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
-
Jan 28th, 2013, 08:29 PM
#2
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
-
Jan 29th, 2013, 08:20 AM
#3
Thread Starter
Junior Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|