sqlParameter conversion

I’m trying to call the SQL statement below but get the following error:

System.Data.SqlClient.SqlException: Conversion failed when converting
the varchar value ‘+@buildingIDs+’ to data type int.

  • How to Re Format DateTime in Sql with Ceiling or Flooring method?
  • Passing parameter to a SQL Server stored procedure
  • How to copy values from table for another column
  • Performance issues with transpose and insert large, variable column data files into SQL Server
  • Errors: “INSERT EXEC statement cannot be nested.” and “Cannot use the ROLLBACK statement within an INSERT-EXEC statement.” How to solve this?
  • Return total records from SQL Server when using ROW_NUMBER
  • @"SELECT id, startDateTime, endDateTime 
        FROM tb_bookings 
       WHERE buildingID IN ('+@buildingIDs+') 
         AND startDateTime <= @fromDate";

    buildingID is an int type column in the db. Will I need to pass the IDs as an array of ints?

  • Can you create a table from XML
  • Perform same SQL query on multiple databases
  • SQL split and merge string
  • String sql is not in correct format insertion fails using vb.net?How
  • Sql connection security
  • SQL Server Parallelism - never happens
  • 4 Solutions collect form web for “sqlParameter conversion”

    Bravax’s way is a bit dangerous. I’d go with the following so you don’t get attacked with SQL Injections:

    int[] buildingIDs = new int[] { 1, 2, 3 };
    /***/ @"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN (" +
          string.Join(", ", buildingIDs.Select(id => id.ToString()).ToArray())
          + ") AND startDateTime <= @fromDate"; 

    Note that LINQ can do this via Contains (which maps to IN). With regular TSQL, another option is to pass down the list as a CSV (etc) varchar, and use a table-valued UDF to split the varchar into pieces. This allows you to use a single TSQL query (doing an INNER JOIN to the UDF result).

    I would rather suggest to go for a stored procedure, if possilble, and pass the lists of IDs as xml. You can get more details on this approach from here.

    It’s trying to compare an int with the string value ‘+@buildingsIDs+’
    So it tries to convert the string to convert it to an int and fails.

    So do the following:

    buildingsIDs = "1, 5, 6";
    @"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN (" + buildingIDs + ") AND startDateTime <= @fromDate";

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.