Ad:

C# Discussion Thread
Unsolved
Views: 38374

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Permalink

Feb 3rd, 2006

sql query problem with MS Access and C#

Expand Post »

[HTML][/HTML]

(Toggle Plain Text)

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;
 
}
 
 
 
}
 
 
 
}
 
}

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

Similar Threads

SQL query problem with WHERE clause in ASP
Please help me out with MySQL query in MySQL
query problem in MySQL

msaqib

Reputation Points: 9

Solved Threads: 1

Junior Poster in Training

Offline

91 posts
since Sep 2004

Permalink

Feb 5th, 2006

Re: sql query problem with MS Access and C#

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

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
}
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
}

plazmo

Reputation Points: 23

Solved Threads: 16

Posting Whiz in Training

Offline

206 posts
since Aug 2005

Permalink

Feb 7th, 2006

Re: sql query problem with MS Access and C#

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?

msaqib

Reputation Points: 9

Solved Threads: 1

Junior Poster in Training

Offline

91 posts
since Sep 2004

Permalink

Feb 7th, 2006

Re: sql query problem with MS Access and C#

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

plazmo

Reputation Points: 23

Solved Threads: 16

Posting Whiz in Training

Offline

206 posts
since Aug 2005

Permalink

Feb 8th, 2006

Re: sql query problem with MS Access and C#

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

msaqib

Reputation Points: 9

Solved Threads: 1

Junior Poster in Training

Offline

91 posts
since Sep 2004

Permalink

Mar 28th, 2006

Re: sql query problem with MS Access and C#

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?

colpace

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Mar 2006

Permalink

Mar 28th, 2006

Re: sql query problem with MS Access and C#

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

C# Syntax (Toggle Plain Text)

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

plazmo

Reputation Points: 23

Solved Threads: 16

Posting Whiz in Training

Offline

206 posts
since Aug 2005

Permalink

Mar 29th, 2006

Re: sql query problem with MS Access and C#

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.

colpace

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Mar 2006

Permalink

Mar 29th, 2006

Re: sql query problem with MS Access and C#

No i am still having the same problem.

msaqib

Reputation Points: 9

Solved Threads: 1

Junior Poster in Training

Offline

91 posts
since Sep 2004

Permalink

Mar 29th, 2006

Re: sql query problem with MS Access and C#

can i see the latest version of your page and code

plazmo

Reputation Points: 23

Solved Threads: 16

Posting Whiz in Training

Offline

206 posts
since Aug 2005

Page 1
2
Next

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in C# Forum Timeline: Join table from different databse

Next Thread in C# Forum Timeline: Find the Largest Prime Factor of a Composite Number?

DaniWeb Message

Cancel Changes

User Name
Password