sqlParameter conversion

Question

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.

@"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?

This is almost exactly the same ? as http://stackoverflow.com/questions/182060/sql-where-in-array-of-ids — Josef, Oct 08 '08 at 20:50

score 2 · Accepted Answer · answered Oct 08 '08 at 11:37

2

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";

answered Oct 08 '08 at 11:37

Omer van Kloeten

11,800
9
42
53

To be honest, I can't see the difference in the code. We're both building a string and executing it. You've just fleshed out how you would construct the buildingIDs string. If SQL Injection is an issue, then use a stored procedure, possibly using Marc Gravell's approach. – Bravax Oct 08 '08 at 11:53
The difference is small but significant - Your code example implied string concatenation using a passed in string(which introduces the possibility of SQL Injection); This example passes in an integer array, essentially forcing clean data to be passed to it. – Chris Shaffer Oct 08 '08 at 12:24

score 1 · Answer 2 · answered Oct 08 '08 at 11:42

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).

score 0 · Answer 3 · answered Oct 08 '08 at 11:41

0

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.

answered Oct 08 '08 at 11:41

Vijesh VP

4,508
6
30
32

score -1 · Answer 4 · answered Oct 08 '08 at 11:20

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";

sqlParameter conversion

4 Answers4

Linked