• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1428
  • Last Modified:

Linq to Sql Return data from Stored Procedure

I have an example of a procedure I use that works fine. But when I add it to my DataContext and try create a query to retrieve the data I get the following msg:
Expression of type integer is not queryable.

Using VB 2008 framework 3.5 SQL Server 2005
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
alter PROCEDURE [dbo].[uspStatus] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @StatusInfo table (
	RefID int Identity(1,1) Primary key not null,
	RefNum varchar(4) not null,
	Grade varchar(30),
	Qty int not null
	
	insert into @StatusInfo
	SELECT  RefNum, 'LG 1',
			sum(quantity) as Qty, 
	FROM  refStatus 
	WHERE (ref = 'AAAA') 
	Group by RefNum


	insert into @StatusInfo
	SELECT  RefNum, 'LG 2',
			sum(quantity) as Qty, 
	FROM  refInfo 
	WHERE (ref = 'ZZZZ') 
	Group by RefNum

	select *
	From  @StatusInfo

END
GO

Open in new window

0
MadIce
Asked:
MadIce
  • 7
  • 3
2 Solutions
 
Bob LearnedCommented:
Did you follow these guidelines?

http://msdn.microsoft.com/en-us/library/bb763092.aspx

1.Add an Import statement for the System.Linq, System.Data.Linq, or System.Xml.Linq namespace to your code file. You can also import namespaces for your project by using the References page of the Project Designer (My Project).

2.Ensure that the type that you have identified as the source of your query is a queryable type. That is, a type that implements IEnumerable<(Of <(T>)>) or IQueryable<(Of <(T>)>).
0
 
MadIceAuthor Commented:
TheLearnedOne, I had the namespaces set up. Not sure of the syntax for IEnumerable<(Of <(T>)>) or IQueryable<(Of <(T>)>).  Do you have an example?
0
 
MadIceAuthor Commented:
I don't have the integer issue anymore but can't seem to query the procedure correctly.  In code below, when it gets to the count, I get the following
Unable to cast object of type 'System.Int32' to type 'System.String'.
if I skip to the for each I get the following
The query results cannot be enumerated more than once.

If I add .AsQueryable to the end of the name of the procedure, I get the same msgs.

What am I missing/doing wrong?
 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim RefNames = From R In dc.uspStatus _
                       Select R

          Dim i As Integer = RefNames.Count

        ResultsTable = New DataTable("Results")

            'Create the cols for the table
        ResultsTable.Columns.Add("Num", GetType(String))
        Dim dr As DataRow = ResultsTable.NewRow
        For Each tref In RefNames
            dr("Num") = tref.RefNum

        Next
    End Sub

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
AshokCommented:
If you still did not change your original Stored Procedure, your Stored Procedure is RETURNING an integer, not RESULTSET as expected.

To confirm this, open your DBML file created by Linq to Sql class creation.

And find your Stored Procedure return code and check.

HTH
Ashok
0
 
MadIceAuthor Commented:
I'm not at work to check, but would it be the same as executing the procedure?  I get records back when I execute. I'll have to check the rest tomorrow..
0
 
MadIceAuthor Commented:
When I execute the code I get the results and then this
No rows affected.
(14 row(s) returned)
@RETURN_VALUE = 0
0
 
MadIceAuthor Commented:
I've changed my procedure to create a union Query as oppose to storing the results into a table variable. Now it works as expected. This solves my current issue but most of the time I won't be able to do union queries. Most of the time, I have to pull data from multiple databases not just one and I have to do calculations and stuff where I have to use a temp table or table variables. So I would still like to figure out what I'm doing wrong.
0
 
AshokCommented:
When I execute the code I get the results and then this
No rows affected.
(14 row(s) returned)
@RETURN_VALUE = 0

When you say this, where are you seeing 14 rows returned?  If in MS-SQL Server Management Studio, that is not good enough.  I was having this type of problems, where "MS-SQL Server Management Studio" was showing actual RESULTSET, but Linq-To-Sql was not working.

HTH
Ashok
0
 
MadIceAuthor Commented:
I'm executing it from Server Explorer.
When you mentioned "And find your Stored Procedure return code and check."
not sure what to do.
0
 
AshokCommented:
When you create Linq To Sql, it creates DBML for you.

Let's say you named your DBML file abc.dbml

I want to you to inspect code in abc.designer.cs (which could be found under abc.dbml).
After openning this file, search for your stored procedure name, for example "usp_myStoredProc".

HTH
Ashok
0
 
MadIceAuthor Commented:
I haven't had time to continue looking into this, but I believe this is will resolve or at least lead me the right direction now that I understand where to look. Thank you and sorry for the delay...
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now