Solved

find value in any table and any column

Posted on 2007-12-04
12
1,195 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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 As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

630 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