0

I am trying to bind my gridview to a stored procedure via Linq to SQL but keep getting 'Data source is an invalid type. It must be either an IListSource, IEnumerable, or IDataSource.' I know I am supposed to convert the stored Proc .ToList, but this method is not available to me and throws an error (BC30456: 'toList' is not a member of 'Integer'). How do I get around this and why can I return some Stored procedures right to a gridview but not others?


Linq/.Net

Dim DC As New DistributionDataContext
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            GridView1.DataSource = (DC.usp_GetInvestorData(0, 50, "InvestorName", 0))

            GridView1.DataBind()
        End If

    End Sub

StoredProc

alter Procedure [dbo].[usp_GetInvestorData](
	@page int,
    @page_len int,
    @sortfield varchar(100),
    @desc bit  
)
as
begin
	set nocount on
	declare @rowcount int;
	declare @innerrows int
	select @rowcount=count(*) from dbo.Investors
	
	if (@page*@page_len) > @rowcount
		begin
			set @page=(@rowcount/@page_len)
		end

	set @innerrows = @rowcount - (@page * @page_len)

	
       declare @sortdesc varchar(100)
       declare @sortasc varchar(100)
       declare @a varchar(6)
       declare @b varchar(6)
       IF @desc=0
               BEGIN
                   set @a = ' DESC '
                   set @b = ' ASC '
               END
       ELSE
               BEGIN
                   set @a = ' ASC '
                   set @b = ' DESC '
               END    	
		


       DECLARE @sql nvarchar(1000)
       SET @sql = 'SELECT TOP ' + STR(@page_len) + ' [InvestorName], [InvestorAttention], dbo.fnGetInvestorTotalByID(InvestorID) as InvestorTotal, InvestorID as InvestorID FROM
               (
                   SELECT TOP ' + STR(@innerrows) + ' [InvestorName], 
						[InvestorAttention], 
						dbo.fnGetInvestorTotalByID(InvestorID) as InvestorTotal, 
						InvestorID as InvestorID
       
                   FROM
                   [dbo].[Investors] 
                   ORDER BY [dbo].[Investors].' + @sortfield + @a  + ' 
               ) Alias
               ORDER BY Alias.' + @sortfield + @b   
       EXEC (@sql);
	end
1
Contributor
2
Replies
3
Views
7 Years
Discussion Span
Last Post by MichaelWClark
0

Ok it turns out that the Linq didn't recognize the return type of my stored procedure. I changed the function declaration and added a class to the Linq.Designer.vb file as such:

    <Global.System.Data.Linq.Mapping.FunctionAttribute(Name:="dbo.usp_GetInvestorData")> _
    Public Function usp_GetInvestorData(<Global.System.Data.Linq.Mapping.ParameterAttribute(DbType:="Int")> ByVal page As System.Nullable(Of Integer), <Global.System.Data.Linq.Mapping.ParameterAttribute(DbType:="Int")> ByVal page_len As System.Nullable(Of Integer), <Global.System.Data.Linq.Mapping.ParameterAttribute(DbType:="VarChar(100)")> ByVal sortfield As String, <Global.System.Data.Linq.Mapping.ParameterAttribute(DbType:="Bit")> ByVal desc As System.Nullable(Of Boolean)) As ISingleResult(Of usp_GetInvestorDatasResult)
        Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), page, page_len, sortfield, desc)
        Return CType(result.ReturnValue, ISingleResult(Of usp_GetInvestorDatasResult))
    End Function



Partial Public Class usp_GetInvestorDataResult

    Private _InvestorName As String

    Private _InvestorAttention As String

    Private _InvestorTotal As System.Nullable(Of Decimal)

    Private _InvestorID As Integer

    Public Sub New()
        MyBase.New()
    End Sub

    <Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_InvestorName", DbType:="VarChar(250) NOT NULL", CanBeNull:=False)> _
    Public Property InvestorName() As String
        Get
            Return Me._InvestorName
        End Get
        Set(ByVal value As String)
            If (String.Equals(Me._InvestorName, Value) = False) Then
                Me._InvestorName = Value
            End If
        End Set
    End Property

    <Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_InvestorAttention", DbType:="VarChar(100)")> _
    Public Property InvestorAttention() As String
        Get
            Return Me._InvestorAttention
        End Get
        Set(ByVal value As String)
            If (String.Equals(Me._InvestorAttention, Value) = False) Then
                Me._InvestorAttention = Value
            End If
        End Set
    End Property

    <Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_InvestorTotal", DbType:="Money")> _
    Public Property InvestorTotal() As System.Nullable(Of Decimal)
        Get
            Return Me._InvestorTotal
        End Get
        Set(ByVal value As System.Nullable(Of Decimal))
            If (Me._InvestorTotal.Equals(Value) = False) Then
                Me._InvestorTotal = Value
            End If
        End Set
    End Property

    <Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_InvestorID", DbType:="Int NOT NULL")> _
    Public Property InvestorID() As Integer
        Get
            Return Me._InvestorID
        End Get
        Set(ByVal value As Integer)
            If ((Me._InvestorID = Value) _
               = False) Then
                Me._InvestorID = Value
            End If
        End Set
    End Property
End Class

After these changes she ran like a champ! :)

Edited by mike_2000_17: Fixed formatting

0

UPDATE: Datas should be Data

ISingleResult(Of usp_GetInvestorDatasResult)
ISingleResult(Of usp_GetInvestorDataResult)
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.