Strange String.Format() - Input string was not in a correct format case

Question

I can't seem to understand why I'm running into trouble as I've done this several times but with this string to be formatted string fsql = string.Format(sql, fa); I get the error: Input string was not in a correct format case.

sql:

"DECLARE @BENTEST varchar(100)\r\nSET @BENTEST = '{1}'\r\n\r\nSELECT a.Period, a.NCR, a.Date_Created, a.Date_Done, a.Item, a.Qty, tc.TargetCost*a.Qty Cost, a.Aging FROM\r\n\t(SELECT \r\n\tnt.NCR, \r\n\tDATENAME(yy, nt.Date_Done) + '-' + RIGHT('0' + DATENAME(ISOWK, nt.Date_Done), 2) Period,\r\n\tnt1.Date_Created,\r\n\tnt.Date_Done,\r\n\t(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%\"%', a.FirstParseStep)) ParsedItem FROM\r\n\t\t(SELECT SUBSTRING(nt.ItemData, PATINDEX('%\"Item%', nt.ItemData) + LEN('\"Item\":\"'), 60) FirstParseStep) a) Item,\r\n\t(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}%', a.FirstParseStep)) ParsedQuantity FROM\r\n\t\t(SELECT SUBSTRING(nt.ItemData, PATINDEX('%\"ItemQty%', nt.ItemData) + LEN('\"ItemQty\":'), 60) FirstParseStep) a ) Qty,\r\n\tPCF.dbo.GetRealWorkDays(nt1.Date_Created, nt.Date_Done) Aging\r\n\tFROM PCF.dbo.NCRTask nt, PCF.dbo.NCRTask nt1\r\n\tWHERE nt1.NCR = nt.NCR\r\n\tAND (nt.TaskType = 5 OR nt.TaskType = 16)\r\n\tAND nt1.TaskType = 25\r\n\tUNION\r\n\tSELECT \r\n\tnt.NCR, \r\n\tDATENAME(yy, @BENTEST) + '-' + RIGHT('0' + DATENAME(ISOWK, @BENTEST), 2) Period,\r\n\tnt.Date_Created,\r\n\tnull Date_Done,\r\n\t(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%\"%', a.FirstParseStep)) ParsedItem FROM\r\n\t\t(SELECT SUBSTRING(nt.ItemData, PATINDEX('%\"Item%', nt.ItemData) + LEN('\"Item\":\"'), 60) FirstParseStep) a) Item,\r\n\t(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}%', a.FirstParseStep)) ParsedQuantity FROM\r\n\t\t(SELECT SUBSTRING(nt.ItemData, PATINDEX('%\"ItemQty%', nt.ItemData) + LEN('\"ItemQty\":'), 60) FirstParseStep) a ) Qty,\r\n\tPCF.dbo.GetRealWorkDays(nt.Date_Created, GETDATE()) Aging\r\n\tFROM PCF.dbo.NCRTask nt\r\n\tWHERE nt.TaskType = 25\r\n\tAND nt.Date_Created < @BENTEST\r\n\tAND NOT EXISTS (SELECT 1 FROM PCF.dbo.NCRTask WHERE nt.NCR = NCR AND (TaskType = 5 OR TaskType = 16) AND Date_Done < @BENTEST)) a,\r\n\tavvik1.dbo.TargetCost tc\r\nWHERE a.Period = DATENAME(yy, @BENTEST) + '-' + RIGHT('0' + DATENAME(ISOWK, @BENTEST), 2)\r\nAND tc.ItemNo = a.Item"

sql but formatted so you can easily read it:

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 is a string[] and it consist of:

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

I can't figure out why I'm getting the error. I've done it for some other sql:s and I'm not having this issue. Anyone know what's wrong?

Why are you doing this? Don't use string.Format() to put parameter values into your SQL. — JLRishe
– JLRishe, Commented Mar 19, 2015 at 8:22
@JLRishe why not? Sql differs each time I can't concat string when the sql is set dynamically. — President Camacho
– President Camacho, Commented Mar 19, 2015 at 8:23
Instead DECLARE @BENTEST varchar(100)\r\nSET @BENTEST = '{1} ... start with {0} like in ` DECLARE @BENTEST varchar(100)\r\nSET @BENTEST = '{0} .... However, you also have to fix that lonely } in the middle of the sql string-. I would use a stored-procedure instead. Then you can pass arguments via sql-parameter. — Tim Schmelter
– Tim Schmelter, Commented Mar 19, 2015 at 8:24
@PresidentCamacho No, from what I can see, the SQL is the same each time. It's just that one value that's different. So use parameterized SQL for goodness' sake. — JLRishe
– JLRishe, Commented Mar 19, 2015 at 8:25

user4003407 · Accepted Answer · 2015-03-19 08:35:59Z

3

You have non escaped } in your string:

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

You should escape it:

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

answered Mar 19, 2015 at 8:35

user4003407

22.3k4 gold badges54 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

maam27 · Accepted Answer · 2015-03-19 08:23:38Z

1

From what I can see you just put the 2 variables in the string.Format, however you never set a string or told it to use the variables. perhaps you meant to do something like this? :

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

since you have to give up a string and you can use the {} with the index of the variable given behind the string instead of just declaring the strings. for more information of String.Format please refer to MSDN

answered Mar 19, 2015 at 8:23

maam27

4344 silver badges22 bronze badges

1 Comment

President Camacho Over a year ago

I can't use that. The string to be formatted could be infinite variables to be replace. Then fa should also be infinite long.

chris · Accepted Answer · 2015-03-19 08:51:40Z

1

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

answered Mar 19, 2015 at 8:51

chris

112 bronze badges

Collectives™ on Stack Overflow

Strange String.Format() - Input string was not in a correct format case

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related