SQL query using string.format

Question

i am developing a web application in c#,i want to write sql query by using string.format function as follows:

string sSql = string.Format("Select * From {0}", DbReference.TABLE_NAME_SEC_ROLES);
                if (roleCriteria._roleName != null && roleCriteria._isEnabled == true)
                    sSql += string.Format(" where {0}={1} and {2}={3} " + DbReference.ROLE_NAME_COL, roleCriteria._roleName, DbReference.IS_ENABLED_COL, roleCriteria._isEnabled);
                if (roleCriteria._roleName != null)
                    sSql += string.Format(" where {1} = {2} " + DbReference.ROLE_NAME_COL, roleCriteria._roleName);
                if (roleCriteria._isEnabled == true)
                    sSql += string.Format("where {0}" + DbReference.IS_ENABLED_COL + "'false'");

and it gives me exception as follows:

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

so, Please give me solution on this exception.

"i must use string.format" - either you don't understand what's being suggested and you do not need to use string.format, or this is homework and your teacher is a fool. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 12, 2012 at 10:17

Tim Schmelter · Accepted Answer · 2012-10-12 10:19:04Z

2

This does not work and raises a FormatException:

string.Format(" where {1} = {2} " + DbReference.ROLE_NAME_COL, roleCriteria._roleName);

Instead you need to start with zero since {2} is equal to the length of the args array what is not allowed:

string.Format(" where {0} = {1} " + DbReference.ROLE_NAME_COL, roleCriteria._roleName);

String.Format Method (String, Object[])

Edit: Another bug found:

replace

string.Format("where {0}" + DbReference.IS_ENABLED_COL + "'false'")

with

string.Format("where {0}", DbReference.IS_ENABLED_COL + "'false'")

Here you have specified a format item but not added the argument.

The number indicating an argument to format is less than zero, or greater than or equal to the length of the args array.

>>> But i would recommend to use Parameters instead.

edited Oct 12, 2012 at 10:19

answered Oct 12, 2012 at 10:13

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

1 Comment

MonaliJ Over a year ago

yes i already gone through this string.Format(" where {0} = {1} " + DbReference.ROLE_NAME_COL, roleCriteria._roleName); but not working still there is same exception

Anil Soman · Accepted Answer · 2012-10-12 10:18:48Z

0

This is the line giving error

if (roleCriteria._roleName != null)                     
sSql += string.Format(" where {1} = {2} " + DbReference.ROLE_NAME_COL, roleCriteria._roleName);

Here you have used index 2 which does not exist. You should use 0 and 1.

answered Oct 12, 2012 at 10:18

Anil Soman

2,4677 gold badges41 silver badges68 bronze badges

1 Comment

Damien_The_Unbeliever Over a year ago

There were at least 3 lines with errors which blow up string.Format, plus another few logical errors and a string concatenation error.

Damien_The_Unbeliever · Accepted Answer · 2012-10-12 10:27:48Z

There is so much wrong in such a short stretch of code. I think what you want is below, but I would strongly recommend that you switch to using parameters. Treating everything as strings is an invitation to trouble:

string sSql = string.Format("Select * From {0}", DbReference.TABLE_NAME_SEC_ROLES);
if (roleCriteria._roleName != null && roleCriteria._isEnabled == true)
   sSql += string.Format(" where {0}={1} and {2}={3} " ,/* , not + */ DbReference.ROLE_NAME_COL, roleCriteria._roleName, DbReference.IS_ENABLED_COL, roleCriteria._isEnabled); 
else if (roleCriteria._roleName != null) /* else added, otherwise this will fire if the above if did, and add a second WHERE clause */
   sSql += string.Format(" where {0} = {1} " ,/* , not + */ DbReference.ROLE_NAME_COL, roleCriteria._roleName); 
else if (roleCriteria._isEnabled == true) /* else added, otherwise this will fire if the first if did, and add another WHERE clause */
   sSql += string.Format(" where {0} = 'false'" , DbReference.IS_ENABLED_COL); /* , not +, and moved 'false' */
   /* Also, indented the `where`, since if only this if is true, it would mash the `where` onto the table name */

And we probably still need to insert some (') quote characters, in places, since I'm guessing that some of these formatted in values will be strings. And then we have to deal with escaping quotes.

Collectives™ on Stack Overflow

SQL query using string.format

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related