CODEWITHSUNDEEP
c#
pier
pier

Reputation: 503

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

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?

Upvotes: 0

Views: 421

Answers (3)

oglester
oglester

Reputation: 6655

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.

Upvotes: 2

David Robbins
David Robbins

Reputation: 10046

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.

Upvotes: 1

tvanfosson
tvanfosson

Reputation: 532435

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.

Upvotes: 0

Related Questions