error in ms access parameterized query

S

smriti

Well-known member
Joined
Jan 7, 2005
Messages
54
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 cannt understand what is going to be wrong

If anyone knows the solution to this , please provide me

Its very urgent

Thanks in advance
 
You may try something like this?

Copied from here
Code: 
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.
 
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. Ive made a function within a class that handles all data retrieval from the Access database. The function works with all other queries that Ive made within the Access database. This is what I do in VB.NET

Code: 
    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

Ive tried to change the cmd.CommandType like you suggested, but this didnt work. If possible I dont want to use any SQL Statements within my code so I just have to change the Access Query if nessecary.
 
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:

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