| | |
sql query problem with MS Access and C#
Please support our C# advertiser: $4.95 a Month - ASP.NET Web Hosting – Click Here!
![]() |
•
•
Join Date: Sep 2004
Posts: 89
Reputation:
Solved Threads: 1
[HTML][/HTML]
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 454 wrox
Executive Management Executive Management 4545 wrox
Electrical Engineering Electrical Engineering 4545 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
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.AppSettings["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>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 454 wrox
Executive Management Executive Management 4545 wrox
Electrical Engineering Electrical Engineering 4545 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
ok below is code i made to connect to a access database.
the Select function takes your query and returns a dataset
datasets are really easy to work with. if you dont have any experience with them i could post how to use them.
also use parameters this doesnt have it, i have some on my webpage that i didnt want to dig up, but it prevents sql injections,
oh yeah and try to keep the sql function outside of the button click because it makes it easier to manage if you ever need to use it again on that page or you could just copy and paste it onto a different page without editing stuff
the Select function takes your query and returns a dataset
datasets are really easy to work with. if you dont have any experience with them i could post how to use them.
also use parameters this doesnt have it, i have some on my webpage that i didnt want to dig up, but it prevents sql injections,
oh yeah and try to keep the sql function outside of the button click because it makes it easier to manage if you ever need to use it again on that page or you could just copy and paste it onto a different page without editing stuff
C# Syntax (Toggle Plain Text)
using System; using System.Data; using System.Data.Common; using System.Data.OleDb; namespace DataConnection { /// <summary> /// Creates connection to query and modify a database /// </summary> public class DBConnection { private System.String strConnection;//connection string public DBConnection(string ConnectionString) { strConnection = ConnectionString; //strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;" + // @"Data Source=\dir\Mydb.mdb; User Id=dbUser; Password=pass"; } protected OleDbConnection Connect() { OleDbConnection conn = null; //create the connection from the connection string conn = new OleDbConnection(strConnection); conn.Open(); return conn; } //Used for database SELECT queries public System.Data.DataSet Select(string selectQuery) { if(selectQuery.Length <=1) { return null; } //create new connection OleDbConnection conn = this.Connect(); DataSet dataSet = new DataSet(); //create new adapter and command builder OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter); //Send SELECT query - set the dataadapters select statment to our select // and out open connection dataAdapter.SelectCommand = new OleDbCommand (selectQuery, conn); //fills our datatable with retreived table dataAdapter.Fill(dataSet); conn.Close(); conn.Dispose(); return dataSet; }//end Select //Used to INSERT, DELETE, UPDATE sql statments public void Modify(string mod) { OleDbConnection conn = this.Connect(); OleDbCommand command = new OleDbCommand(mod,conn); command.ExecuteNonQuery(); conn.Close(); conn.Dispose(); }//end Modify }//end DBConnection }
•
•
Join Date: Sep 2004
Posts: 89
Reputation:
Solved Threads: 1
sorry, actually u didnt understand my problem. I jsut wanted to know that
if i run "select * from book where name like '*x*' OR description like '*x*';" from the MS Access then it returns some records containing X somewhere in the text.
But if i run it throught C# code and execute the query it returns no records.
why?
if i run "select * from book where name like '*x*' OR description like '*x*';" from the MS Access then it returns some records containing X somewhere in the text.
But if i run it throught C# code and execute the query it returns no records.
why?
•
•
Join Date: Sep 2004
Posts: 89
Reputation:
Solved Threads: 1
wel sir, i tried both the * and % . but both are not working even.
Do u think there may be some other problem. Have anybody checked this sort of quiries...
Coz i have tried many queries with like keyword, but all are not working.
Plz any one try it and give me the results.
thanks
Do u think there may be some other problem. Have anybody checked this sort of quiries...
Coz i have tried many queries with like keyword, but all are not working.
Plz any one try it and give me the results.
thanks
are you even connecting to the database? like can you run a simple "select * from book"
because i just tried "select * from book where name like '%x%' OR description like '%x%'"
in c# and it worked fine
check your connection string maybe, it should look like this for access
because i just tried "select * from book where name like '%x%' OR description like '%x%'"
in c# and it worked fine
check your connection string maybe, it should look like this for access
C# Syntax (Toggle Plain Text)
string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Mydb.mdb;";
•
•
Join Date: Mar 2006
Posts: 4
Reputation:
Solved Threads: 0
yes i am connecting to the database, and i am getting,inserting and updating data in the db.
However when i run an SQL query which includes the LIKE statement and add the wild card * before and after the text being searched, the returned resultset is empty. If I run the SAME query directly on Access as a query, the result set is not empty.
However when i run an SQL query which includes the LIKE statement and add the wild card * before and after the text being searched, the returned resultset is empty. If I run the SAME query directly on Access as a query, the result set is not empty.
•
•
Join Date: Sep 2004
Posts: 89
Reputation:
Solved Threads: 1
No i am still having the same problem.
![]() |
Similar Threads
- SQL query problem with WHERE clause (ASP)
- Please help me out with MySQL query (MySQL)
- query problem (MySQL)
Other Threads in the C# Forum
- Previous Thread: Salary calculation
- Next Thread: how to write sql connection string
| Thread Tools | Search this Thread |
.net access algorithm animation array bitmap box c# check checkbox client combobox control conversion csharp customactiondata cyclethruopenforms data database datagrid datagridview dataset date/time datetime datetimepicker degrees directrobot dll draganddrop drawing encryption enum excel file filename files finalyearproject form format forms function gdi+ getoutlookcontactusinfcsvfile globalization gtk hash image input install installer java list math mono mouseclick mysql operator outlook2003 panel path photoshop picturebox pixelinversion pixelminversion post print programming radians regex remoting richtextbox save server sleep snooze socket sql sql-server statistics string table tables tcp text textbox thread time timer timespan update usercontrol usercontrols users validate validation visualstudio webcam wia wpf xml





