AndyLin程式生活

[ASP.NET 啟動 javascript 偵錯]2019

[SQL]無法為連結伺服器的 OLE DB 提供者 "SQLNCLI11" 啟動巢狀交易。因為 XACT_ABORT 選項已設定為 OFF，所以必須要有巢狀交易。

LINK SERVER 開啟分散式交易

SET XACT_ABORT ON
DELETE FROM DKF.IF.dbo.IFCOM_INVNO
WHERE INVNO = @I_CHR_INVOID
SET XACT_ABORT OFF

[C# .Net]控制項擁有的 SelectedValue 無效，因為它不在項目清單中

控制項擁有的 SelectedValue 無效，因為它不在項目清單中

1. 當來源為DB datasource為SQL產出

請注意，"ABC" 不等於 "ABC " => 因後方空白不被承認

檢查SQL語法是否有做處理

2. 來源為定值或輸入，使用DEbug mode 檢查items中是否沒有SelectedValue

範例加入預設項目的寫法

從DB抓來的資料後，直接加入data row 再bind

DataTable dtOftenACC = db.GetOftenACC(txtIFFIA20F040.Text, ddlIFFIA20FD1015.SelectedValue, txtIFFIA20FD1003.Text, string.Empty, string.Empty);
if (dtOftenACC.Rows.Count > 0)
{
string tmpOftenACC = "";
tmpOftenACC = fddlOftenACC.SelectedValue.ToString();

fddlOftenACC.DataValueField = "SEG";
fddlOftenACC.DataTextField = "SEGNM";

DataRow dr = dtOftenACC.NewRow();
dr["SEGNM"] = "已自行挑選科目";
dr["SEG"] = "ZZ";

DataRow dr2 = dtOftenACC.NewRow();
dr2["SEGNM"] = "請選擇";
dr2["SEG"] = "1";

dtOftenACC.Rows.InsertAt(dr, 0);
dtOftenACC.Rows.InsertAt(dr2, 0);
fddlOftenACC.DataSource = dtOftenACC;
fddlOftenACC.DataBind();
if (tmpOftenACC == "")
{
fddlOftenACC.SelectedValue = "1";
}
else
{
fddlOftenACC.SelectedValue = tmpOftenACC;
}

}

[SQL]執行查詢字串連接串參數寫法參考

SET @TMP_CHR_SIGGRPSEQ = 1 --20180825 --(SELECT CONVERT(int, @TMP_DEPTLEVEL))

SELECT @sql_INSERT_INTO_SIGMGRDF = 'INSERT INTO '+ @CHR_TABLENM_SIGMGRDF + ' ' --20180825
+'(COMPID, APDEPTID, SIGGRP, SIGGRPSEQ, SIGDEPTID, SIGDEPTLEVEL, SIGDEPTMGRUID) VALUES (''' --20180825
+@TMP_COMPID+''','+''''+@TMP_APDEPTID+''','+''''+@TMP_CHR_SIGGRP+''','+''''+CONVERT(VARCHAR(2), CONVERT(INT,@TMP_CHR_SIGGRPSEQ)*5)+''','+''''+@TMP_BASEDEPTID+''','+''''+@TMP_DEPTLEVEL+''','+''''+@TMP_SIGDEPTMGRUID+''')' --20190513

EXEC (@sql_INSERT_INTO_SIGMGRDF)

SELECT @sql_INSERT_INTO_WHERE_SIGDEPTLVL = 'INSERT INTO ' + @CHR_TABLENM_SIGDEPTLVL + ' VALUES ('+@TMP_DEPTLEVEL+')'
print '[dbo].[HD_USP_INSERT_APDEPT5T_W_I01].88.sql_INSERT_INTO_WHERE_SIGDEPTLVL='+@sql_INSERT_INTO_WHERE_SIGDEPTLVL
print '[dbo].[HD_USP_INSERT_APDEPT5T_W_I01].TMP_DEPTLEVEL='+@TMP_DEPTLEVEL
-- EXEC (@sql_SELECT_SIGDEPTLVL) --20180911
SELECT @sql_SELECT_WHERE_SIGDEPTLVL = 'SELECT * FROM ' + @CHR_TABLENM_SIGDEPTLVL + ' WHERE [DEPELVLID]=' +@TMP_DEPTLEVEL --20180911
print '[dbo].[HD_USP_INSERT_APDEPT5T_W_I01].sql_SELECT_WHERE_SIGDEPTLVL'+@sql_SELECT_WHERE_SIGDEPTLVL

[JavaScript]將日期格式化傳給 .NET 控制項和時間的加減

//取得現在時間
var dt = new Date();

//日期格式化傳給textbox

//此字串可直接傳給SQL存檔為datetime                
document.getElementById("ctl00_ContentPlaceHolder1_ftbCRTDT_D3").value = dt.format("yyyy-MM-dd HH:mm:ss").toString();

                var dt = new Date();
                year = dt.getFullYear();
                month = dt.getMonth();
                day = dt.getDate();
                if(fgvD3.rows[i2].cells[3].children[0].value == '教育訓練') {
                    fgvD3.rows[i2].cells[6].getElementsByTagName("input")[0].value = new Date(this.year + 2, this.month, this.day).format("yyyyMMdd").toString();
                }
                if (fgvD3.rows[i2].cells[3].children[0].value == 'show room展示') {
                    fgvD3.rows[i2].cells[6].getElementsByTagName("input")[0].value = new Date(this.year + 2, this.month, this.day).format("yyyyMMdd").toString();
                }
                if (fgvD3.rows[i2].cells[3].children[0].value == '測試用') {
                    fgvD3.rows[i2].cells[6].getElementsByTagName("input")[0].value = new Date(this.year + 1, this.month, this.day).format("yyyyMMdd").toString();
                }
                if (fgvD3.rows[i2].cells[3].children[0].value == '其他') {
                    fgvD3.rows[i2].cells[6].getElementsByTagName("input")[0].value = new Date(this.year , this.month+3, this.day).format("yyyyMMdd").toString();
                }

[JavaScript]字串轉換而成的整數

parseInt()可以傳回由字串轉換而成的整數

[SQL]PIVOT使用，將公司階層轉向給 report 頁尾使用+UNPIVOT

select 'SIGSEQ',[050],[100],[110],[120],[130],[140],[150],[160],[170],[180],[190],[200] from
(
SELECT
A.FLOWDPTY003
--,A.FLOWDPTY007
--,B.JOBTITLEID
,C.JOBTITLENM
FROM [dbo].[FLOWDPTY] AS A
left join [IFLOW2].[dbo].[VW_HPMUSER] AS B ON A.FLOWDPTY007 = B.USERID AND A.FLOWDPTY008 = B.COMPID
left join [IFLOW2].[dbo].[VW_HPMJOBTITLE] AS C ON B.JOBTITLEID = C.JOBTITLEID AND B.COMPID =C.COMPID
where A.FLOWDPTY001='IFFIB05F'
AND A.FLOWDPTY002 = @I_CHR_SHEETNO
AND A.FLOWDPTY003 BETWEEN '050' AND '200'
) as t
PIVOT(
Max(JOBTITLENM)
for [FLOWDPTY003] in ([050],[100],[110],[120],[130],[140],[150],[160],[170],[180],[190],[200])
) as p;

轉向後

原始

UNPIVOT

SELECT * FROM (

SELECT

A.CCID

,'工作檔' AS VISION

,@SQL_COMPID AS COMPID

,A.DATAYEAR

,A.SEG1

,A.SEG2

,C.SEG2NM

,A.SEG3

,C.SEG3NM

,A.SEG4

,C.SEG4NM AS SEG4NM

,A.SEG5

,C.SEG5NM AS SEG5NM

,A.SEG6

,C.SEG6NM AS SEG6NM

, A.AMT_M1

, A.AMT_M2

, A.AMT_M3

, A.AMT_M4

, A.AMT_M5

, A.AMT_M6

, A.AMT_M7

, A.AMT_M8

, A.AMT_M9

, A.AMT_M10

, A.AMT_M11

, A.AMT_M12

FROM dbo.BJMF AS A

LEFT JOIN dbo.V_ALLACCID_FROM_GL AS C ON A.CCID = C.CCID

WHERE

A.DATAYEAR = @I_CHR_DATAYEAR

AND A.YEARTYPE = @I_CHR_YEARTYPE

and A.SEG1 = @SQL_COMPID

and A.SEG2 = CASE WHEN @I_CHR_SEG2 <> '' THEN @I_CHR_SEG2 ELSE A.SEG2 END

and A.SEG3 = CASE WHEN @I_CHR_SEG3 <> '' THEN @I_CHR_SEG3 ELSE A.SEG3 END

and A.SEG4 = CASE WHEN @I_CHR_SEG4 <> '' THEN @I_CHR_SEG4 ELSE A.SEG4 END

and A.SEG5 = CASE WHEN @I_CHR_SEG5 <> '' THEN @I_CHR_SEG5 ELSE A.SEG5 END

and A.SEG6 = CASE WHEN @I_CHR_SEG6 <> '' THEN @I_CHR_SEG6 ELSE A.SEG6 END

) AS P