Link to home
Start Free TrialLog in
Avatar of RekhaShah
RekhaShah

asked on

calling Parameterized Query in MS Access from VB.Net does not return any data

HI Experts,
I am using MS Access 2010 as back end and VB.Net (VS 2010) as front end for my desktop application.
I have created a Query in Access database : This query uses two parameters and filters the data based on these two parameters. It works fine if I run the query in Access itself.
Now I am calling it from my VB.Net code. If I pass DBNull.value as value for both parameters, it returns all the data( as expected), but If I pass one of the values, it does not return any data.
Attaching my code as well as my Query.

What i am trying to accomplish is that I have a combobox that gets populated with all these nodes. User can filter this combobox by Node Description or NodeId. If they leave both blank, it should return all the data. But if they say e.g. "CP" then only the nodes that have description starting with "CP" will be returned.
I will greatly appreciate your help.
Thanks
Public Function execStoredQuery(ByVal queryName As String, ByVal sConnStr As String) As DataTable
        Dim dt As New DataTable
        'Note the query is entered as a string. 
        Dim da As New OleDbDataAdapter(queryName, sConnStr) 'qryGroupData is the name of the query in Access 

        ' ''Set the CommandType of the SelectCommand to StoredProcedure
        da.SelectCommand.CommandType = CommandType.StoredProcedure
        Dim sDesc As String
        sDesc = "C"
        ''Add the required parameter value for the query
        da.SelectCommand.Parameters.AddWithValue("@NodeId", DBNull.Value)
        da.SelectCommand.Parameters.AddWithValue("@Description", sDesc)
        da.Fill(dt)
        Return dt
    End Function

My Query looks like:

SELECT NodeID, Description, ShortDesc
FROM Ref_Nodes
WHERE ([@NodeID] Is Null Or NodeID=[@NodeID]) And ([@Description] Is Null Or Description Like + [@Description]+'*')
ORDER BY Description;

Open in new window

Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Try with:

WHERE ([@NodeID] Is Null Or NodeID=[@NodeID]) And ([@Description] Is Null Or Description Like + Chr(34) + [@Description] + '*' + Chr(34))

/gustav
Avatar of RekhaShah
RekhaShah

ASKER

This is a stored query in Access 2010. If I run the query in Access it works fine for either parameters.
Now It does not even work in Access. So my Query is correct.
It also works if I send  some value in 1st parameter, it brings correct record. It is the description field that does not work.
OK. The SQL syntax for Like is (for your example):

  Like "C*"

as the parameter is a string. Could be:

WHERE ([@NodeID] Is Null Or NodeID=[@NodeID]) And ([@Description] Is Null Or Description Like  [@Description] & '*')

/gustav
ASKER CERTIFIED SOLUTION
Avatar of Den_HBR
Den_HBR

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
> The problem is in '*', this has to be '%'

No. I guess you have SQL Server in mind, but:

> This is a stored query in Access 2010.

/gustav
No, I have Microsoft.Jet.OLEDB.4.0 in mind.
I know if the author changes '*' to '%', the query will no longer work in Access, but it should work in Visual Studio.

I tested it with Access 2003 and VS2005.
I asume it will be the same for Access 2010 and VS2010 (using Microsoft.ACE.OLEDB.12.0)


Like you said Den_HBR, if i replace '*' with '%' in the query, it does not return any records in side of Access.
But it did not fix the problem with Visual Studio either. Is there a setting to recognize wild character? Do i need to include wrap the  string variable with single quotes ?
Wildcard character is * in Access, ? is for a single character.
As you run a query stored in Access, this is what to use.

/gustav
But how do i get it to work from visual studio?
My query runs fine with '*'.
Like I suggested, modify your query a little:

WHERE ([@NodeID] Is Null Or NodeID=[@NodeID]) And ([@Description] Is Null Or Description Like [@Description] & '*')

/gustav
'%' does not work inside Access Query. So I removed wildcard completely from my Query like you suggested. Added '%' in my parameter like you suggested, and it works fine. If i need to check the logic when i run the query inside os Access, I append '*' with my parameter when I am prompted. This is crazy, but it works.
Thank you
Accessdatabase is a database-file, not a database-server.
If you store a query in Access, it will be executed by the program who calls it.

If you open Access and run your query there, it will be executed by Access. (wildcard '*')
If you connect to the file with oledb or odbc, it will be executed by the provider. (wildcard '%' for jet.oledb)

In the past I used Accessfiles a lot as database for my applications, and Access didn't even had to be installed on the computer where the file was on.
In those days I came across a similar problem. The function Replace() was supported by Access, but not by the Jet.oledb-driver. I hoped, if a stored the query in Access, it would work, but it didn't(see reason above).

These days I use SQL Server 2005 Express.
Thanks for your clerification. But this brings me to a related dilema...
Can you create individual (like .mdb or now .accdb) file in SQL Server Express file for each account in the business? If so, how portable these files are? I don't mind switching to SQL Server Express since, I can create more robust stored procedures in SQL Server if i can have an individual account file. Do you recommand using in line T-SQL code inside VB>Net or Stored Queries like i am trying to implement?
You can create multiple database on the same instance. (different database on the same SQLserver)
You can also create multiple instances.(different SQLservers on the same computer)

For each database there are 2 files created (a .mdf and a .ldf)
It is possible to drop these files from SQLserver, move them to another computer and attach them there to another SQLserver. But it may be easier to create a backUp on one computer and restore it on the other.
You will have to install SQL Server 2005 Express to host the database.
And I also always install SQL Management Studio 2005 Express for digging around in my tables.

To use stored procedures or not is a discussion I will NOT get in to. :-)
Google the pro and cons and choose for yourself.
pro: http://grokable.com/2003/11/dont-use-stored-procedures-yet-must-be-suffering-from-nihs-not-invented-here-syndrome/
con: http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html


PS: I don't use them.