Hello

I have a stored procedure witch should return products from a specific table:

ALTER PROCEDURE [dbo].[Browse_selectproduse] 
               -- Add the parameters for the stored procedure here 
               @SqlCommand NVARCHAR(3000), 
               @PageNumber      TINYINT, 
               @ProductsPerPage TINYINT, 
               @HowManyResults  SMALLINT  OUTPUT 
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  @Products  TABLE( 
                              rownumber   SMALLINT    IDENTITY ( 1 , 1 )    NOT NULL, 
                              productid   INT, 
                              username    NVARCHAR(255), 
                              name        VARCHAR(70), 
                              DESCRIPTION VARCHAR(2000), 
                              image1      VARCHAR(200) 
                              ) 
     
    INSERT INTO @Products 
    EXEC @SqlCommand 
     
    SELECT @HowManyResults = Count(* ) 
    FROM   @Products 
     
    SELECT [ProductID], 
           [UserName], 
           [Name], 
           [Description], 
           [Image1] 
    FROM   @Products 
    WHERE  rownumber BETWEEN (@PageNumber - 1) * @ProductsPerPage + 1 AND @PageNumber * @ProductsPerPage 
  END 

Normally, i should replace EXEC @SqlCommand with a SELECT statement that inserts into @Products table the required products.

In code-behind, i am constructing SqlCommand string witch is exactly that SELECT statement, but when i am trying to execute database command i get the folowing error:

The name 'SELECT p.[ProductID], p.[UserName], p.[Name], p.[Description],p.[Image1] FROM [Product] AS p INNER JOIN [ProductSscat] AS s ON p.[ProductID] = s.[ProductID] WHERE s.[SubsubcategoryID] = 502 ORDER BY p.[DateAdded] DESC' is not a valid identifier.

Line 785:        comm.Connection.Open();
Line 786:        // Execute the command and save the results in a DataTable
Line 787:        DbDataReader reader = comm.ExecuteReader();
Line 788:        table = new DataTable();
Line 789:        table.Load(reader);

is there any way to resolve my problem?

Thank you

Hi,

in the stored procedure where you are writing exec @sqlcommand relpace it with exec (@sqlcommand) and try.

Great!!!! thanks!!!

hi,good day

i think u querry is correct otherwise the error will be different so u r free of u querry worry ,so now as u r using stored procedure , first make sure u r really in need of that as many of time it may creat prob. on extracting multiple join etc ,so if u can solve u purpose bu using table & simple querry then u can follow that way,& u can make similar querry in other places (as that time u r free of u error) i hope u can solve u problem,if that stills then u can look for the online help.

ok good luck bye

hello

soumendu:

first make sure u r really in need of that as many of time it may creat prob

what do you mean that can create problems? stored procedure or the way i am executing sql command?

thank you

NOO ya

its for simply i had write that .. create problems?  i mean that in later part if u want to use that u procedure(that u definitely want to use) ,then that time also,that can fall u in trouble(if that hurt u ,then sorry from me),& also u 's approach is correct,only thing i just want to tell that if u can change the way u make u querry then the result may be different ,so good luck to u .ok bye

don't worry, you didn't hurt my feelings