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
Solved

find value in any table and any column

Posted on 2007-12-04
12
1,191 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
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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