AccessDataSource Error Data type mismatch in criteria expression.

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] 
        Where   
        (@IdParam IS NULL OR [ID]=@IdParam  ) OR
         (@SpecialtyParam IS NULL OR [Specialty]=@SpecialtyParam ) "

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

Open in new window

LVL 9
TonyRebaAsked:
Who is Participating?
 
Bob LearnedConnect With a Mentor Commented:
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
http://msdn.microsoft.com/en-us/library/8e5545e1.aspx

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.
0
 
BitsqueezerCommented:
Hi,

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.

Cheers,

Christian
0
 
TonyRebaAuthor Commented:
Thedatatypes werethe problem thank. You. Both
0
All Courses

From novice to tech pro — start learning today.