Solved

find value in any table and any column

Posted on 2007-12-04
12
1,192 Views
Last Modified: 2012-08-13
I want to use vb.net to search all the tables in a ms sql 2000 db for a value. I want it to return the tables and associated rows it finds. The app is already connected to the db.

I only have read-only access, so you can't use temp tables or cursors unless they work read-only. I would rather use temp arrays on the client for the hunt.

P.S.  When I click New Project, Add Data Source, Next, Next, Finish, pick a table, and then drag it to the form so it makes a datagridview, here is the code I get:


Public Class Form1
    Private Sub AbcAnalysisBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AbcAnalysisBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.AbcAnalysisBindingSource.EndEdit()
        Me.AbcAnalysisTableAdapter.Update(Me.CompanyADataSet.AbcAnalysis)
    End Sub
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'CompanyADataSet.AbcAnalysis' table. You can move, or remove it, as needed.
        Me.AbcAnalysisTableAdapter.Fill(Me.CompanyADataSet.AbcAnalysis)
    End Sub
End Class

Open in new window

0
Comment
Question by:Pizpot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20404170
If you have a DataTable, you can use the Select method to search through the table using an expression.

Bob
0
 

Author Comment

by:Pizpot
ID: 20404228
Keep going...
0
 

Author Comment

by:Pizpot
ID: 20404352
Bob, I don't have one data table, I have 700. I want a vb.net program to go through each table one at a time, load it into an array, and look for a values that match. Then return the tables and rows it found. I have read-only access only to the db, so I want this done client. Is that too strange a way of doing it?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 96

Expert Comment

by:Bob Learned
ID: 20405502
You can use a For Each with the DataTables for the DataSet:

Dim list As New List(Of DataRow)
For Each dt As DataTable In ds.Tables
   Dim rows() As DataRow = dt.Select(String.Format("FirstName='{0}' ", "Bob"))

   If rows.Length > 0 Then
      list.AddRange(rows)
   End If
Next dt

Bob
0
 

Author Comment

by:Pizpot
ID: 20405637
what if I don't know the column name that the value will be under?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20405732
Does that mean that you want to search all DataTables and all DataColumns for a value?

Bob
0
 

Author Comment

by:Pizpot
ID: 20405776
Yes. I am in the situation having 700 tables and no docs or code. Currently I have this working... it outputs the table names to a listbox. It only works after I Add the table "AbcAnalysis" as a Data Source.
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        ListBox1.Items.Clear()
 
        Me.AbcAnalysisTableAdapter.Fill(Me.CompanyADataSet1.AbcAnalysis)
        Dim table As DataTable
        Me.AbcAnalysisTableAdapter.Connection.Open()
        table = Me.AbcAnalysisTableAdapter.Connection.GetSchema("Tables")
        Me.AbcAnalysisTableAdapter.Connection.Close()
 
        Dim view As DataView = table.DefaultView
        view.Sort = "TABLE_NAME ASC"
        table = view.ToTable(False, New String() {"TABLE_NAME"})
 
        'output list of tables
        For Each row As DataRow In table.Rows
            ListBox1.Items.Add(row("TABLE_NAME"))
        Next
    End Sub

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20406321
You can get table schema from SQL Server without adding a DataSource and TableAdapters.  Did you think about FREETEXT or CONTAINS on the backend?

Bob
0
 

Author Comment

by:Pizpot
ID: 20406412
I don't know what they are yet. Going back to the code you put up above starting with:

Dim list As New List(Of DataRow)
For Each dt As DataTable In ds.Tables
...

How do I use this? How do I define my ds. Can I say
dim ds as connectionString="Data Source=CUBSQL;Initial Catalog=CompanyA;Persist Security Info=True;User ID=odbc;Password=*********"

I know that is not a valid line, can you set me up a bit?
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 20406525
You should be using an SQL Server connection string:

http://www.connectionstrings.com/?carrier=sqlserver

Data Source=myServer;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Bob
0
 

Author Comment

by:Pizpot
ID: 20406773
How do I:

dim ds as datasource="CUBSQL"
dim ic as "CompanyA"
dim cs as connectionstring="Data Source=CUBSQL;Initial Catalog=CompanyA;Persist Security Info=True;User ID=odbc;Password=*****"
ds.ic.connect cs      'I made up this command. How do I define my connection and connect with code?"
0
 

Author Comment

by:Pizpot
ID: 20407088
Imports System.Data.SqlClient

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim myConnection As SqlConnection
        myConnection = New SqlConnection("Data Source=CUBSQL;Initial Catalog=CompanyA;Persist Security Info=True;User ID=odbc;Password=odbc123")
        Try
            myConnection.Open()
           
            'do something here

            myConnection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question