0

I have an access database which I am using with .net, I can search tables and maker very specific searches no problem, trouble is that I can't work out how to make global searches. IE search entire databse for "fred" or search a single table for "fred". Anyone know what the SQL string might look like:

3
Contributors
4
Replies
30
Views
3 Years
Discussion Span
Last Post by TnTinMN
0

Unfortunatly that example is writen in c# and I use .net. However I think I get the jist, basically there is no single sql statement, instead you get a list of tables and then search in each table in turn.

0

That example is actually SQL, and C# is a .Net Language.

The problem exists on querying each table.

If you have a DataSet with each table strongly typed you can try a For Each loop.

Either way, there is no single line statement to solve the problem.

0

Not sure of what use this would be, but for OLEDB connections you could do it like this:

Private Sub Search()
   Dim conn As New OleDb.OleDbConnection("Your Connection String ")
   conn.Open()
   ' get all tables filtered to user tables
   Dim dbTables As DataTable = conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "Table"})
   conn.Close()

   For Each tableentry As DataRow In dbTables.Rows
      Dim cmd As New OleDb.OleDbCommand("Select * From [" & tableentry("Table_Name").ToString() & "]", conn)
      Dim da As New OleDb.OleDbDataAdapter(cmd)
      Dim tmpTable As New DataTable
      da.Fill(tmpTable)

      ' get a list of string datatype columns in tmpTable
      Dim cols As List(Of DataColumn) = (From col In tmpTable.Columns _
                                         Select dc = CType(col, DataColumn) _
                                         Where dc.DataType Is GetType(String) _
                                        ).ToList()

      For Each r As DataRow In tmpTable.Rows
         For Each dc As DataColumn In cols
            If r.ItemArray(dc.Ordinal).ToString().Contains("fred") Then
               'found one
               Debug.WriteLine("Table:  " & tableentry("Table_Name").ToString())
               Debug.WriteLine("Column:  " & dc.ColumnName)
               Debug.WriteLine("Value:  " & r.ItemArray(dc.Ordinal).ToString())

            End If
         Next
      Next

   Next
End Sub
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.