Click here to Skip to main content
15,909,518 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
I have 2 temp table and i want to get there data and view it in browser but i always get an error of index out of range was caught.... how can I get out of that problem..
Posted
Comments
Sanchayeeta 17-Oct-14 2:26am    
Can you please show your code.
Pravuprasad 17-Oct-14 2:27am    
please share what you have done and where you got stocked? By this we can't see the actual problem.
Mathew Soji 17-Oct-14 2:27am    
Hi ,

With the information provided its difficult to suggest something or debug .
Appreciate if you could post code and mentioned exactly where you get the error.

Thanks
Rajesh waran 17-Oct-14 2:32am    
check ur query and table,that no of columns in both query and table are matching not? sometimes ur temp table have 4 column,but u are calling 5th column from ur table.just debug and check it.
Henley A Dispo 17-Oct-14 2:36am    
im stocked up in temp table ... bdw first time using temp table.. i can fix this prob on my own if not in temp table but now... i cant...

1 solution

below is my data access codes...


C#
public List<CategoriesRollup> CategBranch()
   {
       var categBranch = new List<CategoriesRollup>();
       const string spName = "categories_rollup_newbranchlist"; //"categories_rollup_branchlist";
       using (var con = _connectionString.GetConnection())
       using (var cmd = new SqlCommand(spName, con))
       {
           try
           {
               cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = UsersSession.Users.UserName;
               cmd.Parameters.Add("@terminal", SqlDbType.VarChar).Value = IpAddress.GetMyIpAddress();
               cmd.Parameters.Add("@type", SqlDbType.TinyInt).Value = 1;
               cmd.CommandType = CommandType.StoredProcedure;
               var rdr = cmd.ExecuteReader();

               if (rdr.HasRows)
                   while (rdr.Read())
                categBranch.Add(new CategoriesRollup
                                      {
                                      // Result = Convert.ToInt32(rdr["result"]),
                                       BranchLookupKey = Convert.ToInt32(rdr["BranchLookupKey"]),
                                       Branch = rdr["Branch"].ToString(),
                                       ComboPromoKey = Convert.ToInt32(rdr["ComboPromoKey"]),
                                       Combo = rdr["Combo"].ToString(),
                                       CategoryKey = Convert.ToInt32(rdr["CategoryKey"]),
                                       Category = rdr["Category"].ToString(),
                                       ItemCategoryKey = Convert.ToInt32(rdr["ItemCategoryKey"]),
                                       Item = rdr["Item"].ToString()
                                      });



then my sp in ssms

ALTER PROCEDURE [dbo].[categories_rollup_branchlist]

@range INT = 0,
@username VARCHAR(20), 
@terminal VARCHAR(60),
@type TINYINT


AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE
@Error INT,
@Rowcount INT,
--****** SYSTEM LOGS
@action VARCHAR(20),
@log_details VARCHAR(8000),
@record_key INT,


---temporary table
--@key INT,
--@result INT,
@categoryKeys VARCHAR(max),
@startPromos INT,
@countPromos INT,
@promoKey INT,
@parse VARCHAR(max),
@final VARCHAR(max),
@branch_code VARCHAR(max)
SET @Error = 0

SET @action = 'CATEGORIES ROLLUP LIST'
SET @record_key = 0

BEGIN TRANSACTION begin_transactionA
SAVE TRANSACTION save_transactionB

	SET ROWCOUNT @range
	
	-----
CREATE TABLE #promosPerBranch	
(   -- id INT ,
	id INT IDENTITY (1,1),
	PromoKey int
)
CREATE TABLE #wholebranch (
		--Id INT IDENTITY (1,1),
		--Id INT,
		--   result INT,
		BranchLookupKey INT,
		Branch VARCHAR(50),
		ComboPromoKey INT,
		Combo VARCHAR(60),
		CategoryKey INT,
		Category VARCHAR(50),
		ItemCategoryKey INT,
		Item VARCHAR(60)	
)
    --SET IDENTITY_INSERT #promosPerBranch ON
		INSERT INTO #promosPerBranch
		SELECT promo_key AS 'PromoKey' 
		FROM dbo.branch_promos 
		WHERE branch_lookup_key = branch_lookup_key
		SELECT @countPromos = COUNT(*) from #promosPerBranch
		SET @startPromos = 1
		WHILE @startPromos <= @countPromos
BEGIN
		SELECT @branch_code = branch_code FROM dbo.branches WHERE lookup_key = @promoKey
		SELECT @promoKey = promoKey FROM #promosPerBranch WHERE id = @startPromos
		SELECT @categoryKeys = p.category_keys FROM dbo.promos p WHERE [key] = @promoKey

	--SET IDENTITY_INSERT #wholebranch ON
		INSERT INTO #wholebranch
		SELECT  
			--   0 AS 'result',
			   b.lookup_key AS 'BranchLookupKey',
			   b.branch_code AS 'Branch',
			   p.[key] AS 'ComboPromoKey',
			   p.name AS 'Combo',
			   c.[key] AS 'CategoryKey',
			   c.name AS 'Category',
			   i.category_key AS 'ItemCategoryKey',
			   i.name AS 'Item'
			 
			   FROM items i 
			   INNER JOIN dbo.branches b ON i.category_key = b.lookup_key
			   INNER JOIN dbo.categories c ON i.category_key = c.[key]  
			   INNER JOIN dbo.promos p ON p.[key] = @promoKey
			   WHERE category_key IN (SELECT [key] FROM dbo.categories
			   WHERE [key] IN (SELECT * FROM dbo.parse_comma_delimited_string(@categoryKeys)))
		SET @startPromos = @startPromos + 1
	--SET IDENTITY_INSERT #wholebranch OFF
	--SET IDENTITY_INSERT #promosPerBranch OFF
END	
SELECT * FROM #promosPerBranch
SELECT * FROM #wholebranch
DROP TABLE #promosPerBranch
DROP TABLE #wholebranch
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900