Click here to Skip to main content
13,505,751 members
Rate this:
Please Sign up or sign in to vote.
See more:
I Have a query in which i want to search in all the table for a common field using query below ismy code----

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
         'Dim da As OleDb.OleDbDataAdapter
         Dim dbprovider As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Taher\Documents\Visual Studio 2010\Projects\WindowsApplication1\WindowsApplication1\Database1.accdb;Persist Security Info=False;"
         Me.con = New OleDb.OleDbConnection
         Dim sqlquery As String = "SELECT * FROM **mba mca mscit** WHERE urno='" & TextBox2.Text & "';"
         Dim command As New OleDb.OleDbCommand(sqlquery, con)
         Dim reader As OleDb.OleDbDataReader
         con.ConnectionString = dbprovider

         reader = command.ExecuteReader()
         TextBox1.Text = reader(1).ToString
         TextBox2.Text = reader(2).ToString
         TextBox3.Text = reader(3).ToString
         TextBox4.Text = reader(4).ToString
         TextBox5.Text = reader(5).ToString
     Catch ex As Exception
         MsgBox("Not Found")
     End Try

i want to search for urno in all the tables which have the fields in them with the same name....?
Posted 21-Feb-13 9:11am
Updated 21-Feb-13 9:14am
Maciej Los 21-Feb-13 15:46pm
And the question is...
tacky0 21-Feb-13 16:04pm
i want to search urno in all tables

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

You have to do a SELECT on each table. You can use UNION to gather the results of each SELECT into one result set. Be sure that each SELECT yields the same number and type of column.

This example is from Books Online for SQL Server 2012 UNION Operator
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e1
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e2

Please look into using SQLParameter rather than concatenating a SELECT statement using user entered data. The way that you construct your SELECT statement leaves your program susceptible to an SQL Injection Attack. Using SQL Injection Attack, a nefarious individual could erase or change data in your database.

Also, please consider whether you should really have this information in separate tables.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web04 | 2.8.180417.1 | Last Updated 21 Feb 2013
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100