Conversion failed when converting the varchar to data type int: SQL

Question

I am creating a web app in asp.net mvc I have a query which looks like below

using (SqlConnection conn = new SqlConnection(_connStr))
{
    conn.Open();

    var p = new DynamicParameters();
    p.Add("@SP_RoleId", "7,8,9", dbType: DbType.String, direction: ParameterDirection.Input);
    p.Add("@SP_UserId", userId, dbType: DbType.Int32, direction: ParameterDirection.Input);

    var obj = conn.Query<PendingKmsRequest>(sql: "SELECT [f].[id] AS [FileId],[fvr].[Id] AS [RequestId], [au].[Name]"
                        + ", [fvr].[RequestByUserId], [fvr].[FromDate], [fvr].[ToDate],[f].[Title], [fvr].[Status], [fvr].[StatusRemarks]"
                        + "FROM [dbo].[File] AS[f]"
                        + "INNER JOIN [dbo].[FileViewRequest] AS [fvr] ON [f].[CurrentFileVersionId] = [fvr].[FileVersionId]"
                        + "INNER JOIN [Access].[User] AS [au] ON [fvr].[RequestByUserId] = [au].[Id]"
                        + "WHERE ([fvr].[Status] = 'P' OR ([fvr].[Status] = 'A' AND [fvr].[StatusByUserId] = @SP_UserId AND GETDATE() BETWEEN [fvr].[FromDate] AND [fvr].[ToDate]))"
                        + "AND (SELECT 1 FROM [Access].[UserRoleMap] WHERE UserId=@SP_UserId AND RoleId IN(@SP_RoleId)) = 1", param: p, commandType: CommandType.Text);

    if (obj != null && obj.Count() > 0)
        return obj.ToList();
    else
        return new List<PendingKmsRequest>();
}

NOTE: Role id is always like (7,8,9) and it is int column in the database.

I get this conversion error on this line of code:

 WHERE UserId = @SP_UserId AND RoleId IN (@SP_RoleId))

This is the error:

Conversion failed when converting the nvarchar value '7,9,10' to data type int.

How can I prevent this error?

the RoleID as a column is an int, however when your condition contains comma ',' then your condition is a string, not an int. — Zeina, Aug 01 '19 at 06:38
Look at the solution [here](http://www.sommarskog.se/arrays-in-sql.html) — Peter Smith, Aug 01 '19 at 06:48

score 1 · Accepted Answer · answered Aug 01 '19 at 07:08

The following line in your question code:

p.Add("@SP_RoleId", "7,8,9", dbType: DbType.String, direction: ParameterDirection.Input);

The value "7,8,9" is string and parameter type DbType.String is string as well.

But, you said this is int in your database. This is mismatch.

Further, your query:

WHERE UserId = @SP_UserId AND RoleId IN (@SP_RoleId))

The query is using IN clause.

Dapper can convert your value for IN clause if pass in an IEnumerable.

Change the line of code as below:

p.Add("@SP_RoleId", new[] {7,8,9}, dbType: DbType.Int32, direction: ParameterDirection.Input);

Hardik Leuwa · Answer 2 · 2019-08-02T10:35:08.397

No need to use convert string in array or any string split() function

If you have comma saperated string then you can check it like below steps,

If you have @SP_RoleId = "7, 8, 9"
You can convert this string as below
@SP_RoleId = ",7,8,9," ( ',' + ltrim(rtrim( @SP_RoleId )) + ',' )
Now use Like to check ,UserId,

Updated code as below,

using (SqlConnection conn = new SqlConnection(_connStr))
{
    conn.Open();

    var p = new DynamicParameters();
    p.Add("@SP_RoleId", "7,8,9", dbType: DbType.String, direction: ParameterDirection.Input);
    p.Add("@SP_UserId", userId, dbType: DbType.Int32, direction: ParameterDirection.Input);

    var obj = conn.Query<PendingKmsRequest>(sql: "SELECT [f].[id] AS [FileId],[fvr].[Id] AS [RequestId], [au].[Name]"
                        + ", [fvr].[RequestByUserId], [fvr].[FromDate], [fvr].[ToDate],[f].[Title], [fvr].[Status], [fvr].[StatusRemarks]"
                        + "FROM [dbo].[File] AS[f]"
                        + "INNER JOIN [dbo].[FileViewRequest] AS [fvr] ON [f].[CurrentFileVersionId] = [fvr].[FileVersionId]"
                        + "INNER JOIN [Access].[User] AS [au] ON [fvr].[RequestByUserId] = [au].[Id]"
                        + "WHERE ([fvr].[Status] = 'P' OR ([fvr].[Status] = 'A' AND [fvr].[StatusByUserId] = @SP_UserId AND GETDATE() BETWEEN [fvr].[FromDate] AND [fvr].[ToDate]))"
                        + "AND (SELECT 1 FROM [Access].[UserRoleMap] WHERE ',' +  lTrim(rTrim(@SP_RoleId)) + ',' like '%,' + lTrim(rTrim(UserId) + ',%' " // Updated line
                        + "AND RoleId IN(@SP_RoleId)) = 1", param: p, commandType: CommandType.Text);

    if (obj != null && obj.Count() > 0)
        return obj.ToList();
    else
        return new List<PendingKmsRequest>();
}

if you have `"7,8,9"` as string in `@SP_RoleId` so no need to convert it in array. You can add commas at start and end of string in query it self and use `Like` to check `,UserID,`. `ltrim()` and `rtrim()` use to remove extra spaces — Hardik Leuwa, Aug 01 '19 at 08:35

Conversion failed when converting the varchar to data type int: SQL

2 Answers2