--- Code ในปุ่ม บันทึก -----
try
if Adddata then
response.write("บันทึกข้อมูลแล้ว !")
else
response.write("เกิดข้อผิดพลาด ไม่สามารถบันทึกข้อมูลได้ !")
end if
catch ex
end try
---- code Function ---
Function Adddata() as boolean
dim Sqlconn as SqlConnection= new SqlConnection(strConnString)
dim TranS as sqlTransection
try
Sqlconn.open
Trans = Sqlconn.BeginTransection
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
" VALUES (@CustomerID,@Name,@Email,@CountryCode,@Budget,@Used)"
dim Fm(5) as sqlclient.sqlparameter
Fm(0) = new sqlclient.sqlparameter("@CustomerID",'C005')
Fm(1) = new sqlclient.sqlparameter("@Name",'Weerachai Nukitram')
Fm(2) = new sqlclient.sqlparameter("@Email",'[email protected]')
Fm(3) = new sqlclient.sqlparameter("@CountryCode",'TH')
Fm(4) = new sqlclient.sqlparameter("@Budget",'2000000')
Fm(5) = new sqlclient.sqlparameter("@Used",'1000000')
sqlhelper.ExecuteNonQuery(TranS,Commantype.Text,strSQL ,fm )
'---------- เห็นข้อมูลที่คุณ บันทึก ข้อมูลเดียปวกัน ผมเลยใช้ query ตัวเดียวนะ
sqlhelper.ExecuteNonQuery(TranS,Commantype.Text,strSQL ,fm )
Trans.Commit()
Sqlconn.close
return true
catch ex
Trans.Rollback()
Sqlconn.close
return false
end try
end Function
WITH cte
AS (
SELECT *
,ROW_NUMBER() OVER (
ORDER BY InputType DESC
,StringNum ASC
) AS rn
,(
(
(
(
ROW_NUMBER() OVER (
PARTITION BY InputType ORDER BY InputType DESC
,StringNum ASC
) + 4
) / 5
) - 1
) % 5
) + 1 AS rnGroup
FROM (
SELECT InputType
,StringNum
,LimitPrice1
,LimitPrice2
,Y.Price1
,Y.Price2
,ISNULL(LimitPrice1, 0) + ISNULL(Y.Price1, 0) AS OverP1
,ISNULL(LimitPrice2, 0) + ISNULL(Y.Price2, 0) AS OverP2
FROM GoodLuckConfigSpread AS Z
CROSS APPLY (
SELECT SUM(Price1) AS Price1
,SUM(Price2) AS Price2
FROM GoodLuck a
WHERE a.InputType = Z.InputType
AND a.StringNum = Z.StringNum
) Y
) X
WHERE X.OverP1 > 0
OR X.OverP2 > 0
)
,cte0
AS (
SELECT InputType
,MIN(rn) AS rn
FROM cte
GROUP BY InputType
)
,cte1
AS (
SELECT cte.InputType, StringNum, OverP1, OverP2, cte.rn
FROM cte
INNER JOIN cte0 ON cte.InputType = cte0.InputType
AND (cte.rn - cte0.rn) % 5 = 0
)
SELECT cte1.InputType
,cte1.StringNum AS StringNum_1
,cte1.OverP1 AS OverP1_1
,cte1.OverP2 AS OverP2_1
,cte2.StringNum AS StringNum_2
,cte2.OverP1 AS OverP1_2
,cte2.OverP2 AS OverP2_2
,cte3.StringNum AS StringNum_3
,cte3.OverP1 AS OverP1_3
,cte3.OverP2 AS OverP2_3
,cte4.StringNum AS StringNum_4
,cte4.OverP1 AS OverP1_4
,cte4.OverP2 AS OverP2_4
,cte5.StringNum AS StringNum_5
,cte5.OverP1 AS OverP1_5
,cte5.OverP2 AS OverP2_5
FROM cte1
LEFT JOIN cte AS cte2 ON cte1.InputType = cte2.InputType
AND cte2.rn - cte1.rn = 1
LEFT JOIN cte AS cte3 ON cte1.InputType = cte3.InputType
AND cte3.rn - cte1.rn = 2
LEFT JOIN cte AS cte4 ON cte1.InputType = cte4.InputType
AND cte4.rn - cte1.rn = 3
LEFT JOIN cte AS cte5 ON cte1.InputType = cte5.InputType
AND cte5.rn - cte1.rn = 4
ORDER BY InputType DESC
Load balance : Server 02
