15,911,786 members
Sign in
Sign in
Email
Password
Forgot your password?
Sign in with
home
articles
Browse Topics
>
Latest Articles
Top Articles
Posting/Update Guidelines
Article Help Forum
Submit an article or tip
Import GitHub Project
Import your Blog
quick answers
Q&A
Ask a Question
View Unanswered Questions
View All Questions
View C# questions
View C++ questions
View Javascript questions
View Visual Basic questions
View Python questions
discussions
forums
CodeProject.AI Server
All Message Boards...
Application Lifecycle
>
Running a Business
Sales / Marketing
Collaboration / Beta Testing
Work Issues
Design and Architecture
Artificial Intelligence
ASP.NET
JavaScript
Internet of Things
C / C++ / MFC
>
ATL / WTL / STL
Managed C++/CLI
C#
Free Tools
Objective-C and Swift
Database
Hardware & Devices
>
System Admin
Hosting and Servers
Java
Linux Programming
Python
.NET (Core and Framework)
Android
iOS
Mobile
WPF
Visual Basic
Web Development
Site Bugs / Suggestions
Spam and Abuse Watch
features
features
Competitions
News
The Insider Newsletter
The Daily Build Newsletter
Newsletter archive
Surveys
CodeProject Stuff
community
lounge
Who's Who
Most Valuable Professionals
The Lounge
The CodeProject Blog
Where I Am: Member Photos
The Insider News
The Weird & The Wonderful
help
?
What is 'CodeProject'?
General FAQ
Ask a Question
Bugs and Suggestions
Article Help Forum
About Us
Search within:
Articles
Quick Answers
Messages
Comments by Henley A Dispo (Top 9 by date)
Henley A Dispo
17-Oct-14 3:41am
View
hello?
Henley A Dispo
17-Oct-14 3:17am
View
my sp..
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
Henley A Dispo
17-Oct-14 2:41am
View
my first temp table has 2 columns and my second temp have 8 columns..
im getting the value from second temp
my list in my data access is equal to my second temp table..
show no results.. or index out of range... maybe im passing no value in temp table.. but i already inserted the data in my second table..
Henley A Dispo
17-Oct-14 2:36am
View
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...
Henley A Dispo
17-Oct-14 2:31am
View
Deleted
this is my data access orig codes
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()
});
Henley A Dispo
16-Oct-14 4:48am
View
well sir i did not run to ssms.. i just apply wt u said
Probably, because your stored procedure is not returning a column called "Id".
Check the SP.
Henley A Dispo
16-Oct-14 4:41am
View
i hve new error
An explicit value for the identity column in table '#wholebranch' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Henley A Dispo
16-Oct-14 4:41am
View
Deleted
i hve new error
An explicit value for the identity column in table '#wholebranch' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Henley A Dispo
16-Oct-14 4:16am
View
this is my sp.. maybe i forgot something to write here
CREATE TABLE #promosPerBranch
(
id INT IDENTITY (1,1),
PromoKey int
)
CREATE TABLE #wholebranch (
Id INT IDENTITY (1,1),
BranchLookupKey INT,
Branch VARCHAR(50),
ComboPromoKey INT,
Combo VARCHAR(60),
CategoryKey INT,
Category VARCHAR(50),
ItemCategoryKey INT,
Item VARCHAR(60)
)
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
--SET @id = (SELECT id FROM #wholebranch WHERE Id = (SELECT id FROM #promosPerBranch WHERE id = id)
--SELECT @id = id FROM #wholebranch WHERE id = Id
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
INSERT INTO #wholebranch
SELECT
--b.[key] AS 'BranchKey',
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
END
Show More