locked
How to Find Data's from Database.... RRS feed

  • 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-Access

    Wednesday, 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