Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

find value in any table and any column

Posted on 2007-12-04
12
Medium Priority
?
1,197 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
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…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

722 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