convert a string value to int when checking with the db in c#

Question

I have a string of itemIDs, and I want the gridview to use this parameter and get me the rest of the details from the item table. The problem i think is itemIDs is in varchar format and the db itemID is in int format.

because of this when the gridview page that display the item details loads i get the following error. Well i think this is the cause for the error, correct me if im wrong!

Conversion failed when converting the varchar value ' + itemIDs + ' to data type int.

the sql query used is:

SELECT ItemID, Name, RelDate, Price, Status FROM item_k WHERE (ItemID IN (' + itemIDs + '))

itemIDs string contains values like "3,16,8"

how can I convert it to the int format? and where to place the conversion? in the sql statement?

thanks

//edit 2

ArrayList tmpArrayList = new ArrayList();

    tmpArrayList = (ArrayList)Session["array"];

    string itemIDs = string.Empty;

    foreach (object itemID in tmpArrayList)
    {
        itemIDs += itemID.ToString() + ',';
    }

   itemIDs = itemIDs.Substring(0, itemIDs.Length - 1);

Is there any simple way to solve this problem?

score 2 · Answer 1 · edited May 23 '17 at 10:27

I think the problem is with the ItemID column, not the itemIDs string or you have quotes around 3,16,8.

The SQL should be

SELECT ItemID, Name, RelDate, Price, Status FROM item_k  
WHERE ItemID IN (3,16,8)

which is assuming ItemID is indeed an integer, and I'm guessing that it should be.

If you have the same scenario for non-integer IN clause, it would be

SELECT * FROM table  
WHERE columnname IN ('Val1','VaL2')

Addition For nulls:

SELECT * FROM table 
WHERE (columnname IN ('Val1','VaL2') OR columnname IS NULL)

or

SELECT * FROM table 
WHERE (columnname IN ('Val1','VaL2') AND columnname IS NOT NULL)

Addition for second question: Refer to article "Join a String Using Delimiters" for multiple solutions. Also, as an aside, I would use a List<int> / List(Of Integer) (C#, vb.net respectively) instead of ArrayList if using .NET 2.0+ Framework.

yea that'll work fine. No quotes around 3,16,8. BUT the itemIDs string can have more than three specific values and sometimes null. In that case how must it be done? thanks — pier, May 17 '09 at 16:33

score 1 · Answer 2 · edited May 23 '17 at 12:18

1

See my answer from here. Essentially you pass the string of int's to a function as varchar(8000), return a table variable and join to that variable with your table. The SQL for the function is included in my answer. This works nicely with SQL Reports and with other instances where you will have a varying number of int's you wish to filter with.

edited May 23 '17 at 12:18

Community

1
1

answered May 17 '09 at 16:46

David Robbins

9,996
7
51
82

score 0 · Answer 3 · answered May 17 '09 at 16:25

0

I think that it is putting the literal string ' + itemIDs + ' into the query instead of appending the value of the itemIDs string to the query. It would be helpful to actually see the code where you construct the query.

answered May 17 '09 at 16:25

tvanfosson

524,688
99
697
795

This is how i get the itemIDs string, Refer //edit 1 in question.\ I used the wizard in gridview to write the query. thanks – pier May 17 '09 at 16:38

convert a string value to int when checking with the db in c#

3 Answers3