VB2010 Throws Error : Index Out Of Range.

[nadiam]

Hi.

im trying to get data from table TBL_ORDERITEM

FLD_ORDERITEM_ID | FLD_ORDER_ID | FLD_PRODUCT_ID | FLD_QTY | FLD_SUBTOTAL

grd_productlist.DataSource = ("SELECT FLD_ORDER_ID,FLD_PRODUCT_ID,FLD_QTY,FLD_SUBTOTAL FROM TBL_ORDERITEM_A15428 WHERE FLD_ORDER_ID = '" & id & "' IN (SELECT '" & id & "' FROM TBL_ORDERITEM_A15428 GROUP BY '" & id & "' HAVING COUNT(*) > 1 )")

grd_productlist.Columns(2).HeaderText = "Product ID"
grd_productlist.Columns(3).HeaderText = "Quantity"
grd_productlist.Columns(4).HeaderText = "Subtotal"

but I get Index out of range error poiting at -> grd_productlist.Columns(2).HeaderText = "Product ID"

why?

[Santanu.Das]

WHERE FLD_ORDER_ID = '" & id & "' IN (SELECT '" & id & "' FROM TBL_ORDERITEM_A15428 GROUP BY '" & id & "' HAVING COUNT(*) > 1 )")

From my opinion in sql statement has problem.
You have used IN predicate. The arithmatic operator equal to i.e. = compares a single value to another single value. In case a value needs to compared to a list of values then the IN predicate is used. The IN predicate helps reduce the need to use multiple OR condition.

Your statement should be

"SELECT FLD_ORDER_ID,FLD_PRODUCT_ID,FLD_QTY,FLD_SUBTOTAL FROM TBL_ORDERITEM_A15428 WHERE FLD_ORDER_ID IN (SELECT FLD_ORDER_ID FROM TBL_ORDERITEM_A15428 GROUP BY FLD_ORDER_ID HAVING COUNT(*) > 1 )"

[nadiam]

Shark_1, im trying to get all rows but with a specific value in a column. like for example:

ID | FLD_ORDER_ID | FLD_PRODUCT_ID | FLD_QTY | FLD_SUBTOTAL
1 | A123
2 | A548
3 | A123
4 | A487
5 | A548
for example i want to get the rows with fld_order_id AS A123 and display those rows only. both queries dont get what i want.

and i fixed the original problem by selecting all fields. and i forgot to add my 'run_sql_query' before brackets"select...."

[nadiam]

NVM