Can't find Count(*) field in form

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Can't find Count(*) field in form

Author

Topic

dariggy
Starting Member

2 Posts

Posted - 2010-03-22 : 01:04:20

I have created a stored procedure that works when executed in SQL Sever 2008 but the field cannot be found in my asp VB form. The important part are the 2 EXEC statements at the end of the following code:


USE [kstech]
GO
/****** Object:  StoredProcedure [dbo].[sp_20_1_0_10_Get_Entity_All]    Script Date: 03/21/2010 14:10:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		KG 1
-- Create date: 12/30/2010
-- Description:	Get Entitys related to Client ID that Includes Primary Contacts if they have one
-- =============================================
ALTER PROCEDURE [dbo].[sp_20_1_0_10_Get_Entity_All]
	
	@Entity_ID varchar(50),
	@Client_ID int=null,
	@Role varchar(20)=null,
	@Entity_Type char(1)=null,
	@e_type int=null,
	@e_cat int=null,
	@e_active char(1)=null,
	@Order_By int=null,
	@filter_0 varchar(15)=null,
	@filter_1 varchar(15)=null,
	@filter_2 varchar(15)=null,
	@filter_3 varchar(15)=null,
	@filter_4 varchar(15)=null,
	@filter_5 varchar(15)=null,
	@filter_6 varchar(15)=null,
	@filter_7 varchar(15)=null,
	@start_page int,
	@display_records int

AS
BEGIN
	
SET NOCOUNT ON
DECLARE @Query varchar(1000)
DECLARE @Select_Query varchar(1000)
DECLARE @Order_Query varchar(1000)
DECLARE @Filter_Query varchar(1000)
DECLARE @Page_Query varchar(1000)
DECLARE @Char_N varchar(1)
DECLARE @start_rec int
DECLARE @end_rec int
SET @start_rec = (@display_records *  (@start_page - 1)) + 1
SET @end_rec = @display_records * @start_page
SET @Char_N = 'N'

begin
	CREATE TABLE [dbo].[#Entity_Master](
		[ID] [int]  NULL,
		[Client_ID] [int] NULL,
		[Entity_ID] [int] NULL,
		[Flag_Active] [nchar] (1) NULL,
		[Entity_Name] [nvarchar](75) NULL,
		[Entity_Type] [nchar](30)  NULL)
End

begin
	CREATE TABLE [dbo].[#Entity_Master_2](
		[T_ID] [int] IDENTITY,
		[ID] [int]  NULL,
		[Client_ID] [int] NULL,
		[Entity_ID] [int] NULL,
		[Flag_Active] [nchar] (1) NULL,
		[Entity_Name] [nvarchar](75) NULL,
		[Entity_Type] [nchar](30) NULL,
		[ID_Category] [int] NULL,
		[Description_Category] [nvarchar](75) NULL,
		[ID_Sub_Category] [int] NULL,
		[Description_Sub_Category] [nvarchar](75) NULL,
		[ID_Contact] [int] NULL,
		[Entity_Name_Contact] [nvarchar](75) NULL,
		[Phone] [nvarchar](30) NULL,
		[Email_Address] [nvarchar](75) NULL)
End

if @Entity_Type='C' 
begin
	SET @Query = 'INSERT INTO #Entity_Master select ID'
	+ ',' + 'Client_ID'
	+ ',' + 'Entity_ID'
	+ ',' + 'Flag_Active'
	+ ',' + 'Entity_Name'
	+ ',' + 'Entity_Type'
	+ ' FROM Entity_master em'
	+ ' WHERE em.Client_ID= ' + ltrim(str(@Client_ID)) 
	+ ' AND (em.Entity_Type=''' + 'Broker' + ''' or em.Entity_Type=''' + 'Client' +  ''' or em.Entity_Type=''' + 'Distributor' + ''')' 

	PRINT @Query
	EXEC (@Query)

	SET @Query = 'INSERT INTO #Entity_Master_2'
	+ ' select em.ID as E_ID'
	+ ',' + 'em.Client_ID'
	+ ',' + 'em.Entity_ID'
	+ ',' + 'em.Flag_Active'
	+ ',' + 'em.Entity_Name'
	+ ',' + 'em.Entity_Type'
	+ ',' + 'ec.ID as Cat_ID'
	+ ',' + 'ec.Description as C_Desc'
	+ ',' + 'es.ID as Sub_ID'
	+ ',' + 'es.Description'
	+ ',' + 'cn.ID'
	+ ',' + 'cn.Entity_Name_Contact'
	+ ',' + 'cn.Phone'
	+ ',' + 'cn.Email_Address'
	+ ' FROM Entity_master em'
	+ ' Inner JOIN #Entity_Master tm ON tm.entity_id = em.client_id'
	+ ' LEFT JOIN Support_Entity_InDirect_Category ec ON em.ID_Category= ec.ID' 
	+ ' LEFT JOIN Support_Entity_InDirect_Sub_Category es ON em.ID_Sub_Category= es.ID' 
	+ ' LEFT JOIN Contact cn ON em.ID = cn.ID_Entity_Master AND cn.Type_Contact = ''' + 'Primary' + '''' 
	+ ' WHERE em.Entity_Type <> ''' + 'Customer' + ''''
	if @Order_By = 01 SET @Query = @Query + ' ORDER By ID ASC'
	if @Order_By = 02 SET @Query = @Query + ' ORDER By ID DESC'
	if @Order_By = 11 SET @Query = @Query + ' ORDER By Entity_Name ASC'
	if @Order_By = 12 SET @Query = @Query + ' ORDER By Entity_Name DESC'
	if @Order_By = 21 SET @Query = @Query + ' ORDER By Entity_Type, Entity_Name ASC'
	if @Order_By = 22 SET @Query = @Query + ' ORDER By Entity_Type DESC, Entity_Name ASC'
	if @Order_By = 31 SET @Query = @Query + ' ORDER By ec.Description, Entity_Name ASC'
	if @Order_By = 32 SET @Query = @Query + ' ORDER By ec.Description DESC, Entity_Name'
	if @Order_By = 41 SET @Query = @Query + ' ORDER By es.Description, Entity_Name ASC'
	if @Order_By = 42 SET @Query = @Query + ' ORDER By es.Description DESC,Entity_Name'
	if @Order_By = 51 SET @Query = @Query + ' ORDER By Entity_name_contact, Entity_Name ASC'
	if @Order_By = 52 SET @Query = @Query + ' ORDER By Entity_name_contact DESC, Entity_Name'
	if @Order_By = 61 SET @Query = @Query + ' ORDER By Phone, Entity_Name ASC'
	if @Order_By = 62 SET @Query = @Query + ' ORDER By Phone DESC, Entity_Name'
	if @Order_By = 71 SET @Query = @Query + ' ORDER By Email_Address, Entity_Name ASC'
	if @Order_By = 72 SET @Query = @Query + ' ORDER By Email_Address DESC, Entity_Name'
	
	PRINT @Query
	EXEC (@Query)
		
end

if @Entity_Type='B' 
begin
	insert into #Entity_Master_2
		Select em.ID,
		em.Client_ID,
		em.Entity_ID,
		em.Flag_Active,
		em.Entity_Name,
		em.Entity_Type,
		em.ID_Category,
		em.ID_Sub_Category
		
	 from Entity_master em
		where (em.Client_ID= @Entity_ID) OR (em.Entity_ID= @Entity_ID) 

end

if @Entity_Type='A' 
begin
	insert into #Entity_Master_2
		Select em.ID,
		em.Client_ID,
		em.Entity_ID,
		em.Flag_Active,
		em.Entity_Name,
		em.Entity_Type,
		em.ID_Category,
		em.ID_Sub_Category
		
	 from Entity_master em
		where em.Entity_ID= @Entity_ID and em.Entity_Type <> 'Customer'


end

SET @Select_Query = 'SELECT T_ID'
+ ',' + 'ID'
+ ',' + 'Client_ID'
+ ',' + 'Entity_ID'
+ ',' + 'Flag_Active'
+ ',' + 'Entity_Name'
+ ',' + 'Entity_Type'
+ ',' + 'ID_Category'
+ ',' + 'ID_Sub_Category'
+ ',' + 'Description_Category'
+ ',' + 'Description_Sub_Category'
+ ',' + 'Entity_Name_Contact'
+ ',' + 'Phone'
+ ',' + 'Email_Address'
+ ' From #Entity_Master_2 '

SET  @Page_Query = ' WHERE T_ID BETWEEN ' + STR(@start_rec) + ' AND ' + STR(@end_rec)


if @e_type <> 0
begin
	if @e_type = 1 SET @Filter_Query = @Filter_Query + ' AND entity_type = ''' + 'Distributor' + ''''
	if @e_type = 2 SET @Filter_Query = @Filter_Query + ' AND entity_type = ''' + 'Broker' + ''''
	if @e_type = 3 SET @Filter_Query = @Filter_Query + ' AND entity_type = ''' + 'Delivery Agent' + ''''
	if @e_type = 4 SET @Filter_Query = @Filter_Query + ' AND entity_type = ''' + 'Shipping Agent' + ''''
	if @e_type = 5 SET @Filter_Query = @Filter_Query + ' AND entity_type = ''' + 'Warehouse' + ''''
end
if @e_cat <> 0 
SET @Filter_Query = @Filter_Query + ' and ID_Category = ' + LTRIM(STR(@e_cat))	

if @e_active <> 0
begin
	if @e_active = 1 SET @Filter_Query = @Filter_Query + ' AND Flag_Active = ''' + 'Y' + ''''
	if @e_active = 2 SET @Filter_Query = @Filter_Query + ' AND (Flag_Active = ''' + 'N' + ''''+ ' or Flag_Active = ''' + '' + '''' + ' or Flag_Active IS NULL)'
end


if @filter_0 <> '' SET @Filter_Query = @Filter_Query + ' AND LTRIM(STR(ID)) LIKE ''' + @filter_0 + '%' + ''''
if @filter_1 <> '' SET @Filter_Query = @Filter_Query + ' AND entity_name LIKE ''' + @filter_1 + '%' + ''''
if @filter_2 <> '' SET @Filter_Query = @Filter_Query + ' AND Entity_Type LIKE ''' + @filter_2 + '%' + ''''
if @filter_3 <> '' SET @Filter_Query = @Filter_Query + ' AND Description LIKE ''' + @filter_3 + '%' + ''''
if @filter_4 <> '' SET @Filter_Query = @Filter_Query + ' AND Description LIKE ''' + @filter_4 + '%' + ''''
if @filter_5 <> '' SET @Filter_Query = @Filter_Query + ' AND Entity_name_contact LIKE ''' + @filter_5 + '%' + ''''
if @filter_6 <> '' SET @Filter_Query = @Filter_Query + ' AND Phone LIKE ''' + @filter_6 + '%' + ''''
if @filter_7 <> '' SET @Filter_Query = @Filter_Query + ' AND Email_Address LIKE ''' + @filter_7 + '%' + ''''

if @Order_By = 01 SET @Order_Query = ' ORDER By ID ASC'
if @Order_By = 02 SET @Order_Query = ' ORDER By ID DESC'
if @Order_By = 11 SET @Order_Query = ' ORDER By Entity_Name ASC'
if @Order_By = 12 SET @Order_Query = ' ORDER By Entity_Name DESC'
if @Order_By = 21 SET @Order_Query = ' ORDER By Entity_Type, Entity_Name ASC'
if @Order_By = 22 SET @Order_Query = ' ORDER By Entity_Type DESC, Entity_Name ASC'
if @Order_By = 31 SET @Order_Query = ' ORDER By Description_Category, Entity_Name ASC'
if @Order_By = 32 SET @Order_Query = ' ORDER By Description_Category DESC, Entity_Name'
if @Order_By = 41 SET @Order_Query = ' ORDER By Description_Sub_Category, Entity_Name ASC'
if @Order_By = 42 SET @Order_Query = ' ORDER By Description_Sub_Category DESC,Entity_Name'
if @Order_By = 51 SET @Order_Query = ' ORDER By Entity_name_contact, Entity_Name ASC'
if @Order_By = 52 SET @Order_Query = ' ORDER By Entity_name_contact DESC, Entity_Name'
if @Order_By = 61 SET @Order_Query = ' ORDER By Phone, Entity_Name ASC'
if @Order_By = 62 SET @Order_Query = ' ORDER By Phone DESC, Entity_Name'
if @Order_By = 71 SET @Order_Query = ' ORDER By Email_Address, Entity_Name ASC'
if @Order_By = 72 SET @Order_Query = ' ORDER By Email_Address DESC, Entity_Name'

begin
PRINT (@Select_Query + @Page_Query + @filter_Query + @Order_Query)
EXEC (@Select_Query + @Page_Query + @filter_Query + @Order_Query)
EXEC ('SELECT COUNT(*) as rec_count From #Entity_Master_2 ' + @filter_Query)
end
Drop table [#Entity_Master]
Drop table [#Entity_Master_2]
END

When executed in SQL, it returns the record count of 95:
sp_20_1_0_10_Get_Entity_All '2','2','adm','C','0','0','0','01','','','','','','','','','1','10'

when executed in my ASP VB form:
strSQL="sp_20_1_0_10_Get_Entity_All '" & session("entity_ID") & "','" & session("client_id") & "','" & session("role") & "','" & Type_Passed & "','" & filter_id_type & "','" & filter_id_cat & "','" & filter_id_active & "','" & sort_id & "','" & sort_field(0) & "','" & sort_field(1) & "','" & sort_field(2) & "','" & sort_field(3) & "','" & sort_field(4) & "','" & sort_field(5) & "','" & sort_field(6) & "','" & sort_field(7) & "','" & start_page & "','" & display_records & "'"
response.Write strSQL
rsEntity.open strSQL, adoCon
response.Write "rec_count:"
response.Write rsEntity("rec_count")

The field rec_count is not found. Any help would be appreciated.....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-22 : 02:26:14

I am not familiar with this programming so maybe I am wrong but I think the problem is that you can't receive two record sets from a stored procedure into your application.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-03-22 : 04:02:47

Two Record Sets from an Sproc is fine, but you have to do "rsEntity.NextRecordset" to get to the second one.

Some things to point out:

You are using parameter positioning, rather than named parameters, in your call - which tends to lead to bugs as changes are made.

Your definition of VARCHAR(1000) for the SQL strings seems arbitrary, any longer string will cause run time errors- Why not use VARCHAR(8000) (no penalty) or perhaps VARCHAR(MAX) - possibly some penalty, but probably only if >8,000 characters actually used

This query is at high risk of SQL Injection (if you are not familiar with that please Google it, your application is at risk of people hacking your database)

If would be better that you used a parametrised call to the SProc from your application, rather than executing SQL string.

I don't see why you need dynamic SQL in the Sproc to make this query (particularly as you are using SQL2008, and provided you have latest Cumulative Update for Service Pack 1 (or SP2 - if you are reading this in the months to come!) as it is optimised to allow multiple parameters in WHERE clause).

But if you are using dynamic SQL you should us ea parametrised query, otherwise there are serious performance issues because the query will not be using a cached query plan.

Dynamic SQL like this requires SELECT permissions on the underlying table (whereas you could just GRANT PERMISSION TO execute THE Sproc, which has much lower impact on the risks for the system

dariggy
Starting Member

2 Posts

Posted - 2010-03-22 : 11:01:10

Kristen,

Thank you for the thorough response. I need to do some homework on many of your suggestions but they are very useful.

My website was developed in ASP VB but I am not quite sure I can perform the function as you stated. I may have it defined incorrectly but I get this error:

ADODB.Recordset error '800a0cb3'

Current provider does not support returning multiple recordsets from a single execution.

strSQL="sp_20_1_0_10_Get_Entity_All '" & session("entity_ID") & "','" & session("client_id") & "','" & session("role") & "','" & Type_Passed & "','" & filter_id_type & "','" & filter_id_cat & "','" & filter_id_active & "','" & sort_id & "','" & sort_field(0) & "','" & sort_field(1) & "','" & sort_field(2) & "','" & sort_field(3) & "','" & sort_field(4) & "','" & sort_field(5) & "','" & sort_field(6) & "','" & sort_field(7) & "','" & start_page & "','" & display_records & "'"
response.Write strSQL
rsEntity.open strSQL, adoCon
rsEntity2.nextrecordset

Am I missing something in my logic or am I limited due to be host or DB service offering? If so, is there a different way I can skin this?

On the other suggestions, can I use named parameters in ASP VB? If so, it would definitely be more manageable and structured but I cannot find anything through Google that suggests Named Parameters are supported in ASP VB.

I would love to not be required to use dynamic SQL and need to do some reading on the ability of SQL2008 to allow multiple parameters in a WHERE clause.

Thanks again for your assistance.

Regards,
Steve

Kristen
Test

22859 Posts

Posted - 2010-03-22 : 11:26:36

rsEntity2.nextrecordset

should be

rsEntity.nextrecordset

but that may just be a Cut & paste error?


rsEntity.open strSQL, adoCon

... process the first resultset in rsEntity ...

rsEntity.nextrecordset

... process the second resultset in rsEntity ...

best you have a read up about NextRecordSet in VBscript - long time since I had to write an VBScript!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources