Forgot Password ??

PHP , ASP , ASP.NET, VB.NET, C#, Java , jQuery , Android , iOS , Windows Phone

	Registered : 109,037

HOME > .NET Framework > Forum > Pivot table ใช้กับ store procedure พอดีทำสร้าง store procedure มาตัวนึงสำหรับออก pivot table ตอนรันและ debug

Pivot table ใช้กับ store procedure พอดีทำสร้าง store procedure มาตัวนึงสำหรับออก pivot table ตอนรันและ debug

เริ่มหัวข้อใหม่

Guest

พอดีทำสร้าง store procedure มาตัวนึงสำหรับออก pivot table ตอนรันและ debug โปรแกรมดู Flow การทำงานมันเกิด Error

pivot

Code (VB.NET)

With pvCache
     .Connection = "OLEDB;Provider=SQLOLEDB;Server=(local);Trusted_connection=yes;Initial Catalog=db_iconsme;"
      ตรงนี้ผมไม่ทราบว่าใช้ถูกหรือเปล่าหรือว่าสำหรับ store procedure ใช้ commandtype แบบอื่นหรือปล่าวครับ
         .CommandType = XlCmdType.xlCmdSql    
     .CommandText = "exec POS_Pivot_Sale"
 End With

     pvTable = pvCache.CreatePivotTable(xlApp.ActiveCell) ตรงนี้ Error
     pvCache.MaintainConnection = False

Code

Tag : - - - -

Date : 2010-07-07 16:21:51

By : yo

View : 1558

Reply : 4

No. 1

Guest

ขอดู store ที่เขียนหน่อยสิ

Date : 2010-07-07 16:44:10

By : Programmer ด้อยปัญญา

No. 2

Qoo

โพสกระทู้ ( 13 )
บทความ ( 0 )

Code

USE [db_iconsme]
GO
/****** Object: StoredProcedure [dbo].[POS_Pivot_Sale] Script Date: 07/07/2010 17:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--ข้อมูลการขายสินค้าสำหรับออก pivot table
--exec POS_Pivot_Sale
ALTER PROCEDURE [dbo].[POS_Pivot_Sale]
--@qureryStr ntext,
@ss nvarchar(20)='ProductName'
AS
BEGIN
-- ตัวแปลสำหรับเก็บค่า product แต่ละแถว
declare @ItemID nvarchar(15);
declare @ProductNo nvarchar(15);
declare @ProductName nvarchar(255);
declare @GoldPoints int;
declare @PlatinumPoints int;
declare @Cost money;
declare @Price money;
declare @ProductStyle nvarchar(50);
declare @Balance int;
declare @Unit nvarchar(50);
declare @SalePrice money;
declare @SaleTotalPrice money;
declare @SalePoint int;
declare @FullName nvarchar(500);
declare @MemberType nvarchar(50);
declare @SalePercenDiscount float;
declare @SaleSpecialDiscount float;
declare @SaleTypeSpecialDiscount nvarchar(50);
declare @SaleTotalDiscount float;
declare @SaleDiscount money;
declare @SaleDay int;
declare @SaleMonth int;
declare @SaleYear int ;
declare @BillID int;
declare @SaleID int;
declare @Numb int;
declare @SaleLotNo int;
-- ตัวแปลสำหรับเก็บค่า productvolume แต่ละแถว
declare @VolumeID int;
declare @VolumeProduct_ItemID nvarchar(50);
declare @VolumeLotNo nvarchar(50);
declare @VolumeAmount int;
declare @VolumeBalance int;
declare @VolumeCost money;
-- ตัวแปลสำหรับเก็บค่าจากตาราง Adjustment
declare @AdjustmentID int;
declare @AdNumb int;
declare @AdjustType nvarchar(50);
declare @AdProduct_ItemID nvarchar(50);
declare @AdLotNo int;
declare @AdProductVoumeID int;
--End Product Field
declare @TempTable table (
ItemID nvarchar(15),
ProductVolumeID nvarchar(15),
ProductNo nvarchar(15),
ProductName nvarchar(255),
GoldPoints int,
PlatinumPoints int,
Price money,
Cost money,
ProductStyle nvarchar(50),
Balance int,
Unit nvarchar(50),
SalePrice money,
SaleTotalPrice money,
SalePoint int,
FullName nvarchar(500),
MemberType nvarchar(50),
SalePercenDiscount float,
SaleSpecialDiscount float,
SaleTypeSpecialDiscount nvarchar(50),
SaleTotalDiscount float,
SaleDiscount money,
SaleDay int,
SaleMonth int,
SaleYear int,
Status nvarchar(50),
SaleProductVolumeID int,
SaleBillID int,
LotNo int
)
declare @TempProduct table (
ItemID nvarchar(15),
ProductNo nvarchar(15),
ProductName nvarchar(255),
GoldPoints int,
PlatinumPoints int,
Cost money,
Price money,
ProductStyle nvarchar(50),
Balance int,
Unit nvarchar(50)
)
declare @TempProductVolume table (
ID int,
Product_ItemID nvarchar(50),
LotNo nvarchar(50),
Amount int,
Balance int,
Cost money
)
declare @TempProductAdjust table (
AdjustmentID int,
Numb int,
AdjustType nvarchar(50),
Product_ItemID nvarchar(50),
LotNo int,
ProductVoumeID int
)
--Sale Product
declare @SaleTable table (
ItemID nvarchar(15),
ProductVolumeID nvarchar(15),
ProductNo nvarchar(15),
ProductName nvarchar(255),
ProductStyle nvarchar(50),
ServiceType nvarchar(50),
SaleID int,
Numb int,
Unit nvarchar(50),
Price money,
TotalPrice money,
Points int,
Cost money,
BillID int,
PercenDiscount float,
SpecialDiscount float,
TypeSpecialDiscount nvarchar(50),
TotalDiscount float,
Discount money,
FullName nvarchar(500),
MemberType nvarchar(50),
Day int,
Month int,
Year int,
LotNo int
)
insert into @TempProduct(ItemID, ProductNo, ProductName, GoldPoints, PlatinumPoints, Cost, Price, ProductStyle, Balance, Unit)
SELECT ItemID, ProductNo, ProductName, GoldPoints, PlatinumPoints, Cost, Price, ProductStyle, Balance, Unit
FROM ICON_EntForms_Products
WHERE(ServiceType = 'Sale')

declare @Pcount int-- เก็บจำนวน record สินค้าว่ามีกี่รายการ
declare @I int set @I=0 ;-- running number

declare @VolumeCount int -- เก็บจำนวน record ของ productvolume

select @Pcount=COUNT(ItemID) from ICON_EntForms_Products where ServiceType='Sale'
if(@Pcount>0)
begin
while (@I<@Pcount)
begin
set @I=@I+1;
select top 1 @ItemID=ItemID,@ProductNo=ProductNo,@ProductName=ProductName,@GoldPoints=GoldPoints,@PlatinumPoints=PlatinumPoints,@Cost=Cost,@Price=Price,@ProductStyle=ProductStyle,@Balance=Balance,@Unit=Unit from @TempProduct
if (@ProductStyle='Volume')
begin
-- count จำนวน record ของ product volume
select @VolumeCount=COUNT(ID) from ICON_EntForms_Products_Volume where Product_ItemID=@ItemID
-- save record product volume to @TempProductVolume
insert into @TempProductVolume(ID,Product_ItemID,LotNo,Amount,Balance,Cost)
SELECT ID, Product_ItemID, LotNo, Amount, Balance, Cost FROM ICON_EntForms_Products_Volume WHERE (Product_ItemID = @ItemID)
if (@VolumeCount>0)
begin
--จำนวน record ของ product volume
declare @J int set @J=0 ;
while (@J<@VolumeCount)
begin
set @J=@J+1;
-- ข้อมูลสินค้าแต่ละแถวของ product volume
SELECT top 1 @VolumeID= ID,@VolumeProduct_ItemID= Product_ItemID,@VolumeLotNo= LotNo,@VolumeAmount= Amount,@VolumeBalance= Balance,@VolumeCost= Cost FROM @TempProductVolume
-- insert ข้อมูลสินค้าเป็นไอเทมตามจำนวน balance
declare @Temp int set @Temp=0 ;
while (@Temp<@VolumeBalance)
begin
insert into @TempTable(ItemID,ProductVolumeID,ProductNo,ProductName,GoldPoints,PlatinumPoints,Price,Cost,ProductStyle,Balance,Unit,Status,LotNo)
values(@ItemID,@VolumeID,@ProductNo,@ProductName,@GoldPoints,@PlatinumPoints,@Price,@Cost,@ProductStyle,@Balance,@Unit,'Ready',@VolumeLotNo)
delete @TempProductVolume where ID =@VolumeID;
set @Temp=@Temp+1;
end
-- สินค้าที่ถูก adjust
declare @ADCount int set @ADCount=0;
SELECT @ADCount=COUNT( AdjustmentID) FROM POS_AdjustMent WHERE (ProductVoumeID = @VolumeID) AND (IsLock = 1)
insert into @TempProductAdjust(AdjustmentID ,Numb ,AdjustType ,Product_ItemID , LotNo ,ProductVoumeID)
SELECT AdjustmentID, Numb, AdjustType, Product_ItemID, LotNo, ProductVoumeID FROM POS_AdjustMent WHERE (ProductVoumeID = @VolumeID) AND (IsLock = 1)
declare @ADindex int set @ADindex=0;
while (@ADindex<@ADCount)
begin
set @ADindex=@ADindex+1;
select top 1 @AdjustmentID=AdjustmentID,@AdNumb=Numb,@AdjustType=AdjustType,@AdLotNo=LotNo,@AdProduct_ItemID=Product_ItemID,@AdProductVoumeID=ProductVoumeID from @TempProductAdjust
declare @AdTemp int set @AdTemp=0;
while (@AdTemp<@AdNumb)
begin
set @AdTemp=@AdTemp+1;
insert into @TempTable(ItemID,ProductVolumeID,ProductNo,ProductName,GoldPoints,PlatinumPoints,Price,Cost,ProductStyle,Balance,Unit,SalePrice,SaleTotalPrice,SalePoint,FullName,MemberType,SalePercenDiscount,SaleSpecialDiscount,SaleTypeSpecialDiscount,SaleTotalDiscount,SaleDiscount,SaleDay,SaleMonth,SaleYear,Status,LotNo)
values(@ItemID,@VolumeID,@ProductNo,@ProductName,@GoldPoints,@PlatinumPoints,@Price,@Cost,@ProductStyle,@Balance,@Unit,@SalePrice,@SaleTotalPrice,@SalePoint,@FullName,@MemberType,@SalePercenDiscount,@SaleSpecialDiscount,@SaleTypeSpecialDiscount,@SaleTotalDiscount,@SaleDiscount,@SaleDay,@SaleMonth,@SaleYear,@AdjustType,@AdLotNo)
delete @TempProductAdjust where AdjustmentID=@AdjustmentID;
end
end

--สินค้าที่ขายไปแล้ว
declare @SaleCount int set @SaleCount=0;
select @SaleCount=COUNT(SaleID) from POS_Sale where Product_ItemID=@ItemID AND ProductVolumeID=@VolumeID
insert into @SaleTable (ItemID ,ProductVolumeID ,ProductNo ,ProductName ,ProductStyle ,SaleID ,Numb , Unit ,Price ,TotalPrice ,Points ,Cost , BillID ,PercenDiscount ,SpecialDiscount ,TypeSpecialDiscount ,TotalDiscount ,Discount ,FullName ,MemberType ,Day ,Month ,Year,LotNo)
SELECT s.Product_ItemID, s.ProductVolumeID, p.ProductNo, p.ProductName, s.ProductStyle, s.SaleID, s.Numb, p.Unit, s.Price, s.TotalPrice, s.Point, v.Cost, s.BillID,
s.PercenDiscount, s.SpecialDiscount, s.TypeSpecialDiscount, s.TotalDiscount, s.Discount, c.Title + c.FirstName + ' ' + c.LastName AS FullName,
CASE m.MemberType WHEN 1 THEN 'Gold' WHEN 2 THEN 'Platinum' ELSE 'NULL' END AS MemberType, s.Day, s.Month, s.Year, v.LotNo
FROM ICON_EntForms_Products_Volume AS v RIGHT OUTER JOIN
POS_Sale AS s ON v.ID = s.ProductVolumeID LEFT OUTER JOIN
ICON_EntForms_Contacts AS c RIGHT OUTER JOIN
Member AS m ON c.ItemId = m.ItemId ON s.MemberID = m.MemberID LEFT OUTER JOIN
ICON_EntForms_Products AS p ON s.Product_ItemID = p.ItemID
WHERE (p.ServiceType = N'Sale') AND (s.Product_ItemID = @ItemID) AND (s.ProductVolumeID = @VolumeID)

if (@SaleCount>0)
begin
declare @K int set @K=0;
while (@K<@SaleCount)
begin
set @K=@K+1;
select top 1 @ItemID=ItemID,@VolumeID=ProductVolumeID,@ProductNo=ProductNo,@ProductStyle=ProductStyle,@ProductName=ProductName,@SaleID= SaleID ,@numb=Numb ,@Unit=Unit ,@SalePrice=Price ,@SaleTotalPrice=TotalPrice ,@SalePoint= Points ,@Cost=Cost ,@BillID= BillID ,@SalePercenDiscount= PercenDiscount/Numb ,@SaleSpecialDiscount= SpecialDiscount/Numb ,@SaleTypeSpecialDiscount= TypeSpecialDiscount ,@SaleTotalDiscount= TotalDiscount/Numb ,@SaleDiscount=Discount/Numb ,@FullName=FullName ,@MemberType=MemberType ,@SaleDay=Day ,@SaleMonth=Month ,@SaleYear=Year,@SaleLotNo=LotNo from @SaleTable
declare @L int set @L=0;
while (@L<@Numb)
begin
set @L=@L+1;
insert into @TempTable(ItemID,ProductVolumeID,SaleBillID,ProductNo,ProductName,GoldPoints,PlatinumPoints,Price,Cost,ProductStyle,Balance,Unit,SalePrice,SaleTotalPrice,SalePoint,FullName,MemberType,SalePercenDiscount,SaleSpecialDiscount,SaleTypeSpecialDiscount,SaleTotalDiscount,SaleDiscount,SaleDay,SaleMonth,SaleYear,Status,LotNo)
values(@ItemID,@VolumeID,@BillID,@ProductNo,@ProductName,@GoldPoints,@PlatinumPoints,@Price,@Cost,@ProductStyle,@Balance,@Unit,@SalePrice,@SaleTotalPrice,@SalePoint,@FullName,@MemberType,@SalePercenDiscount,@SaleSpecialDiscount,@SaleTypeSpecialDiscount,@SaleTotalDiscount,@SaleDiscount,@SaleDay,@SaleMonth,@SaleYear,'Soled',@SaleLotNo)
end
delete @SaleTable where SaleID=@SaleID;
end
end
delete @TempProductVolume where ID=@VolumeID
end
end
delete @TempProduct where ItemID=@ItemID;
end
-- กรณีที่ไม่ใช้สินค้าประเภท volume
else
begin
if (@Balance>0)
begin
insert into @TempTable(ItemID,ProductNo,ProductName,GoldPoints,PlatinumPoints,Price,Cost,ProductStyle,Balance,Unit,Status)
--SalePrice,SaleTotalPrice,SalePoint,MemberType,SalePercenDiscount,SaleSpecialDiscount,SaleTypeSpecialDiscount,SaleTotalDiscount,SaleDiscount,SaleDay,SaleMonth,SaleYear,
values(@ItemID,@ProductNo,@ProductName,@GoldPoints,@PlatinumPoints,@Price,@Cost,@ProductStyle,@Balance,@Unit,'Ready')
--@SalePrice,@SaleTotalPrice,@SalePoint,@MemberType,@SalePercenDiscount,@SaleSpecialDiscount,@SaleTypeSpecialDiscount,@SaleTotalDiscount,@SaleDiscount,@SaleDay,@SaleMonth,@SaleYear,
delete @TempProduct where ItemID=@ItemID;
end
else
begin
insert into @TempTable(ItemID,ProductNo,ProductName,GoldPoints,PlatinumPoints,Price,Cost,ProductStyle,Balance,Unit,SalePrice,SaleTotalPrice,SalePoint,FullName,MemberType,SalePercenDiscount,SaleSpecialDiscount,SaleTypeSpecialDiscount,SaleTotalDiscount,SaleDiscount,SaleDay,SaleMonth,SaleYear,Status)
values(@ItemID,@ProductNo,@ProductName,@GoldPoints,@PlatinumPoints,@Price,@Cost,@ProductStyle,@Balance,@Unit,@SalePrice,@SaleTotalPrice,@SalePoint,@FullName,@MemberType,@SalePercenDiscount,@SaleSpecialDiscount,@SaleTypeSpecialDiscount,@SaleTotalDiscount,@SaleDiscount,@SaleDay,@SaleMonth,@SaleYear,'Soled')
delete @TempProduct where ItemID=@ItemID;
end
end
end
end
select ProductNo,ProductName,GoldPoints,PlatinumPoints,Price,Cost,Unit,SalePrice,SaleTotalPrice,SalePoint,FullName,MemberType,SalePercenDiscount,SaleSpecialDiscount,SaleTypeSpecialDiscount,SaleTotalDiscount,SaleDiscount,SaleDay,SaleMonth,SaleYear,Status,SaleBillID,LotNo from @TempTable;
END

storeprocedure ที่เขียนนี้รันได้ไม่มีปัญหาเลยครับ

Date : 2010-07-07 17:13:09

By : kyokyocs(โย)

No. 3

Stupid.gurl.th

โพสกระทู้ ( 1,603 )
บทความ ( 1 )

ถ้ามัน run ได้ผ่านจริงแนะนำว่าไม่ต้อง run ผ่าน store procedure ใน .net framework หรอกค่ะ
Wrap มันด้วย Stringbuilder แล้ว ExecuteQuery เก็บผ่าน datatable แบบ Anonymous Query ก้อได้ค่ะ
แล้ว ถ้าจะออก report ก้อใช้ผ่าน datatable เพราะ .net จะ cache resultset ทั้งหมดเก็บไว้ให้อยู่แล้ว

เขียน QUERY ยาวไปปะคะ แบบไม่เกรงใจคนอ่านเลย
มันจะลดได้ถ้าใช้ view ช่วยแล้วก้อ ใช้ insert from select ... จะลดความยาวได้เยอะค่ะ

Anonymous Query :
Code (VB.NET)

Dim SQLStatementBuilder As StringBuilder = New StringBuilder ()

SQLStatementBuilder.AppendLine ("....")
SQLStatementBuilder.AppendLine ("....")

' เอาจนหมดทุกบรรทัดนั่นแหละค่ะ และสุดท้ายคุณต้องมาจบ ด้วย 
SQLStatementBuilder.AppendLine ("select  * from @TempTable;") ;

แล้วส่ง ไป execute ตามปกติและ get ออกเป็น dataTable ออกมาเลยค่ะ
เขียน QUERY ยาวเกินความจำเป็นนะคะ หลายที่เลย
ยาวจนขี้เกียจดูให้ TAG CODE ก้ใส่ผิดอันอีก ใช้ของ VB หรือ C# ก้อได้
จะได้เห็น INDENTATION ชัดๆค่ะ

อีกอย่าง temp table นี่ performance ของระบบลดฮวบๆ เลย
เลี่ยงได้ก้อเลี่ยงๆมั่งเหอะค่ะ อย่าบอกว่ามีวิธีนี้วิธีเดียวค่ะ
ลองมี user เกิดต้องตาต้องใจอยากทำ report นี้สัก 10 คนพร้อมกัน
สงสาร server ค่ะ

Date : 2010-07-08 03:20:04

By : blurEyes

No. 4

Qoo

โพสกระทู้ ( 13 )
บทความ ( 0 )

ขอบคุณมากครับ

Date : 2010-07-08 10:16:25

By : kyokyocs

ค้นหาข้อมูล

แสดงความคิดเห็น

Re : Pivot table ใช้กับ store procedure พอดีทำสร้าง store procedure มาตัวนึงสำหรับออก pivot table ตอนรันและ debug

รายละเอียด

เพื่อความเรียบร้อยของข้อความ ควรจัดรูปแบบให้พอดีกับขนาดของหน้าจอ เพื่อง่ายต่อการอ่านและสบายตา และตรวจสอบภาษาไทยให้ถูกต้อง

อัพโหลดแทรกรูปภาพ

Notice

เพื่อความปลอดภัยของเว็บบอร์ด ไม่อนุญาติให้แทรก แท็ก [img]....[/img] โดยการอัพโหลดไฟล์รูปจากที่อื่น เช่นเว็บไซต์ ฟรีอัพโหลดต่าง ๆ
อัพโหลดแทรกรูปภาพ ให้ใช้บริการอัพโหลดไฟล์ของไทยครีเอท และตัดรูปภาพให้พอดีกับสกรีน เพื่อความโหลดเร็วและไฟล์ไม่ถูกลบทิ้ง

เพื่อความปลอดภัยและการตรวจสอบ กระทู้ที่แทรกไฟล์อัพโหลดไฟล์จากที่อื่น อาจจะถูกลบทิ้ง

โดย

อีเมล์

บวกค่าให้ถูก