AccessDataSource  Error Data type mismatch in criteria expression.

Posted on 2011-04-28
Last Modified: 2012-05-11
can someone help me troubleshooting this error, what I am doing wrong?
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/Providers.mdb"
        DeleteCommand = "DELETE * From [tbl_Providers] WHERE [ID] = ?" 
        UpdateCommand="UPDATE [tbl_Providers] SET [Provider_Type] = ?, [Last_Name] = ?, [First_Name] = ?, [Prof_Designation] = ?, [Group_Business_Name] = ?, [Specialty] = ?, [Specialty2] = ?, [Address] = ?, [Address2] = ?, [City] = ?, [State] = ?, [Zip] = ?, [Phone] = ?, [Fax] = ? WHERE [ID] = ? "    
        SelectCommand="SELECT [Id],[First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax],[Provider_Type],[Group_Business_Name] FROM [tbl_Providers] 
        (@IdParam IS NULL OR [ID]=@IdParam  ) OR
         (@SpecialtyParam IS NULL OR [Specialty]=@SpecialtyParam ) "

            <asp:ControlParameter ControlID="NameDropDown" Name="IdParam" 
                  PropertyName="SelectedValue" ConvertEmptyStringToNull="true" />
                <asp:ControlParameter ControlID="specialityDropDown" Name="SpecialtyParam" 
                  PropertyName="SelectedValue" ConvertEmptyStringToNull="true" />

Open in new window

Question by:TonyReba
    LVL 24

    Expert Comment


    I am no expert in ASP but in Access you can't use variable names beginning with "@", that's only possible in SQL Server. Moreover also in SQL Server it would not work if you do not declare the parameters anywhere before. In Access you can use "PARAMETERS" as first line and add all variable names (without "@") which you want to use later and their datatype (see Access help for details). But that normally only works in saved queries in Access, not in dynamic SQL strings.


    LVL 96

    Accepted Solution

    The AccessDataSource inherits from SqlDataSource, and provides access to the OLE DB libraries.  It does not support named arguments, as suggested.  The question marks are place holders, and parameters are matched up in ordinal position.

    Retrieving Data Using the AccessDataSource Web Server Control

    You can use parameterized queries for commands and supply parameters at run time. Additionally, you can specify parameters at run time when calling a Microsoft Access query. (To call a Microsoft Access query, you set the command type property for the command to StoredProcedure). For more information, see Using Parameters with Data Source Controls.
    Because the AccessDataSource control extends the SqlDataSource class and uses the System.Data.OleDb provider, you specify parameter placeholders using the "?" placeholder character. The System.Data.OleDb provider does not support named parameters; instead, the parameter values are applied in the order they are specified in the parameters collection. You must ensure that the order of the parameters in the parameters collection matches the order of the parameter placeholders in your SQL statement or Microsoft Access query. For more information and examples, see Using Parameters with the SqlDataSource Control.
    LVL 9

    Author Comment

    Thedatatypes werethe problem thank. You. Both

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now