sql query problem

[msaqib]

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> /// Summary description for inc_search_books. /// </summary> 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); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> 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="<table width=100% style="border:1px solid #ffffff;">"; StrResult += "<tr><td align=center height=35 valign=middle style="border:1px solid #ffffff;background-color:#fed500;color:#000000;"><b&g t;Search Results</b></td></tr>"; while(r.Read()){ StrResult += "<tr><td><div class=header><b>" + r["Name"] + "</b> by "+ r["authors"] +"</div><BR>"+r["description"] +"</td></tr>"; StrResult += "<tr><td><b>Price: </b>"+ r["price"] +" | "; StrResult += "<b>ISBN: </b>"+ r["isbn"] +" | "; StrResult += "<b>Quantity: </b>"+ r["NoOfAvailableBooks"] +" | "; StrResult += "<b>Publisher: </b>"+ r["publisher"] +" | "; if (r["status"].ToString()=="True") { StrResult += "<b>Available: </b><font color=00ff00>Yes</font>"; } else { StrResult += "<b>Available: </b><font color=fed500>No</font>"; } if (r["type"].ToString()=="True") { StrResult += " | <b>PDF: </b><font color=00ff00>Yes</font>"; } else { StrResult += " | <b>PDF: </b><font color=fed500>No</font>"; } StrResult += " | <a href="inc_request_book.aspx?BookID="+r ["Id"] +"">Request to Issue this book</a>"; StrResult +="<BR><strong>Accession #: </strong>" +r["Accession"].ToString()+"</td></tr>"; } StrResult += "</table>"; r.Close(); con.Close(); cmd.Dispose(); Label1.Text=StrResult; } } } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121

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> /// Summary description for inc_search_books. /// </summary> 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); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> 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="<table width=100% style="border:1px solid #ffffff;">"; StrResult += "<tr><td align=center height=35 valign=middle style="border:1px solid  #ffffff;background-color:#fed500;color:#000000;"><b&g t;Search Results</b></td></tr>"; while(r.Read()){ StrResult += "<tr><td><div class=header><b>" + r["Name"] + "</b> by "+ r["authors"] +"</div><BR>"+r["description"] +"</td></tr>"; StrResult += "<tr><td><b>Price: </b>"+ r["price"] +" | "; StrResult += "<b>ISBN: </b>"+ r["isbn"] +" | "; StrResult += "<b>Quantity: </b>"+ r["NoOfAvailableBooks"] +" | "; StrResult += "<b>Publisher: </b>"+ r["publisher"] +" | "; if (r["status"].ToString()=="True") { StrResult += "<b>Available: </b><font color=00ff00>Yes</font>"; } else { StrResult += "<b>Available: </b><font color=fed500>No</font>"; } if (r["type"].ToString()=="True") { StrResult += " | <b>PDF: </b><font color=00ff00>Yes</font>"; } else { StrResult += " | <b>PDF: </b><font color=fed500>No</font>"; } StrResult += " | <a href="inc_request_book.aspx?BookID="+r ["Id"] +"">Request to Issue this book</a>"; StrResult +="<BR><strong>Accession #: </strong>" +r["Accession"].ToString()+"</td></tr>"; } StrResult += "</table>"; 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

1 2 3 4 5

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 run this query in MS access database it returns the two records. Why is it so, any one have an idea. thanks

• This topic was modified 1 year, 2 months ago by M. Saqib.

[Author]

Posts