Søg i flere tabeller i Db - vises i datagrid

Hej Elvstine

Du er nødt til at hente en tabel ad gangen når du bruger OleDb namespacet, så vidt jeg ved.
...og hvis du ikke har noget at bruge DataSettet til bagefter, så er det lidt overkill at bruge DataSet til at hente data ind, det er langsommere og tager flere ressourcer på serveren end DataReader klassen.

Det ser ud til at du har samme kolonner i alle fire tabeller er det korrekt?

I så fald kan du, efter at have lavet din SQLsøgestreng og tilføjet ekstra DataGrids på din side (f.eks. DataGrid1, DataGrid2, DataGrid3 og DataGrid4)

- gøre som følger.

                ObjConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("gemDB.MDB") & ";")
        objComm = New OleDbCommand
        'sæt command objektet til at anvende connection objektet
        objComm.Connection = ObjConn

        'sæt dit Command objekt til at bruge SQL'en
        objComm.CommandText = sql1
        'åben forbindelsen
        objConn.Open()
        'Afvikl SQL sætningen
        DataGrid1.DataSource = objComm.ExecuteReader()
        'hent data ind i DataGrid'et
        DataGrid1().DataBind()
        'luk forbindelsen igen
        ObjConn.Close()
        'åben forbindelsen


        'sæt dit Command objekt til at bruge SQL'en
        objComm.CommandText = sql2
        'åben forbindelsen
        ObjConn.Open()
        'Afvikl SQL sætningen
        DataGrid2.DataSource = objComm.ExecuteReader()
        'hent data ind i DataGrid'et
        DataGrid2().DataBind()
        'luk forbindelsen igen
        ObjConn.Close()
        'åben forbindelsen

Her har du så gemt SQL sætningerne i sql1, sql2, etc. hvor du bare udskifter tabellens navne.

Håber det hjalp.

Venligste hilsener - Jakob

Hej Jakob

1000 tak for hjælpen, men der er kommet en lille bivirkning :-)

Hvis brugeren først vælger (vha dropdown) at søge i tabel1 for derefter at søge i tabel2 så vises datagrid fra tabel 1 stadig.

Man kan jo ikke skrive .text = "" som ved label og textbox har du et forslag??

Hvis det kan hjælpe her jeg inkluderet min færdige kode:

<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.Oledb" %>
<script runat="server">

Dim ObjConn As OledbConnection
Dim ObjRs As OledbDataReader
Dim objComm As OledbCommand

Sub Page_Load()
smartnavigation="true"
OpenDb()
End Sub

Sub Page_UnLoad()
CloseDB()
End Sub

Sub OpenDB()
ObjConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.Mappath("GemDb.MDB") & ";")
ObjComm = New OleDbCommand(strSQL, objConn)
ObjConn.Open()
End Sub

Sub CloseDB()
ObjConn.Close
End Sub

Sub Soeg_Click(sender As Object, e As EventArgs)
Dim    strSearch, strTabel, strSQLTabel1, strSQLTabel2, strSQLTabel3, strSQLTabel4 As String
Dim objDataSet as new DataSet()
Dim objAdapter as new OledbDataAdapter(strSQL, objConn)


Select Case DropTabel.SelectedItem.Value
Case "Ingen"
LblFejlmeddelelse.Text = "Du skal vælge Tabel for at kunne søge"

Case "Tabel1"
If Len(TxtOrdreNr.Text) > 0 Then
strSearch = strSearch & " OrdreNr LIKE '%" & txtOrdrenr.Text & "%' AND"
End If

If Len(TxtKunde.Text) > 0 Then
strSearch = strSearch & " Kunde LIKE '%" & txtKunde.Text & "%' AND"
End If

If Len(TxtForhandlerkode.Text) > 0 Then
strSearch = strSearch & " Forhandlerkode LIKE '%" & txtForhandlerkode.Text & "%' AND"
End If

If Len(TxtLevUge.Text) > 0 Then
strSearch = strSearch & " Leveringsuge LIKE '%" & txtLevUge.Text & "%' AND"
End If

If strSearch <> "" Then
strSearch = Mid$(strSearch, 1, Len(strSearch) - 4)

strSQL = "SELECT * FROM Tabel1 WHERE " & strSearch & " order by Ordrenr Asc;"

objComm.CommandText = strSQL
OpenDB()
DataGridTabel1.DataSource = objComm.ExecuteReader()
DataGridTabel1.DataBind()

end if
CloseDB()

LblTabel1.text = "Tabel1"
LblTabel2.text = ""
LblTabel3.text = ""
LblTabel4.text = ""

Case "Tabel2"
If Len(TxtOrdreNr.Text) > 0 Then
strSearch = strSearch & " OrdreNr LIKE '%" & txtOrdrenr.Text & "%' AND"
End If

If Len(TxtKunde.Text) > 0 Then
strSearch = strSearch & " Kunde LIKE '%" & txtKunde.Text & "%' AND"
End If

If Len(TxtForhandlerkode.Text) > 0 Then
strSearch = strSearch & " Forhandlerkode LIKE '%" & txtForhandlerkode.Text & "%' AND"
End If

If Len(TxtLevUge.Text) > 0 Then
strSearch = strSearch & " Leveringsuge LIKE '%" & txtLevUge.Text & "%' AND"
End If


If strSearch <> "" Then
strSearch = Mid$(strSearch, 1, Len(strSearch) - 4)

strSQL = "SELECT * FROM Tabel2 WHERE " & strSearch & " order by Ordrenr Asc;"

objComm.CommandText = strSQL
OpenDB()
DataGridTabel2.DataSource = objComm.ExecuteReader()
DataGridTabel2.DataBind()
end if
CloseDB()

LblTabel1.text = ""
LblTabel2.text = "Tabel2"
LblTabel3.text = ""
LblTabel4.text = ""

Case= "Tabel3"
OpenDB()

If Len(TxtOrdreNr.Text) > 0 Then
strSearch = strSearch & " OrdreNr LIKE '%" & txtOrdrenr.Text & "%' AND"
End If

If Len(TxtKunde.Text) > 0 Then
strSearch = strSearch & " Kunde LIKE '%" & txtKunde.Text & "%' AND"
End If

If Len(TxtForhandlerkode.Text) > 0 Then
strSearch = strSearch & " Forhandlerkode LIKE '%" & txtForhandlerkode.Text & "%' AND"
End If

If Len(TxtLevUge.Text) > 0 Then
strSearch = strSearch & " Leveringsuge LIKE '%" & txtLevUge.Text & "%' AND"
End If


If strSearch <> "" Then
strSearch = Mid$(strSearch, 1, Len(strSearch) - 4)

strSQL = "SELECT * FROM Tabel3 WHERE " & strSearch & " order by Ordrenr Asc;"

objComm.CommandText = strSQL
OpenDB()
DataGridTabel3.DataSource = objComm.ExecuteReader()
DataGridTabel3.DataBind()
end if
CloseDB()

LblTabel1.text = ""
LblTabel2.text = ""
LblTabel3.text = "Tabel3"
LblTabel4.text = ""

Case "Tabel4"
OpenDB()

If Len(TxtOrdreNr.Text) > 0 Then
strSearch = strSearch & " OrdreNr LIKE '%" & txtOrdrenr.Text & "%' AND"
End If

If Len(TxtKunde.Text) > 0 Then
strSearch = strSearch & " Kunde LIKE '%" & txtKunde.Text & "%' AND"
End If

If Len(TxtForhandlerkode.Text) > 0 Then
strSearch = strSearch & " Forhandlerkode LIKE '%" & txtForhandlerkode.Text & "%' AND"
End If

If Len(TxtLevUge.Text) > 0 Then
strSearch = strSearch & " Leveringsuge LIKE '%" & txtLevUge.Text & "%' AND"
End If


If strSearch <> "" Then
strSearch = Mid$(strSearch, 1, Len(strSearch) - 4)

strSQL = "SELECT * FROM Tabel4 WHERE " & strSearch & " order by Ordrenr Asc;"

objComm.CommandText = strSQL
OpenDB()
DataGridTabel4.DataSource = objComm.ExecuteReader()
DataGridTabel4.DataBind()
end if
CloseDB()

LblTabel1.text = ""
LblTabel2.text = ""
LblTabel3.text = ""
LblTabel4.text = "Tabel4"

Case "Alle"

If Len(TxtOrdreNr.Text) > 0 Then
strSearch = strSearch & " OrdreNr LIKE '%" & txtOrdrenr.Text & "%' AND"
End If

If Len(TxtKunde.Text) > 0 Then
strSearch = strSearch & " Kunde LIKE '%" & txtKunde.Text & "%' AND"
End If

If Len(TxtForhandlerkode.Text) > 0 Then
strSearch = strSearch & " Forhandlerkode LIKE '%" & txtForhandlerkode.Text & "%' AND"
End If

If Len(TxtLevUge.Text) > 0 Then
strSearch = strSearch & " Leveringsuge LIKE '%" & txtLevUge.Text & "%' AND"
End If


If strSearch <> "" Then
strSearch = Mid$(strSearch, 1, Len(strSearch) - 4)

OpenDB()
strSQLTabel1 = "SELECT * FROM Tabel1 WHERE " & strSearch & " order by Ordrenr Asc;"
objComm.CommandText = strSQLTabel1
DataGridTabel1.DataSource = objComm.ExecuteReader()
DataGridTabel1.DataBind()
CloseDB()

OpenDB()
strSQLTabel2 = "SELECT * FROM Tabel2 WHERE " & strSearch & " order by Ordrenr Asc;"
objComm.CommandText = strSQLTabel2
DataGridTabel2.DataSource = objComm.ExecuteReader()
DataGridTabel2.DataBind()
CloseDB()

OpenDB()
strSQLTabel3 = "SELECT * FROM Tabel3 WHERE " & strSearch & " order by Ordrenr Asc;"
objComm.CommandText = strSQLTabel3
DataGridTabel3.DataSource = objComm.ExecuteReader()
DataGridTabel3.DataBind()
CloseDB()

OpenDB()
strSQLTabel4 = "SELECT * FROM Tabel4 WHERE " & strSearch & " order by Ordrenr Asc;"
objComm.CommandText = strSQLTabel4
DataGridTabel4.DataSource = objComm.ExecuteReader()
DataGridTabel4.DataBind()
CloseDB()

end if
LblTabel1.text = "Tabel1"
LblTabel2.text = "Tabel2"
LblTabel3.text = "Tabel3"
LblTabel4.text = "Tabel4"
End Select
LblFejlmeddelelse.Text = ""
End Sub


</script>
<html>
<head>
<title>Søg</title>
</head>
<body>
<form runat="server" >
<table border="0" cellpadding="4" cellspacing="0" class="Text" id="Soeg">
<tr>
<td><h1>Søg</h1></td>
<td>&nbsp;</td>
<td align="left">&nbsp;</td>
</tr>
<tr>
<td> Ordre nummer: </td>
<td><asp:TextBox ID="TxtOrdrenr" runat="server"></asp:TextBox></td>
<td align="left"><asp:Button ID="BtnSoeg" OnClick="Soeg_click" Text="S&oslash;g" runat="server" /></td>
</tr>
<tr>
<td>Kunde Nr: </td>
<td><asp:TextBox ID="TxtKunde" runat="server" /></td>
<td rowspan="4"><asp:Label CssClass="RoedPrik" ID="LblFejlmeddelelse" runat="server" /></td>
</tr>
<tr>
<td>Forhandlerkode: </td>
<td><asp:TextBox ID="TxtForhandlerKode" runat="server" /></td>
</tr>
<tr>
<td>Leverings uge:</td>
<td><asp:TextBox ID="TxtLevUge" runat="server" /></td>
</tr>
<tr>
<td>Tabel: </td>
<td><asp:DropDownList ID="DropTabel" runat="server">
<asp:ListItem Value="Ingen" Text="V&aelig;lg" Selected="true"></asp:ListItem>
<asp:ListItem Value="Tabel1" Text="Tabel1" ></asp:ListItem>
<asp:ListItem Value="Tabel2" Text="Tabel2" ></asp:ListItem>
<asp:ListItem Value="Tabel3" Text="Rulle Tabel2" ></asp:ListItem>
<asp:ListItem Value="Tabel4" Text="Tabel4" ></asp:ListItem>
<asp:ListItem Value="Alle" Text="Alle" ></asp:ListItem>
</asp:DropDownList></td>
</tr>
</table>
<p><asp:Label ID="LblTabel1" CssClass="TabelOverskrift" runat="server" /></p>
<p>
<asp:DataGrid CssClass="Text" id="DataGridTabel1" runat="server" CellPadding="3" BorderColor="#000000" BorderWidth="1px" BorderStyle="None">
<FooterStyle forecolor="#000000" backcolor="White"></FooterStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#000000"></HeaderStyle>
<PagerStyle horizontalalign="Left" forecolor="#000000" backcolor="White" mode="NumericPages"></PagerStyle>
<SelectedItemStyle font-bold="True" forecolor="White" backcolor="#000000"></SelectedItemStyle>
<ItemStyle forecolor="#000000"></ItemStyle>
</asp:DataGrid>


</p>    <p><asp:Label ID="LblTabel2" CssClass="TabelOverskrift" runat="server" /></p>
<p>
<asp:DataGrid CssClass="Text" id="DataGridTabel2" runat="server" CellPadding="3" BorderColor="#000000" BorderWidth="1px" BorderStyle="None">
<FooterStyle forecolor="#000000" backcolor="White"></FooterStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#000000"></HeaderStyle>
<PagerStyle horizontalalign="Left" forecolor="#000000" backcolor="White" mode="NumericPages"></PagerStyle>
<SelectedItemStyle font-bold="True" forecolor="White" backcolor="#000000"></SelectedItemStyle>
<ItemStyle forecolor="#000000"></ItemStyle>
</asp:DataGrid>
</p>


<p><asp:Label ID="LblTabel3" CssClass="TabelOverskrift" runat="server" /></p>
<p>
<asp:DataGrid CssClass="Text" id="DataGridTabel3" runat="server" CellPadding="3" BorderColor="#000000" BorderWidth="1px" BorderStyle="None">
<FooterStyle forecolor="#000000" backcolor="White"></FooterStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#000000"></HeaderStyle>
<PagerStyle horizontalalign="Left" forecolor="#000000" backcolor="White" mode="NumericPages"></PagerStyle>
<SelectedItemStyle font-bold="True" forecolor="White" backcolor="#000000"></SelectedItemStyle>
<ItemStyle forecolor="#000000"></ItemStyle>
</asp:DataGrid>
</p>


<p><asp:Label ID="LblTabel4" CssClass="TabelOverskrift" runat="server" /></p>
<p>
<asp:DataGrid CssClass="Text" id="DataGridTabel4" runat="server" CellPadding="3" BorderColor="#000000" BorderWidth="1px" BorderStyle="None">
<FooterStyle forecolor="#000000" backcolor="White"></FooterStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#000000"></HeaderStyle>
<PagerStyle horizontalalign="Left" forecolor="#000000" backcolor="White" mode="NumericPages"></PagerStyle>
<SelectedItemStyle font-bold="True" forecolor="White" backcolor="#000000"></SelectedItemStyle>
<ItemStyle forecolor="#000000"></ItemStyle>
</asp:DataGrid>
</p>
</form>
</body>
</html>

Jeg har stadig ikke løst probelemet med at tømme datagrid'et.

men smid et svar så jeg kan lukke

Sover du :-)

Hej Jakob.
Det er snart 1 år siden du skrev.
Smider du snart et svar så jeg kan lukke?

Ellers lukker jeg og tager pointene selv.

Jakob / snakejake>>>
Hvis du ikke svarer inden 01-04-2007 lukker jeg selv.