Introduction
I was in the urgent need of a web application that can search into an Ms Access database without any limitations and check on data types. That is, I wanted to search into any Ms Access database in any particular table for a particular word.
I couldn't find such solution on the internet anywhere. At last i decided to do it myself and here it is.
Code
Without wasting much space here, Directly i am giving you the code
<script runat="server">
OleDbConnection conn;
int vCount = 0;
string cStr = null;
void Page_Load(Object Sender, EventArgs E)
{
MakeConnString();
if (!IsPostBack)
{
ListTables();
}
}
void SearchMe(Object Sender, EventArgs E)
{
conn = new OleDbConnection(cStr);
conn.Open();
string Sql = "select * from " + RadioButtonList1.SelectedItem.Value;
OleDbDataAdapter dAd = new OleDbDataAdapter(Sql, conn);
DataSet dSet = new DataSet();
dAd.Fill (dSet, "ThisTable");
dAd = null;
string vSearch = TextBox1.Text.ToString().ToLower();
string vRestString = null;
Regex r = new Regex(vSearch); int vNum = 0;
string vSearchInto = null;
StringBuilder vString = new StringBuilder("<table cellpadding='3' cellspacing='1' border='1' style='border-collapse:collapse;' class='tblParent'>", 5000);
for (int i = 0; i < dSet.Tables["ThisTable"].Rows.Count; i++)
{
vSearchInto = null;
for (int j = 0; j < dSet.Tables["ThisTable"].Columns.Count; j++)
{
vSearchInto += dSet.Tables["ThisTable"].Rows[i][j].ToString().ToLower()+" ";
}
//write search results
vRestString = null;
bool t = r.IsMatch(vSearchInto);
if (t)
{
vCount++;
vString.Append("<tr><td>"+ vCount + ".</td>");
foreach (DataColumn j in dSet.Tables["ThisTable"].Columns)
{
if (dSet.Tables["ThisTable"].Rows[i][j].ToString().Trim() != "")
{
vNum++;
if (vNum == 4) //add show/hide table
vString.Append("<td><a href=javascript:ShowResults('"+vCount+"')>more ...</a><div id='tblRes"+ vCount +"' style='display:none;'><table cellpadding='2' cellspacing='1' border='1' width='600' style='border-collapse:collapse;position:absolute;' class='tblMore'>");
if (vNum >= 4)
vString.Append("<tr valign='top'><td class='heading' width='100'>"+ j + "</td><td width='500'>" + Regex.Replace(dSet.Tables["ThisTable"].Rows[i][j].ToString(), vSearch, "<font color='blue'>"+ vSearch + "</font>", RegexOptions.IgnoreCase) + "</td></tr>");
else
vString.Append("<td class='heading'>"+ j + "</td><td>" + Regex.Replace(dSet.Tables["ThisTable"].Rows[i][j].ToString(), vSearch, "<font color='blue'>"+ vSearch + "</font>", RegexOptions.IgnoreCase) + "</td>");
}
}
if (vNum > 4) //close show/hide table
vString.Append("</table></div></td></tr>");
else
vString.Append("</tr>");
vNum = 0;
}
}
vString.Append("</table>");
Label1.Text = vString.ToString();
lblStatus.Text = "Searching <font color='red'> " + RadioButtonList2.SelectedItem.Value + "->"+ RadioButtonList1.SelectedItem.Value +"</font> for <font color='red'>"+ vSearch + "</font>, " + vCount + " results found.";
conn.Close();
}
void ListTables()
{
conn = new OleDbConnection(cStr);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"});
RadioButtonList1.Items.Clear();
for (int i = 0; i < schemaTable.Rows.Count ; i++)
{
RadioButtonList1.Items.Add (schemaTable.Rows[i]["TABLE_NAME"].ToString());
}
conn.Close();
}
void ChangeMe(Object Sender, EventArgs E)
{
MakeConnString();
ListTables();
}
void MakeConnString()
{
switch (RadioButtonList2.SelectedItem.Value)
{
case "ABC" :
cStr = ConfigurationSettings.AppSettings["AConn"];
break;
case "EFG" :
cStr = ConfigurationSettings.AppSettings["BConn"];
break;
case "FGGD" :
cStr = ConfigurationSettings.AppSettings["CConn"];
break;
case "FDSAF" :
cStr = ConfigurationSettings.AppSettings["DConn"];
break;
case "fixdate" :
cStr = ConfigurationSettings.AppSettings["EConn"];
break;
default :
cStr = ConfigurationSettings.AppSettings["AConn"];
break;
}
}
</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Searching Ms-Access database easy here by Sheo Narayan</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
body {
margin-left: 0px;
margin-top: 0px;
}
body {
font-family: Arial, Helvetica, sans-serif;
font-size: 8pt;
}
.heading {
color:#339900;
}
.tblMore
{
background-color:#F0F0F0;
}
.tblParent
{
font-size:10pt;
}
.style1 {
color: #FFFFFF;
font-weight: bold;
}
-->
</style>
<script language="javascript">
var vLoop = <%=vCount%>;
function ShowResults(id)
{
for (var i = 1; i <= vLoop; i++)
{
if (i==id)
document.getElementById('tblRes'+i).style.display==''?document.getElementById('tblRes'+i).style.display='none':document.getElementById('tblRes'+i).style.display='';
else
document.getElementById('tblRes'+i).style.display='none';
}
}
</script>
</head>
<body>
<form runat="server">
<table width="100%" style="border-collapse:collapse;" border="1" cellpadding="2" cellspacing="1" bordercolor="#996600">
<tr align="center" bgcolor="#6699FF">
<td colspan="3"><span class="style1">Search into tables </span></td>
</tr>
<tr valign="top">
<td>Select database </td>
<td><asp:RadioButtonList ID="RadioButtonList2" runat="server" RepeatLayout="table" RepeatDirection="Horizontal"
OnSelectedIndexChanged="ChangeMe" AutoPostBack="true">
<asp:ListItem value="clinic" Selected="true">clinic</asp:ListItem>
<asp:ListItem value="fixdate">fixdate</asp:ListItem>
<asp:ListItem value="pasadena">pasadena</asp:ListItem>
<asp:ListItem value="transaction">transaction</asp:ListItem>
<asp:ListItem value="verify">verify</asp:ListItem>
</asp:RadioButtonList></td>
<td> </td>
</tr>
<tr valign="top">
<td width="22%">Search </td>
<td width="64%"><asp:TextBox ID="TextBox1" runat="server" AutoPostBack="false" />
<asp:RequiredFieldValidator ControlToValidate="TextBox1" runat="server" Display="Dynamic" Text="*"></asp:RequiredFieldValidator> </td>
<td width="14%"><asp:Button ID="Button1" Text=" Search " runat="server" OnClick="SearchMe" /></td>
</tr>
<tr valign="top">
<td>Search into </td>
<td><asp:RadioButtonList ID="RadioButtonList1" runat="server" RepeatLayout="table" RepeatDirection="Horizontal" RepeatColumns="5"></asp:RadioButtonList> </td>
<td><asp:RequiredFieldValidator ControlToValidate="RadioButtonList1" runat="server" Display="Dynamic" Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr valign="top">
<td colspan="3"><asp:Label ID="lblStatus" runat="server" ForeColor="#0033CC" EnableViewState="false" Font-Size="12" Font-Bold="true" /> <br>
<asp:Label ID="Label1" runat="server" EnableViewState="false" /></td>
</tr>
<tr valign="top">
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
</form>
</body>
</html>
I have used the value of connection string from web.config file.
like
<add key="AConn" value="Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\InetPub\ABC\db\A.mdb;" />
So, what I have done in this article is just listed the tables from the database using schema and when the Search button is clicked. It catches all records from the table and make all fields data as a string and then search into it using Regular expression.
Hope this will be solution of someone else problem.
|
| Author: Rakesah O Sharma 12 Nov 2007 | Member Level: Bronze Points : 0 |
Hey , Its nice for me that i have found your code you have did the fantastic job for me Thanks .................................
|
| Author: Raphael Mutiso 26 Feb 2008 | Member Level: Bronze Points : 0 |
Thanks for the great code. I however would like the code to populate items on a datagrid. How can we go about it.?Thanks in advance.
|
| Author: madhan 18 Jul 2008 | Member Level: Bronze Points : 0 |
EXCELLENT , Very useful content.Thank u very muchhhhhhhhh
|