Home Forums C# Programming sql query problem

Viewing 0 reply threads
  • Author
    Posts
    • #1927
      msaqib
      Participant

      using System;
      using System.Collections;
      using System.ComponentModel;
      using System.Data;
      using System.Data.OleDb;
      using System.Drawing;
      using System.Web;
      using System.Web.SessionState;
      using System.Web.UI;
      using System.Web.UI.WebControls;
      using System.Web.UI.HtmlControls;
      namespace lms
      {
      ///
      /// Summary description for inc_search_books.
      ///

      public class inc_search_books : System.Web.UI.Page
      {
      protected System.Web.UI.WebControls.TextBox Q;
      protected System.Web.UI.WebControls.Button Button1;
      protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;
      protected System.Web.UI.WebControls.Label Label1;
      protected System.Web.UI.WebControls.DropDownList SearchType;

      private void Page_Load(object sender, System.EventArgs e)
      {
      // Put user code to initialize the page here

      if (Session["Student"]!="Yes")
      {
      Response.Redirect("Login1.aspx");
      }

      }
      #region Web Form Designer generated code
      override protected void OnInit(EventArgs e)
      {
      //
      // CODEGEN: This call is required by the ASP.NET Web Form Designer.
      //
      InitializeComponent();
      base.OnInit(e);
      }

      ///
      /// Required method for Designer support - do not modify
      /// the contents of this method with the code editor.
      ///

      private void InitializeComponent()
      {
      this.Button1.Click += new System.EventHandler(this.Button1_Click);
      this.Load += new System.EventHandler(this.Page_Load);
      }
      #endregion
      private void Button1_Click(object sender, System.EventArgs e)
      {
      if (Q.Text!="")
      {
      string source = "";
      switch (SearchType.SelectedIndex)
      {
      case 0:
      source = "select * from book where name like '*" + Q.Text.ToString() + "*' OR description like '*" + Q.Text.ToString() + "*'";
      break;
      case 1:
      source = "select * from book where isbn like '*" + Q.Text.ToString() + "*'";
      break;
      case 2:
      source = "select * from book where publisher like '*" + Q.Text.ToString() + "*'";
      break;
      default:
      source = "select * from book";
      break;
      }
      Response.Write(source);
      OleDbConnection con = new OleDbConnection(System.Configuration.ConfigurationSettings.A ppSettings["MM_CONNECTION_STRING_lms"]);
      con.Open();
      OleDbCommand cmd=new OleDbCommand(source,con);
      OleDbDataReader r = cmd.ExecuteReader();
      string StrResult="";
      StrResult += "";

      while(r.Read()){
      StrResult += "";
      StrResult += "";

      }
      StrResult += "
      " + r["Name"] + " by "+ r["authors"] +"

      "+r["description"] +"
      Price: "+ r["price"] +" | ";
      StrResult += "ISBN: "+ r["isbn"] +" | ";
      StrResult += "Quantity: "+ r["NoOfAvailableBooks"] +" | ";
      StrResult += "Publisher: "+ r["publisher"] +" | ";
      if (r["status"].ToString()=="True")
      {
      StrResult += "Available: Yes";
      }
      else
      {
      StrResult += "Available: No";
      }
      if (r["type"].ToString()=="True")
      {
      StrResult += " | PDF: Yes";
      }
      else
      {
      StrResult += " | PDF: No";
      }
      StrResult += " | Request to Issue this book";
      StrResult +="
      Accession #: " +r["Accession"].ToString()+"
      ";
      r.Close();
      con.Close();
      cmd.Dispose();
      Label1.Text=StrResult;
      }

      }

      }
      }

      This is an asp.net web page source code in C#. on the Button1_Click event it searches the data base and returns the records fetched. We create the query on the basis of drop down list box ‘SearchType’ and the query text from the text box ‘Q’. Now when it creates the query that query doesnt return any record. for example for slected index 1 and Q=”x” it shows no result from the data base which have the following table and records.

      name                    description                        isbn             publisher
      XML XML 123 wrox
      Executive Executive Management 232 wrox
      Marketing Introduction to Marketing 2322 wrox
      Engineering Electrical Engineering 333 wrox

      query it generates is:

      select * from book where name like '*x*' OR description like '*x*';

      but when i runthis query in MS access database it returns the two records. Why is it so, any one have an idea. thanks

Viewing 0 reply threads
  • The forum ‘C# Programming’ is closed to new topics and replies.