locked
Parameter error but no parameters!! RRS feed

  • Question

  • User-1307887978 posted

    I have a SQLDataSource which is linked to an Access back end. I have done this with success multiple times, but for some reason with a new one I just made, I am getting the following error: "No Value Given for one or more required parameters."

    The problem is that the SQL has NO parameters!! I cut and paste the SQL exactly from Access 2010, where the query runs just fine. When I'm in the Configure Data Source wizard in Visual Studio 2010, and I click on "Test Query" the query runs just fine. It's only when I actually try to load the page that I get the error. I can't figure it out.

    Here is the SQL:

    SELECT [Cuts/Lates Table].Date_of_Infraction, [Students_Table].[Last_Name] & ", " & [Students_Table].[First_Name] AS Full_Name, [Cuts/Lates Table].Period, Course_List.Course_Name, [Teacher_Codes_Table].[Last_Name] & ", " & [Teacher_Codes_Table].[Title] & " " & [Teacher_Codes_Table].[First_Name] AS Teacher_Name, IIf(IsNull([Cuts/Lates Table].[Status]) Or [Cuts/Lates Table].[Status]="","Pending",[Cuts/Lates Table].[Status]) AS Current_Status, [Cuts/Lates Table].Rediker_ID, IIf(Left([Cuts/Lates Table].[Infraction],3)="LAT","LATE",IIf(Left([Cuts/Lates Table].[Infraction],3)="CUT","CUT",[Cuts/Lates Table].[Infraction])) AS Issue, [Cuts/Lates Table].Teacher_ID
    FROM (((([Cuts/Lates Table] INNER JOIN Students_Table ON [Cuts/Lates Table].Rediker_ID = Students_Table.Rediker_ID) INNER JOIN Teacher_Codes_Table ON [Cuts/Lates Table].Teacher_ID = Teacher_Codes_Table.Teacher_ID) INNER JOIN Course_List ON [Cuts/Lates Table].Course_Number = Course_List.Course_Number) LEFT JOIN Out_Of_Class_Records ON [Cuts/Lates Table].Meeting_ID = Out_Of_Class_Records.ID) LEFT JOIN Teacher_Codes_Table AS Teacher_Codes_Table_1 ON Out_Of_Class_Records.Teacher_ID = Teacher_Codes_Table_1.Teacher_ID
    WHERE (((Year(DateAdd("m",5,[Cuts/Lates Table].[Date_of_Infraction])))=Year(DateAdd("m",5,Date()))) AND ((IIf(Left([Cuts/Lates Table].[Infraction],3)="LAT","LATE",IIf(Left([Cuts/Lates Table].[Infraction],3)="CUT","CUT",[Cuts/Lates Table].[Infraction])))="CUT" Or (IIf(Left([Cuts/Lates Table].[Infraction],3)="LAT","LATE",IIf(Left([Cuts/Lates Table].[Infraction],3)="CUT","CUT",[Cuts/Lates Table].[Infraction])))="LATE"))
    ORDER BY [Cuts/Lates Table].Date_of_Infraction, [Students_Table].[Last_Name] & ", " & [Students_Table].[First_Name], [Cuts/Lates Table].Period

    (Yes, it's complicated, but it runs fine within Access, and also within the Configure DataSource wizard!!)

    Here are my declarations:

    <asp:SqlDataSource ID="PastAttendanceDataSource" runat="server"
                ConnectionString="<%$ ConnectionStrings:Attendance Back End Connection String %>"
                    
                ProviderName="<%$ ConnectionStrings:Attendance Back End Connection String.ProviderName %>" SelectCommand="SELECT [Cuts/Lates Table].Date_of_Infraction, [Students_Table].[Last_Name] &amp; &quot;, &quot; &amp; [Students_Table].[First_Name] AS Full_Name, [Cuts/Lates Table].Period, Course_List.Course_Name, [Teacher_Codes_Table].[Last_Name] &amp; &quot;, &quot; &amp; [Teacher_Codes_Table].[Title] &amp; &quot; &quot; &amp; [Teacher_Codes_Table].[First_Name] AS Teacher_Name, IIf(IsNull([Cuts/Lates Table].[Status]) Or [Cuts/Lates Table].[Status]=&quot;&quot;,&quot;Pending&quot;,[Cuts/Lates Table].[Status]) AS Current_Status, [Cuts/Lates Table].Rediker_ID, IIf(Left([Cuts/Lates Table].[Infraction],3)=&quot;LAT&quot;,&quot;LATE&quot;,IIf(Left([Cuts/Lates Table].[Infraction],3)=&quot;CUT&quot;,&quot;CUT&quot;,[Cuts/Lates Table].[Infraction])) AS Issue, [Cuts/Lates Table].Teacher_ID
    FROM (((([Cuts/Lates Table] INNER JOIN Students_Table ON [Cuts/Lates Table].Rediker_ID = Students_Table.Rediker_ID) INNER JOIN Teacher_Codes_Table ON [Cuts/Lates Table].Teacher_ID = Teacher_Codes_Table.Teacher_ID) INNER JOIN Course_List ON [Cuts/Lates Table].Course_Number = Course_List.Course_Number) LEFT JOIN Out_Of_Class_Records ON [Cuts/Lates Table].Meeting_ID = Out_Of_Class_Records.ID) LEFT JOIN Teacher_Codes_Table AS Teacher_Codes_Table_1 ON Out_Of_Class_Records.Teacher_ID = Teacher_Codes_Table_1.Teacher_ID
    WHERE (((Year(DateAdd(&quot;m&quot;,5,[Cuts/Lates Table].[Date_of_Infraction])))=Year(DateAdd(&quot;m&quot;,5,Date()))) AND ((IIf(Left([Cuts/Lates Table].[Infraction],3)=&quot;LAT&quot;,&quot;LATE&quot;,IIf(Left([Cuts/Lates Table].[Infraction],3)=&quot;CUT&quot;,&quot;CUT&quot;,[Cuts/Lates Table].[Infraction])))=&quot;CUT&quot; Or (IIf(Left([Cuts/Lates Table].[Infraction],3)=&quot;LAT&quot;,&quot;LATE&quot;,IIf(Left([Cuts/Lates Table].[Infraction],3)=&quot;CUT&quot;,&quot;CUT&quot;,[Cuts/Lates Table].[Infraction])))=&quot;LATE&quot;))
    ORDER BY [Cuts/Lates Table].Date_of_Infraction, [Students_Table].[Last_Name] &amp; &quot;, &quot; &amp; [Students_Table].[First_Name], [Cuts/Lates Table].Period"
                >
            </asp:SqlDataSource>

    Any insight would be greatly appreciated!!

    Wednesday, September 12, 2012 2:45 PM

Answers

  • User-821857111 posted

    @mdyhs: You can save a query inside Access and use a SqlDataSource to invoke it by its name if you want to reduce the character count in the ASPX file - and that would also get round the character encoding issue. You just need to set the SelectCommandType to StoredProcedure.

    @Decker: DbDataAdapters work with DataSets and DataTables. Writing a method to fill a DataSet and then using an ObjectDataSource to bind that DataSet to a control is completely pointless. The SqlDataSource does all that for you. And your suggestion doesn't help resolve the issue which is to do with SQL.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 14, 2012 3:15 AM
  • User-1199946673 posted

    especially as the AccessDataSource doesn't support accdb files

    Since .NET 4.0, the AccessDatasource does support accdb files, although not documented on MSDN

    	Friend Property IsAccess2007 As Boolean
    		Get
    			Return Path.GetExtension(Me.PhysicalDataFile) = ".accdb"
    		End Get
    	End Property
    
    	Friend Property NativeProvider As String
    		Get
    			If (Me.IsAccess2007) Then
    				Return "Microsoft.ACE.OLEDB.12.0"
    			End If
    			Return "Microsoft.Jet.OLEDB.4.0"
    		End Get
    	End Property
    
    Private Function CreateConnectionString() As String 
    	Return String.Concat("Provider=", Me.NativeProvider, "; Data Source=", Me.PhysicalDataFile)
    End Function
    

    Although the ProviderName property will always return "Microsoft.Jet.OLEDB.4.0"

    Public Property ProviderName As String
    	Get
    		Return "System.Data.OleDb"
    	End Get
    	Set(ByVal value As String)
    		Throw New InvalidOperationException(SR.GetString(Dim d As Object()(0) = MyBase.ID, d))
    	End Set
    End Property
    
    



     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 14, 2012 4:15 AM

All replies

  • User-821857111 posted

    Normally you get that error message because you mis-spelt a column name in your SQL. In your case, it may be that your SQL double quotes and ampersands have been converted to HTML entities (&quot; and &amp;) in the ASPX. 

    Wednesday, September 12, 2012 4:55 PM
  • User3866881 posted

    mdyhs

    Hi,

    I have a SQLDataSource which is linked to an Access back end.

    I found that your Select statement seems to be very long and complicated……I suggest you change to use objectdatasource and then expose some public methods to do selecting to bind to the GridView with the help of OleDbDataAdapter,with a common query defined inside Access database.

    Reguards!

    Thursday, September 13, 2012 9:34 PM
  • User-1199946673 posted

    First I think since you're using Access database,you'd better use AccessDataSource instead of SqlDataSource

    Why? An AccessDatasource inherits from a SqlDataSource! When using a password protected access database, you can't use an AccessDatasource

    I found that your Select statement seems to be very long and complicated

    I don't think is complicated, it is just a bit hard to read. But even if this would be trye, why would this be a problem?

    Thursday, September 13, 2012 9:58 PM
  • User-821857111 posted

    you'd better use AccessDataSource instead of SqlDataSource.

    I advise completely the opposite (http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET) especially as the AccessDataSource doesn't support accdb files (which the OP is presumably using since he mentions Access 2010).

    Friday, September 14, 2012 12:45 AM
  • User3866881 posted

    I don't think is complicated, it is just a bit hard to read. But even if this would be trye, why would this be a problem?

    I don't think that's a problem……But I don't think it suitable to write such a long statement inside the aspx page directly……And it would be better to put this inside a Query and let ObjectDataSource to call with the help of OleDbCommand……

    Many thanks for hans_v and Mikesdonetting……yes——AccessDataSource cannot be applied to accdb. So I've changed my mind so as to let client use ObjectDataSource+OleDbDataAdapter

    Friday, September 14, 2012 1:52 AM
  • User-821857111 posted

    @mdyhs: You can save a query inside Access and use a SqlDataSource to invoke it by its name if you want to reduce the character count in the ASPX file - and that would also get round the character encoding issue. You just need to set the SelectCommandType to StoredProcedure.

    @Decker: DbDataAdapters work with DataSets and DataTables. Writing a method to fill a DataSet and then using an ObjectDataSource to bind that DataSet to a control is completely pointless. The SqlDataSource does all that for you. And your suggestion doesn't help resolve the issue which is to do with SQL.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 14, 2012 3:15 AM
  • User-1199946673 posted

    especially as the AccessDataSource doesn't support accdb files

    Since .NET 4.0, the AccessDatasource does support accdb files, although not documented on MSDN

    	Friend Property IsAccess2007 As Boolean
    		Get
    			Return Path.GetExtension(Me.PhysicalDataFile) = ".accdb"
    		End Get
    	End Property
    
    	Friend Property NativeProvider As String
    		Get
    			If (Me.IsAccess2007) Then
    				Return "Microsoft.ACE.OLEDB.12.0"
    			End If
    			Return "Microsoft.Jet.OLEDB.4.0"
    		End Get
    	End Property
    
    Private Function CreateConnectionString() As String 
    	Return String.Concat("Provider=", Me.NativeProvider, "; Data Source=", Me.PhysicalDataFile)
    End Function
    

    Although the ProviderName property will always return "Microsoft.Jet.OLEDB.4.0"

    Public Property ProviderName As String
    	Get
    		Return "System.Data.OleDb"
    	End Get
    	Set(ByVal value As String)
    		Throw New InvalidOperationException(SR.GetString(Dim d As Object()(0) = MyBase.ID, d))
    	End Set
    End Property
    
    



     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 14, 2012 4:15 AM
  • User-821857111 posted

    Since .NET 4.0, the AccessDatasource does support accdb files, although not documented on MSDN

    Thanks. I didn't know that. I should update my old article.

    Friday, September 14, 2012 6:44 AM