sql query problem with MS Access and C#

[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.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

[plazmo]

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

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
}

[msaqib]

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?

[plazmo]

oh my bad, well the reason is because '*' is not the wildcard its '%'
so you just new to replace the *'s in your like clauses with %'s

[msaqib]

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

[colpace]

i have the same problem. i have tried both * and %. * works in MS Access but not from C#.

did u manage to solve it or go around it?

[plazmo]

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

string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Mydb.mdb;";

[colpace]

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.

[msaqib]

No i am still having the same problem.

[plazmo]

can i see the latest version of your page and code

[fathih]

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?

"select * from book where name like '*x*' OR description like '*x*';"
replace your query with the following
"select * from book where name like '%x%' OR description like '%x%';"

[kursat]

Hi there,
I have the same problem, is there any solution to that. I tried both * and % but nothing changed. it does not return any record.

Regards

[JMu]

Hello.
I ran into the same problem today. Fortunatelly for me :), I found the solution.
My old code:

filter+= "(invoice.serie LIKE '%" + txtInvoiceSerie.Text + "%')";

I tried with both % and *. Nothing worked. I searched google, but didn't found answear. Then I tried:

filtru += "(invoice.serie LIKE '" + '%' + txtInvoiceSerie.Text + '%'+"')";

Adding % sepparately, as a char, does the trick. (depending on the access file version, you may need to use * instead of % - the former works on Access 2003 file format).

I hope this helps.
P.S. Sorry for the bad english, it is not my native language.

[abd_alrahman111]

Thank You All
I Had The same problem then when i replaced * with % every thing became right

Here is Some Code :

SQLStatement = SQLStatement + " and StudentName like '%" + textBox26.Text + "%'";
OleDbCommand command = new OleDbCommand(SQLStatement, gConnection);
gDataAdapter.SelectCommand = command;
gDataTable.Clear();
OleDbCommandBuilder gCommandBuilder = new OleDbCommandBuilder(gDataAdapter);
gDataAdapter.Fill(gDataTable);
dataGridView1.DataSource = gDataTable;

Sorry for the bad english, it is not my native language.
my language is Arabic.
I Like Arabic verymuch.

[edusilva]

Hello.

I'm having a similar problem. My LIKE sql statements are not retrieving the correct records. The difference, in my case, is that it is returning records, only incorrect records. Let's say the query should return 3 records. Instead of returning 3 different records, it returns the first record 3 times.

If I use *'s, I get no records, which makes be believe the correct wild card card is %. And it's using % that is causing the error described in the first paragraph.

I have already tried to append % surrounded by single quote, double quotes, in the same line as the rest of the string, and in a separate line. Every time I get the same result.

Does anybody have a clue? Any help will be much appreciated.

Thanks a lot in advance

[phildaniweb]

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?

Try '%x%' - Access uses * but pure sql uses % in LIKE statements

[zeroboo]

JMu was right, i tried his way and fixed the problem.
Thank you JMU

[deepakjoy]

It works in C# because you're likely using OLEDB to execute it, which means Jet/ACE is running in ANSI 92 SQL mode, where % is the wildcard operator. Within Access, the default is the legacy (and proprietary) ASNI 89 SQL mode, and that uses * as the wildcard.