I have code for search button which only access on table in databaseI want sql query which access All tables in database.Iam pasting following code

255 pts.
Tags:
VB.NET
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim connetionString As String Dim connection As SqlConnection Dim command As SqlCommand Dim adapter As New SqlDataAdapter Dim ds As New DataSet Dim dv As DataView Dim sql As String connetionString = "Data Source=NASSQLEXPRESS;Database=data2;Integrated Security=True; Connect Timeout=30;" sql = "Select * from Sfee where RegistrationNo='" & TextBox1.Text & "'" connection = New SqlConnection(connetionString) Try connection.Open() command = New SqlCommand(sql, connection) adapter.SelectCommand = command adapter.Fill(ds, "Sort DataView") adapter.Dispose() command.Dispose() connection.Close() dv = ds.Tables(0).DefaultView DataGridView1.DataSource = dv Catch ex As Exception MsgBox(ex.ToString) End Try End Sub Its working Perfectly,Plz reply me early Thanking u.

Answer Wiki

Thanks. We'll let you know when a new response is added.

There is no single SQL query which will select all data from all tables that matches your query. You’ll need to manually query each table you need to.

Discuss This Question: 13  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Could you please provide more information about your query ? What information do you want to get from the database ? What search criteria will you use if you want to get data from all tables (field names will be different) ? Try to make sure we understand what you need.
    69,465 pointsBadges:
    report
  • Shaz
    Hi, Iam making School Project,I have search buttton in my home page.If i click search button another form will come,In that i have two options one is student the other is Teacher.If i click student a form will come.If i enter registrationNo which is primary Key then it has to search in database and get all records which matcches it.Iam able to do two tables at a time.How to access all the tables ,i mean it has to search all the tables and display records which i have to make crystal Report Again. Is it possible.Following Is my code Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim connetionString As String Dim connection As SqlConnection Dim command As SqlCommand Dim adapter As New SqlDataAdapter Dim ds As New DataSet Dim dv As DataView Dim sql As String connetionString = "Data Source=NASSQLEXPRESS;Database=data2;Integrated Security=True; Connect Timeout=30;" sql = " SELECT * FROM SAdmission Join Sfee ON SAdmission.RegistrationNo=Sfee.RegistrationNo " connection = New SqlConnection(connetionString) Try connection.Open() command = New SqlCommand(sql, connection) adapter.SelectCommand = command adapter.Fill(ds, "Sort DataView") adapter.Dispose() command.Dispose() connection.Close() dv = ds.Tables(0).DefaultView DataGridView1.DataSource = dv Catch ex As Exception MsgBox(ex.ToString) End Try End Sub I want to search all the tables in database all at a time which mathes the RegistrationNo,and display records.Plz help. Thanking u.
    255 pointsBadges:
    report
  • carlosdl
    Sorry Shaz, but the following part doesn't make any sense to me: "...If i click student a form will come.If i enter registrationNo which is primary Key then it has to search in database and get all records which matcches it.Iam able to do two tables at a time.How to access all the tables ,i mean it has to search all the tables..." Do you have student information in all of the tables of your database ? If so, why ? If not, why do you want to search in all tables ? As stated by Mrdenny, there is no way to query all tables automatically; you have to specify which tables you want to query. Maybe you just wanted to ask how to join more than 2 tables in a select statement... if so, here's an example:
    SELECT a.col1, b.col2, c.col3, d.col4
    FROM table_a a JOIN table_b b
    ON a.colx=b.colx
    JOIN table_c c
    ON a.colx=c.colx
    JOIN table_d d
    ON a.colx=d.colx
    WHERE a.coly = 'XXX'
    AND d.colz = 'YYY'
    69,465 pointsBadges:
    report
  • Shaz
    Thanks Mrdenny Carlosdl Your help is appreciated
    255 pointsBadges:
    report
  • Dmenke38
    Sometimes it helps to step back and review your data model. You have two tables about persons, where you could have one with a role attribute as Student or teacher. Then you could differentiate the selections as separate queries with different WHERE clauses. Ex: Student Selection query: SELECT Person.RegistrationNo, Person.Role FROM Person WHERE Person.RegistrationNo='" & TextBox1.Text & "'" AND Person.Role = "Student" Ex. Teacher Selection Query: SELECT Person.RegistrationNo, Person.Role FROM Person WHERE Person.RegistrationNo='" & TextBox1.Text & "'" AND Person.Role = "Teacher" Ex. Select any Person: SELECT Person.RegistrationNo, Person.Role FROM Person WHERE Person.RegistrationNo='" & TextBox1.Text & "'" Try looking up Data Normalization and start with at least 4th Normal Form in your data model.
    185 pointsBadges:
    report
  • carlosdl
    A valid point Dmenke38. However, you need to analyze if the information you need to store for Students is not too diferent from the one you have to store for teachers, and depending on that you can decide whether to use a single table for both. Additionally, in your example above, I wouldn't use "Teacher" or "Student" in the Person table, but a code representing those roles. Best regards,
    69,465 pointsBadges:
    report
  • Dmenke38
    True, Carlosdl, but a fully normalized model might start with just Person, Role, and other attributes in Person_Teacher and Person_Student tables for the differing data. This also allows for expansion of roles to other persons with attributes that also differ from Teacher and Student. I realize that the queries get more complicated in 4th Normal Form, but such a structure is also very flexible and extensible without touching the items already in place. I also realize that such analysis is more advanced than you would expect from most student exercise results, but the study of normalization seems to me to be a prerequisite to building the model of a data collection. Denormalized data requires more programming, is harder to extend or modify, and is often less efficient when volumes increase. Just my opinion based on hard won experience.
    185 pointsBadges:
    report
  • carlosdl
    Hi Dmenke38. I'm liking this. I hope you are doing too. :-) I see your point, but... could you please tell me which normal form would a design having one table for students and another for teachers (assuming that most of the fields would be different), having just one record for each person violate ? Cheers,
    69,465 pointsBadges:
    report
  • Shaz
    Hi Dmenke38, We cant give same table name person to both student and teaher,Database wont accept.
    255 pointsBadges:
    report
  • carlosdl
    Hi Shaz. What Dmenke38 suggested was using the same table for both students and teachers, not two tables with the same name.
    69,465 pointsBadges:
    report
  • Dmenke38
    Carlos, I will give an example data structure (logical) that will illustrate my remarks. Entities: Person - Person Id (assume automatically assigned serial identifier) - Name (may separate first, middle and last) - Birthdate - Gender Role - Role Id (automatically assigned number) - Role Name (Teacher, Student, ...) - Role Code (if needed to shorten a report column title, otherwise unnecessary) Teacher_Role - Person Id - Role Id (domain is Teacher) - School Employee Id - (Other unknown attributes directly relevant to Teacher's role ...) Student_Role - Person Id - Role Id - School Student Id (registrationNo?) - (Other unknown attributes directly relevant to Student's role ...) I am assuming that he was using a separate identifier series for Student and Teacher, otherwise there would be no good answer to using one Id field entry to search both Teacher and Student. except the original query you suggested. Based on Shaz's last remark, this is very advanced for the class exercise he is attempting. It just occurred to me that we don't know exactly what he wants to show on the report. He may be trying to report teacher_Student information (Teacher then Students registred in a class with that Teacher). Until we have that requirement in hand, this discussion may be entirely theoretical, but possibly irrelivant to his problem. For instance, we don't have a Teacher_Class or Enrollment_Class relationship defined. Those would be necessary to report Teacher_Enrolled Student data on a report.
    185 pointsBadges:
    report
  • carlosdl
    "...this discussion may be entirely theoretical, but possibly irrelevant to his problem" I agree. A final note: I didn't insinuate that your approach was wrong by any means, I just wanted to say that not every design improvement has necessarily something to do with normalization, or in other words, although some design might be normalized (to some degree), that doesn't mean it can't be improved. Best regards,
    69,465 pointsBadges:
    report
  • Dmenke38
    Carlos, Yes, normalization is not necessarily an improvement method, just a tool to clarify the data classes and relationships. The process is: 1. define the desired result 2. determine what actions and information is needed 3. structure the stored data concisely (normalization is a tool here) 4. determine the whole process sequence to achieve the result 5. determine the most opportune technology 6. design the application and how you will test it 7. execute the design... etc. However, many times the development goes as follows: "You guys start programming, and I'll go find out what they want." :-) I'll save more rambling comments for the next question. Bye.
    185 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following