(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 :
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
<INPUT TYPE=text ID=selection VALUE="Enter Serial Number" SIZE=30 runat="server"> <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" />
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