Solved

find value in any table and any column

Posted on 2007-12-04
12
1,186 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
  • 7
  • 5
12 Comments
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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
Comment Utility
Keep going...
0
 

Author Comment

by:Pizpot
Comment Utility
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
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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
Comment Utility
what if I don't know the column name that the value will be under?
0
 
LVL 96

Expert Comment

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

Bob
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Pizpot
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now