Question

SELECT COUNT(*) AS Expr1 
FROM Book 
INNER JOIN Temp_Order ON Book.Book_ID = Temp_Order.Book_ID 
WHERE (Temp_Order.User_ID = 25) 
AND (CONVERT (nvarchar, Temp_Order.OrderDate, 111) = CONVERT (nvarchar, GETDATE(), 111))

In here i want to change my User_ID to get from a label.Text

this Sql Statement is in a DataView. so in the Wizard it not accepting a text box values or anything.

can someone please help me to solve this

Answer 1

Never compare date columns by using CONVERT. To compare date only from a date time, use BETWEEN. Better still, use the DATE type for the column instead of DATETIME.

To filter a DataView, filter the query on which is based. Use a parametrized query:

SELECT COUNT(*) AS Expr1 
FROM Book 
INNER JOIN Temp_Order ON Book.Book_ID = Temp_Order.Book_ID 
WHERE (Temp_Order.User_ID = @User_ID) 
AND Temp_Order.OrderDate BETWEEN @fromDate AND @toDate;

Pass the @User_ID as a parameter to the query: command.Parameters.AddWithValue("@User_ID", Convert.ToInt32(label.Text));

You can also obtain similar results using LINQ and converting the result to a DataView, see Filtering with DataView.

In addition to the string-based filtering capabilities DataView also provides the ability to use LINQ expressions for the filtering criteria. LINQ expressions allow for much more complex and powerful filtering operations than the string-based filtering.

Whatever you do, don t use the DataView.RowFilter property.

友情链接