Unable to create dynamic sql query in asp.net C#

Question

I am keep getting

Conversion failed when converting the varchar value '46434,15864' to data type int.

I have this texbox which accepts numeric and commas. I need to create query with emp_num in (46434,15864) like syntax.

The query generated from codebehind is this, which runs fine manually in sql server:

SELECT   *  -- column names
FROM [DBO].[tablename] LPR 
WHERE LPR.[EMPLOYEE_NUMBER] in (46434,15864)

code:

  if (txtEmpNum.Text.Trim() != "")
        {
            ////sb.Append("  and LPR.[EMPLOYEE_NUMBER] like '%'+ @empnumber + '%' ");  
            sb.Append("  and LPR.[EMPLOYEE_NUMBER] in (@empnumber) ");  
            cmd.Parameters.Add("@empnumber", SqlDbType.VarChar).Value = txtEmpNum.Text.Trim(); //.Replace("," ,  "','");
        }

        cmd.CommandText = sb.ToString();

        DataTable dt = GetData(cmd);
        gvdetails.DataSource = dt;
        gvdetails.DataBind();

Table:

you have to remove ','

Sandip - Frontend Developer
– Sandip - Frontend Developer

2017-04-18 08:31:15 +00:00
Commented Apr 18, 2017 at 8:31 — Sandip - Frontend Developer
– Sandip - Frontend Developer, Commented Apr 18, 2017 at 8:31
but from where, the input field ?

Akshay
– Akshay

2017-04-18 08:32:50 +00:00
Commented Apr 18, 2017 at 8:32 — Akshay
– Akshay, Commented Apr 18, 2017 at 8:32
Your in statement is wrong with dynamic

Sandip - Frontend Developer
– Sandip - Frontend Developer

2017-04-18 08:35:53 +00:00
Commented Apr 18, 2017 at 8:35 — Sandip - Frontend Developer
– Sandip - Frontend Developer, Commented Apr 18, 2017 at 8:35

Nino · Accepted Answer · 2017-04-18 08:59:35Z

2

You will have to parametrize every value. This way you'll have dynamically created query, but NOT prone to SQL Inject Here's code:

//where IN part of your query
string inClause = "and LPR.[EMPLOYEE_NUMBER] in ({0})";
// string arrays for values and variables of your query
string[] paramValues = txtEmpNum.Text.Trim().Split(',');
string[] paramVars = paramValues.Select((s, i) => "@empNo" + i.ToString()).ToArray();

//create query, ie. and LPR.[EMPLOYEE_NUMBER] in (@empNo0, @empNo1...)
inClause = string.Format(inClause, string.Join(", ", paramVars));

//add vars and values to command
for (int i = 0; i < paramVars.Length; i++)
{
    cmd.Parameters.Add(paramVars[i], SqlDbType.Int).Value = paramValues[i];
}

answered Apr 18, 2017 at 8:59

Nino

7,1152 gold badges29 silver badges46 bronze badges

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

3 Comments

Akshay Over a year ago

Perfect solution! Thanks a lot Nino.

Akshay Over a year ago

Just curious why it was not working. Reason I am asking because the query generated in stringbuilder runs fine on sql server editor window but not here ?

Nino Over a year ago

by adding parameter with Parameters.Add, because of it's type (VarChar), your parameter gets enclosed with single qoutes and it becomes LPR.[EMPLOYEE_NUMBER] in ('46434,15864')

Marc Guillot · Accepted Answer · 2017-04-18 09:13:34Z

2

You need a split function to create a list from an string. You can create that function running this script once :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    set @delimiter = coalesce(@delimiter, dbo.cSeparador());

    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Now your query will be :

SELECT *
FROM [DBO].[tablename] LPR 
WHERE LPR.[EMPLOYEE_NUMBER] in (select * from fnSplitString(@empnumber, ','))

You can call it from C# exactly the same way you called your original code.

edited Apr 18, 2017 at 9:13

answered Apr 18, 2017 at 8:40

Marc Guillot

6,5431 gold badge23 silver badges54 bronze badges

4 Comments

Tamás Szabó Over a year ago

Wouldn't it be possible to split the input of the textbox and then add it as two separate int parameters? That way it is not vulnerable to SQL Injection.

Marc Guillot Over a year ago

Yes, but would two parameters be enough ?. Aren't there cases when the users will try to check three or four codes ?.

Tamás Szabó Over a year ago

Well then split it, then iterate through all the parts. A bit more complicated but at least it leads to safer code.

Marc Guillot Over a year ago

Yes, you can use a split function, and use it on your select. I will edit my answer to show it.

Hina Khuman · Accepted Answer · 2017-04-18 10:08:54Z

0

Basically the error is saying that your variable @empnumber is varchar and LPR.[EMPLOYEE_NUMBER] is integer

You can just append the value of your textbox to your query.

Edit: As others have suggested, this would be prone to SQL Injection. Marc Guillot and Nino solutions are better.

edited Apr 18, 2017 at 10:08

Hina Khuman

7673 gold badges15 silver badges41 bronze badges

answered Apr 18, 2017 at 8:40

m.liboon

244 bronze badges

Collectives™ on Stack Overflow

Unable to create dynamic sql query in asp.net C#

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related