Answered by:
How to Find Data's from Database....

Question
-
User-720755217 posted
I am using ASP.NET Search Form... Like
Order No.
Lorry No
Invoice No
From
To
When I am enter the Value in the above text fileds... Query will be performed search operation and it will show Serach Result Matched Data's..
I am usingfollwing query
SELECT OrderID, OrderDate, TMNumber, Lorryno, Freight, OrderTotal, Advance, OrderStatus
FROM Orders
WHERE (OrderID = 1) AND (TMNumber = '2543') AND (Lorryno = '2545') AND (Status <> 'D') AND (CreatedBy = 2)
It is working only for should fill all the fields.. but i want User can Fill either All filed or Anyone..
But Query Search corressponding Field...
what is Query I am use???
Wednesday, October 20, 2010 1:39 AM
Answers
-
User-1199946673 posted
I said:
"When using parameterized queries you don't have to worry about that. "
But you don't seem to read what I say and do things your own (wrong) way so this will be my last post in this thread!. For the last time:
When using parameters (The best way)
OrderDate BETWEEN @From AND @To
When you create the commandtext:
OrderDate BETWEEN #2010/10/1# AND #2010/10/2#
But when From and/or To are optional criteria, you cannot use the BETWEEN operator. Read my other posts and Mike's article on Optional Search Criteria how to solve that
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Saturday, October 23, 2010 6:01 AM
All replies
-
User1992938117 posted
For that use OR instead of AND in where clause.
or you need to use if else condition using all combination and create query like that.
if(Order No!="" && Lorry No!="" && Invoice No!=="" && From!="" To!="")
{
}
Wednesday, October 20, 2010 2:03 AM -
User-1199946673 posted
WHERE (OrderID = 1 OR True) AND (TMNumber = '2543' OR True) AND (Lorryno = '2545' OR True) AND (Status <> 'D' OR True) AND (CreatedBy = 2 OR True)
http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria
http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-AccessWednesday, October 20, 2010 2:54 AM -
User-720755217 posted
Thanks....
In My Query...
SELECT OrderID,OrderDate,TMNumber,Lorryno,Freight,OrderTotal,Advance,OrderStatus FROM Orders WHERE (OrderID=1 OR TRUE) AND (TMNumber="" OR TRUE) AND( Lorryno ="356" OR TRUE) AND [OrderDate] BETWEEN [#10/19/2010#] AND [#/10/20/2010#] AND Status <>'D' AND CreatedBY =2
Error...
Error in WHERE clause near 'TRUE'.
Error in WHERE clause near '2'.
Unable to parse query text....
Wednesday, October 20, 2010 8:30 AM -
User-548979073 posted
Try the following
DECLARE @OrderID INT DECLARE @TMNumber VARCHAR(10) DECLARE @Lorryno VARCHAR(10) DECLARE @Status VARCHAR(1) DECLARE @CreatedBy INT SET @OrderID = 1 SET @TMNumber = NULL SET @Lorryno = NULL SET @Status = NULL SET @CreatedBy = NULL SELECT OrderID, OrderDate, TMNumber, Lorryno, Freight, OrderTotal, Advance, OrderStatus FROM Orders WHERE (@OrderID IS NULL OR OrderID = @OrderID) AND (@TMNumber IS NULL OR TMNumber = @TMNumber) AND (@Lorryno IS NULL OR Lorryno = @Lorryno) AND (@Status IS NULL OR Status <> @Status) AND (@CreatedBy IS NULL OR CreatedBy = @CreatedBy)
For the items for which nothing is entered just pass null and you will get the result for rest of the criteria.In the above sql the record should retuen the results where orderid = 1.
Wednesday, October 20, 2010 8:43 AM -
User-1199946673 posted
Did you read the article about Optional Search Criteria? Because the values (OrderID =1, TMNumber="", Lorryno="356" etc....) must come form somewhere? Use parameters, and then it will look something like:
SELECT OrderID,OrderDate,TMNumber,Lorryno,Freight,OrderTotal,Advance,OrderStatus FROM Orders WHERE (OrderID = @OrderID OR @OrderID IS NULL) AND (TMNumber = @TMNumber OR @TMNumber IS NULL) AND (Lorryno = @Lorryno OR @Lorryno IS NULL)....
Wednesday, October 20, 2010 8:43 AM -
User-720755217 posted
I am using Ms Access...So when i implement above query i got a bug like "parsing Error"
SO i want Ms access query....
Wednesday, October 20, 2010 9:26 AM -
User-1199946673 posted
The article is about Access and it works. Show the relevant code?
Wednesday, October 20, 2010 9:34 AM -
User-720755217 posted
#region Search Operation
protected void lbtnSearch_Click(object sender, EventArgs e)
{
divMsg.Visible = true;
OrderList oOrderList = new OrderList();
oOrderList.CreatedBY = Convert.ToInt32(Session["UserID"]);
if (txtOrderno.Text == "")
{
txtOrderno.Text = "0";
}
oOrderList.OrderID=Convert.ToInt32(txtOrderno.Text.Trim());
oOrderList.TmNo=txtTmno.Text.Trim();
oOrderList.LorryNumber=txtLorryno.Text.Trim();
if(txtFrom.Text=="")
{
txtFrom.Text = "01/01/2000";
}
oOrderList.From=Convert.ToDateTime(txtFrom.Text.Trim());
if(txtTo.Text=="")
{
txtTo.Text="01/01/2000";
}
oOrderList.To=Convert.ToDateTime(txtTo.Text.Trim());
if (this.IsValidateForms())
{
DataTable dtValue = oOrderList.SelectOrderList();
if (dtValue.Rows.Count > 0)
{
int rw = 0;
rw = Convert.ToInt32(dtValue.Rows.Count);
for (int i = 0; i < rw; i++)
{
gvOrderList.DataSource = dtValue;
gvOrderList.DataBind();
}
}
else
{
gvOrderList.EmptyDataText = "No Records are Found!";
gvOrderList.DataBind();
}
}
}
#endregion
------------
#region Search OrderList
public DataTable SelectOrderList()
{
OLEDBDAL oDAL = new OLEDBDAL();
oDAL.AddParamToOLEDBCmd(oDAL.OleDbCommand, "@OrderID", OleDbType.Integer, 0, ParameterDirection.Input,iOrderID);
oDAL.AddParamToOLEDBCmd(oDAL.OleDbCommand, "@TMNo", OleDbType.WChar, 20, ParameterDirection.Input,sTmNo);
oDAL.AddParamToOLEDBCmd(oDAL.OleDbCommand, "@Lorryno", OleDbType.WChar, 15, ParameterDirection.Input,sLorryNumber);
oDAL.AddParamToOLEDBCmd(oDAL.OleDbCommand, "@From", OleDbType.Date,0, ParameterDirection.Input,dtFrom);
oDAL.AddParamToOLEDBCmd(oDAL.OleDbCommand, "@To", OleDbType.Date,0, ParameterDirection.Input,dtTo);
oDAL.AddParamToOLEDBCmd(oDAL.OleDbCommand, "@CreatedBY", OleDbType.Integer,0, ParameterDirection.Input,iCreatedBY);
sQuery = "SELECT OrderID,OrderDate,TMNumber,Lorryno,Freight,OrderTotal,Advance,OrderStatus FROM Orders WHERE OrderID=@OrderID AND TMNumber=@TMNo AND Lorryno = @Lorryno AND [OrderDate] BETWEEN [#@From#] AND [#@To#] AND Status <>'D' AND CreatedBY =" + iCreatedBY;
sQuery += " ORDER BY OrderDate Asc";
return oDAL.ExecuteDatasetCmd(oDAL.OleDbCommand, CommandType.Text, sQuery).Tables[0];
}
#endregion
Thursday, October 21, 2010 12:43 AM -
User-1199946673 posted
sQuery = "SELECT OrderID,OrderDate,TMNumber,Lorryno,Freight,OrderTotal,Advance,OrderStatus FROM Orders WHERE (OrderID= @OrderID OR @OrderID IS NULL) AND (TMNumber= @TMNo OR @TMNo IS NULL) AND (Lorryno = @Lorryno OR @Lorryno IS NULL) AND ([OrderDate] >= @From OR @From IS NULL) AND ([OrderDate] <= @To OR @To IS NULL) AND Status <>'D' AND (CreatedBY = @CreatedBY OR @CreatedBY IS NULL);
Thursday, October 21, 2010 3:33 AM -
User-720755217 posted
Nw small change in my Coding...
OLEDBDAL oDAL = new OLEDBDAL();
oDAL.AddParamToOLEDBCmd(oDAL.OleDbCommand, "@OrderID", OleDbType.Integer, 0, ParameterDirection.Input, iOrderID);
oDAL.AddParamToOLEDBCmd(oDAL.OleDbCommand, "@TMNo", OleDbType.WChar, 20, ParameterDirection.Input, sTmNo);
oDAL.AddParamToOLEDBCmd(oDAL.OleDbCommand, "@Lorryno", OleDbType.WChar, 15, ParameterDirection.Input, sLorryNumber);
oDAL.AddParamToOLEDBCmd(oDAL.OleDbCommand, "@From", OleDbType.Date, 0, ParameterDirection.Input, dtFrom);
oDAL.AddParamToOLEDBCmd(oDAL.OleDbCommand, "@To", OleDbType.Date, 0, ParameterDirection.Input, dtTo);
oDAL.AddParamToOLEDBCmd(oDAL.OleDbCommand, "@CreatedBY", OleDbType.Integer, 0, ParameterDirection.Input, iCreatedBY);
sQuery = "SELECT OrderID,OrderDate,TMNumber,Lorryno,Freight,OrderTotal,Advance,OrderStatus FROM Orders WHERE Status <>'D'";
if (iOrderID > 0)
sQuery += " AND OrderID=@OrderID";
if (sTmNo != "")
sQuery += " AND TMNumber = @TMNo";
if (sLorryNumber != "")
sQuery += " AND Lorryno = @Lorryno";
if (dtFrom.Date > DateTime.MinValue || dtTo.Date > DateTime.MinValue)
sQuery += " AND OrderDate BETWEEN [#@From#] AND [#@To#]";
sQuery += " AND CreatedBY =" + iCreatedBY + " ORDER BY OrderDate Asc";
return oDAL.ExecuteDatasetCmd(oDAL.OleDbCommand, CommandType.Text, sQuery).Tables[0];
--------------------
When I am enter OrderID.... Is working Correctly....Either I type Any other Colum..got error msg No Records Are found!....
1.Cant Get database data's
Reason is ( example : @TMNumber=2 is String Format...so during Check value is not wroking)
2nd Error.. When I am enter dates.. Display follow Error....
'Data type mismatch in criteria expression.'
how to recover the error...
Thursday, October 21, 2010 5:45 AM -
User-1199946673 posted
If you want to do it your way, please do.
Thursday, October 21, 2010 7:13 AM -
User-720755217 posted
What is Date Format in ms access Query???
Friday, October 22, 2010 6:19 AM -
User-1199946673 posted
What is Date Format in ms access Query???
When using parameterized queries you don't have to worry about that. If you want to use datestrings anyway, use
#yyyy/mm/dd#
Friday, October 22, 2010 7:17 AM -
User-720755217 posted
same thing i applied in my query...
But i Got Error like....
Data type mismatch in criteria expression.
Friday, October 22, 2010 9:16 AM -
User-1199946673 posted
Remove all parameters, add them one by one to find out which parameter is causing the error
Friday, October 22, 2010 9:27 AM -
User-720755217 posted
error part....
"SELECT OrderID,OrderDate,TMNumber,Lorryno,Freight,OrderTotal,Advance,OrderStatus FROM Orders WHERE Status <>'D' AND OrderDate BETWEEN [#@From#] AND [#@To#]";
Saturday, October 23, 2010 1:11 AM -
User-1199946673 posted
I said:
"When using parameterized queries you don't have to worry about that. "
But you don't seem to read what I say and do things your own (wrong) way so this will be my last post in this thread!. For the last time:
When using parameters (The best way)
OrderDate BETWEEN @From AND @To
When you create the commandtext:
OrderDate BETWEEN #2010/10/1# AND #2010/10/2#
But when From and/or To are optional criteria, you cannot use the BETWEEN operator. Read my other posts and Mike's article on Optional Search Criteria how to solve that
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Saturday, October 23, 2010 6:01 AM