Convert string value to int when checked with db in c #
I have an itemID string and I want the gridview to use this parameter and pass the rest of the information from the item table. The problem I am thinking is itemIDs are in varchar format and dd itemID is in int format.
because of this, when the gridview page displaying item data is loaded, I get the following error. Well I think this is the reason for the error, correct me if I'm wrong!
Conversion failed when converting the varchar value ' + itemIDs + ' to data type int.
The sql query used:
SELECT ItemID, Name, RelDate, Price, Status FROM item_k WHERE (ItemID IN (' + itemIDs + '))
contains values like "3,16,8"
how can i convert it to 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 an easy way to solve this problem?
a source to share
I think the problem is with the ItemID column and not the itemID string or you have quotes around 3,16,8.
SQL should be
SELECT ItemID, Name, RelDate, Price, Status FROM item_k
WHERE ItemID IN (3,16,8)
which assumes the ItemID is indeed an integer and I am assuming it should be.
If you have the same scenario for not a whole IN clause, it would be
SELECT * FROM table
WHERE columnname IN ('Val1','VaL2')
Complement For zeros:
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)
Addendum to second question: Refer to Append a String Using Delimiters for several solutions. Also, as an aside, I would use List <int> / List (Of Integer) (C #, vb.net respectively) instead of ArrayList when using the .NET 2.0+ Framework.
a source to share
See my answer here . Basically, you are passing an int string to the varchar (8000) function, returning a table variable, and joining that variable with your table. The SQL for the function is included in my answer. This works well with SQL reports and other instances where you will have a different number of int you want to filter with.
a source to share