Strange String.Format()-输入字符串的格式不正确

Question

我似乎不明白为什么我遇到了麻烦，因为我已经做了好几次了，但由于这个字符串要格式化为string fsql = string.Format(sql, fa);，我得到了错误：Input string was not in a correct format case。

sql:

"DECLARE @BENTEST varchar(100)rnSET @BENTEST = '{1}'rnrnSELECT a.Period, a.NCR, a.Date_Created, a.Date_Done, a.Item, a.Qty, tc.TargetCost*a.Qty Cost, a.Aging FROMrnt(SELECT rntnt.NCR, rntDATENAME(yy, nt.Date_Done) + '-' + RIGHT('0' + DATENAME(ISOWK, nt.Date_Done), 2) Period,rntnt1.Date_Created,rntnt.Date_Done,rnt(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%"%', a.FirstParseStep)) ParsedItem FROMrntt(SELECT SUBSTRING(nt.ItemData, PATINDEX('%"Item%', nt.ItemData) + LEN('"Item":"'), 60) FirstParseStep) a) Item,rnt(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}%', a.FirstParseStep)) ParsedQuantity FROMrntt(SELECT SUBSTRING(nt.ItemData, PATINDEX('%"ItemQty%', nt.ItemData) + LEN('"ItemQty":'), 60) FirstParseStep) a ) Qty,rntPCF.dbo.GetRealWorkDays(nt1.Date_Created, nt.Date_Done) AgingrntFROM PCF.dbo.NCRTask nt, PCF.dbo.NCRTask nt1rntWHERE nt1.NCR = nt.NCRrntAND (nt.TaskType = 5 OR nt.TaskType = 16)rntAND nt1.TaskType = 25rntUNIONrntSELECT rntnt.NCR, rntDATENAME(yy, @BENTEST) + '-' + RIGHT('0' + DATENAME(ISOWK, @BENTEST), 2) Period,rntnt.Date_Created,rntnull Date_Done,rnt(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%"%', a.FirstParseStep)) ParsedItem FROMrntt(SELECT SUBSTRING(nt.ItemData, PATINDEX('%"Item%', nt.ItemData) + LEN('"Item":"'), 60) FirstParseStep) a) Item,rnt(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}%', a.FirstParseStep)) ParsedQuantity FROMrntt(SELECT SUBSTRING(nt.ItemData, PATINDEX('%"ItemQty%', nt.ItemData) + LEN('"ItemQty":'), 60) FirstParseStep) a ) Qty,rntPCF.dbo.GetRealWorkDays(nt.Date_Created, GETDATE()) AgingrntFROM PCF.dbo.NCRTask ntrntWHERE nt.TaskType = 25rntAND nt.Date_Created < @BENTESTrntAND NOT EXISTS (SELECT 1 FROM PCF.dbo.NCRTask WHERE nt.NCR = NCR AND (TaskType = 5 OR TaskType = 16) AND Date_Done < @BENTEST)) a,rntavvik1.dbo.TargetCost tcrnWHERE a.Period = DATENAME(yy, @BENTEST) + '-' + RIGHT('0' + DATENAME(ISOWK, @BENTEST), 2)rnAND tc.ItemNo = a.Item"

sql，但格式化后可以轻松阅读：

DECLARE @BENTEST varchar(100)
SET @BENTEST = '{1}'
SELECT a.Period, a.NCR, a.Date_Created, a.Date_Done, a.Item, a.Qty, tc.TargetCost*a.Qty Cost, a.Aging FROM
    (SELECT 
    nt.NCR, 
    DATENAME(yy, nt.Date_Done) + '-' + RIGHT('0' + DATENAME(ISOWK, nt.Date_Done), 2) Period,
    nt1.Date_Created,
    nt.Date_Done,
    (SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%"%', a.FirstParseStep)) ParsedItem FROM
        (SELECT SUBSTRING(nt.ItemData, PATINDEX('%"Item%', nt.ItemData) + LEN('"Item":"'), 60) FirstParseStep) a) Item,
    (SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}%', a.FirstParseStep)) ParsedQuantity FROM
        (SELECT SUBSTRING(nt.ItemData, PATINDEX('%"ItemQty%', nt.ItemData) + LEN('"ItemQty":'), 60) FirstParseStep) a ) Qty,
    PCF.dbo.GetRealWorkDays(nt1.Date_Created, nt.Date_Done) Aging
    FROM PCF.dbo.NCRTask nt, PCF.dbo.NCRTask nt1
    WHERE nt1.NCR = nt.NCR
    AND (nt.TaskType = 5 OR nt.TaskType = 16)
    AND nt1.TaskType = 25
    UNION
    SELECT 
    nt.NCR, 
    DATENAME(yy, @BENTEST) + '-' + RIGHT('0' + DATENAME(ISOWK, @BENTEST), 2) Period,
    nt.Date_Created,
    null Date_Done,
    (SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%"%', a.FirstParseStep)) ParsedItem FROM
        (SELECT SUBSTRING(nt.ItemData, PATINDEX('%"Item%', nt.ItemData) + LEN('"Item":"'), 60) FirstParseStep) a) Item,
    (SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}%', a.FirstParseStep)) ParsedQuantity FROM
        (SELECT SUBSTRING(nt.ItemData, PATINDEX('%"ItemQty%', nt.ItemData) + LEN('"ItemQty":'), 60) FirstParseStep) a ) Qty,
    PCF.dbo.GetRealWorkDays(nt.Date_Created, GETDATE()) Aging
    FROM PCF.dbo.NCRTask nt
    WHERE nt.TaskType = 25
    AND nt.Date_Created < @BENTEST
    AND NOT EXISTS (SELECT 1 FROM PCF.dbo.NCRTask WHERE nt.NCR = NCR AND (TaskType = 5 OR TaskType = 16) AND Date_Done < @BENTEST)) a,
    avvik1.dbo.TargetCost tc
WHERE a.Period = DATENAME(yy, @BENTEST) + '-' + RIGHT('0' + DATENAME(ISOWK, @BENTEST), 2)
AND tc.ItemNo = a.Item

fa是一个字符串[]，它包括：

[0] = ""
[1] = date with format "YYYY-MM-DD"

我不明白为什么我会出错。我已经为其他一些sql:s做了这件事，我没有这个问题。有人知道怎么了吗？

Answer 1

您的字符串中有未转义的}：

(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}%', a.FirstParseStep)) ParsedQuantity FROM
                                                  ^

你应该逃离它：

(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}}%', a.FirstParseStep)) ParsedQuantity FROM

Answer 2

根据我所看到的，您只需将2个变量放入字符串中。格式，但您从未设置字符串或告诉它使用变量。也许你想做这样的事？：

string fsql = string.Format("{0} {1}":sql, fa);

因为您必须放弃一个字符串，并且您可以使用{}和字符串后面给定的变量的索引，而不是仅仅声明字符串。有关String.Format的更多信息，请参阅MSDN

Answer 3

(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}}%', a.FirstParseStep)) ParsedQuantity FROM