Edit my code to search 2 or more tables in access database file.

(ASP.NET 2) I have created a search page that searches  for entered text (serial - full or partial). This works fine. I need the perform the search across multiple tables e.g table1, table2, table3. Is this possible? All tables have the same identical columns. However, whatever I try, I cannot search mutliple tables. I've tried changing the select to "select serial, model, sold_on  from table1, table2"

Can anyone help.

My current single search working code is :

<script runat="server">

Sub gogetit(ByVal sender As Object, ByVal e As EventArgs)

Dim strConn As String

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("test.mdb") & ";"

Dim MySQL As String = "select serial, model, sold_on  from table1" & _

"where serial  like '%" + selection.Value + "%'"

Dim Myconn As New OleDbConnection(strConn)

Dim objDR As OleDbDataReader

Dim Cmd As New OleDbCommand(MySQL, Myconn)


objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddlserial.DataSource = objDR



End Sub




<INPUT TYPE=text ID=selection VALUE="Enter Serial Number" SIZE=30 runat="server">&nbsp;<INPUT id="Submit1" TYPE=submit OnServerClick="gogetit" VALUE="Search" runat="server"></p>

<asp:DataGrid id="ddlserial"  runat="server"  AutoGenerateColumns="False" EnableViewState="False">


<asp:BoundColumn DataField="serial" HeaderText="Serial Number" />

<asp:BoundColumn DataField="model" HeaderText="Model" />

<asp:BoundColumn DataField="sold_on" HeaderText="sold on" />



3/20/2006 10:12:36 PM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 5
Get it on Google Play
Get it on Apple App Store

MS Access allows for something called a union query.  You will have to do your search on this new query as opposed to directly on the tables.  To create a union query, start by creating a new query and then from the query menu in Access choose SQL Specific and then union query.  There is no designer or wizard support for union queries.  Your SQL will look something like this

select table1.serial, table1.model, table1.sold_on  from table1

UNION select table2.serial, table2.model, table2.sold_on  from table2;


Once you've saved your new union query, run your search against it.  It will be hard to impossible to know where the search results are comming from which brings us to the next point.

Why would you have multiple tables in the same DB that have the same structure?  It is typically bad DB design to do such things.

Whether you think you can or you think you can't; you're right.
If Heaven ain't a lot like Detroit, then I don't wanna go... - Uncle Cracker
3/22/2006 5:24:04 PM