error in ms access parameterized query

[smriti]

Hi all,

 

I am getting one problem with parameterized queries in Ms access

 

I built one parameterized query in Ms access as follows

 

PARAMETERS Proj Text ( 30 );

SELECT DISTINCT LEFT(PROJ_ID,13), PROJ_NAME

FROM Multiplier

WHERE PROJ_ID Like [Proj] & "*";

 

When I run this query it returns the correct result

 

I am giving name this query to Query1

 

In my frontend application I am calling this as

 

dim dataadapter=new dataadapter("Execute query1 '" & parametervalue & "'",con)

dataadapter.fill(ds,"TableName")

 

When I see the count of my dataset it retuns 0 rows.

 

I cann't understand what is going to be wrong

 

If anyone knows the solution to this , please provide me

 

It's very urgent

 

Thanks in advance

[Thei]

I'm facing the exact same problem. Does anyone know how to solve this?

[Nate Bross]

You may try something like this?

 

Copied from here

'Populate Connection Object
 Dim oCnn As New OleDbConnection(sCnnString)

 'Define our sql query
 Dim sSQL As String = "SELECT FirstName, LastName, Title " & _
                      "FROM Employees " & _
                      "WHERE ((EmployeeID > ? AND HireDate > ?) AND Country = ?)"

 'Populate Command Object
 Dim oCmd As New OledbCommand(sSQL, oCnn)

 'Add up the parameter, associated it with its value
 oCmd.Parameters.Add("EmployeeID", sEmpId)
 oCmd.Parameters.Add("HireDate", sHireDate)
 oCmd.Parameters.Add("Country", sCountry)

 'Opening Connection for our DB operation  
 oCnn.Open()

 'Get the results of our query 
 Dim drEmployee As OleDbDataReader = oCmd.ExecuteReader()

 

If you are trying to execute a query that is built from "Access Query Builder" you may need to change oCmd.Type = Procedure (or or similar) and then call it like you would a Stored Procedure from SQL Server.

[Thei]

Hi Nate Bross,

 

I have the following query in my Access database:

 

PARAMETERS ZoekString Text ( 128 );

SELECT DISTINCT tPersonen.fID AS ID, fAchternaam+IIf(fTussenvoegsel Is Null,'',fTussenvoegsel)+', '+fVoornaam AS NAAM, tPersonen.fAdres AS ADRES, tPersonen.fPostcode AS POSTCODE, tPersonen.fPlaats AS PLAATS, tPersonen.fTelefoon AS TELEFOON, tPersonen.fMobiel AS MOBIEL

FROM ((tPersonen INNER JOIN tLanden ON tPersonen.fLand_ID = tLanden.fID) LEFT JOIN tKoppelingen ON tPersonen.fID = tKoppelingen.fPersoon_ID) LEFT JOIN tCategorien ON tKoppelingen.fCategorie_ID = tCategorien.fID

WHERE tPersonen.fAchternaam LIKE "*" & Zoekstring & "*" OR tPersonen.fTussenvoegsel LIKE "*" & Zoekstring & "*" OR tPersonen.fVoornaam LIKE "*" & Zoekstring & "*" OR tPersonen.fVoorletters LIKE "*" & Zoekstring & "*" OR tPersonen.fAdres LIKE "*" & Zoekstring & "*" OR tPersonen.fPostcode LIKE "*" & Zoekstring & "*" OR tPersonen.fPlaats LIKE "*" & Zoekstring & "*" OR tLanden.fLand LIKE "*" & Zoekstring & "*" OR tPersonen.fTelefoon LIKE "*" & Zoekstring & "*" OR tPersonen.fMobiel LIKE "*" & Zoekstring & "*" OR tPersonen.fEmail LIKE "*" & Zoekstring & "*" OR tPersonen.fOrganisatie LIKE "*" & Zoekstring & "*" OR tPersonen.fOpmerkingen LIKE "*" & Zoekstring & "*" OR tCategorien.fCategorie LIKE "*" & ZoekString & "*";

 

When I run the query in Access it works fine. But in the .NET Application it returns an empty DataSet. I've made a function within a class that handles all data retrieval from the Access database. The function works with all other queries that I've made within the Access database. This is what I do in VB.NET

 

   Function Retrieve_Data(ByVal QueryName As String, ByVal TableName As String, Optional ByVal Param1_Name As String = "", Optional ByVal Param1_Type As OleDbType = OleDbType.IUnknown, Optional ByVal Param1_Size As Integer = 0, Optional ByVal Param1_Value As String = "", Optional ByVal Param2_Name As String = "", Optional ByVal Param2_Type As OleDbType = OleDbType.IUnknown, Optional ByVal Param2_Size As Integer = 0, Optional ByVal Param2_Value As String = "", Optional ByVal Param3_Name As String = "", Optional ByVal Param3_Type As OleDbType = OleDbType.IUnknown, Optional ByVal Param3_Size As Integer = 0, Optional ByVal Param3_Value As String = "") As DataSet
       Dim conn As New OleDbConnection(ConnectionString)
       Dim cmd As New OleDbCommand

       If Param1_Name <> String.Empty Then
           Dim Param1 As New OleDbParameter

           With Param1
               .ParameterName = Param1_Name
               .OleDbType = Param1_Type
               .Size = Param1_Size
               .Value = Param1_Value
           End With

           cmd.Parameters.Add(Param1)
       End If

       If Param2_Name <> String.Empty Then
           Dim Param2 As New OleDbParameter

           With Param2
               .ParameterName = Param2_Name
               .OleDbType = Param2_Type
               .Size = Param2_Size
               .Value = Param2_Value
           End With

           cmd.Parameters.Add(Param2)
       End If

       If Param3_Name <> String.Empty Then
           Dim Param3 As New OleDbParameter

           With Param3
               .ParameterName = Param3_Name
               .OleDbType = Param3_Type
               .Size = Param3_Size
               .Value = Param3_Value
           End With

           cmd.Parameters.Add(Param3)
       End If

       cmd.Connection = conn
       cmd.CommandText = "EXECUTE " & QueryName

       Dim da As New OleDbDataAdapter(cmd)
       Dim ds As New DataSet

       da.Fill(ds, TableName)

       Return ds
   End Function

 

I've tried to change the cmd.CommandType like you suggested, but this didn't work. If possible I don't want to use any SQL Statements within my code so I just have to change the Access Query if nessecary.

[Nate Bross]

Does this method fail for any access query, or only the queries that require parms?

 

I believe that when calling a query made within access, you need to change the CommandType.

 

Try something like this, I think the "EXECUTE" in your command text may be causing issues:

 

objCommand As New System.Data.OleDb.OleDbCommand("QueryName", objConnection) 
objCommand.CommandType = CommandType.StoredProcedure