Results 1 to 24 of 24

Thread: DATABASE relationship between tables

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    DATABASE relationship between tables

    Hello i created a database and in this database i have 2 tables - dbo.Customer and dbo.Visits, i created a foreignkey in dbo.Visits CustomerID. I want one Customer can have multiple visits, i created it but dont know how to show these records in datagridview for Customer1 and these multiple datas.

    Name:  svg1.jpg
Views: 373
Size:  20.1 KB

    How to show datagridview example like this

    Customer1 | Date of Visit | Treatment | Price
    ---------------------------------------------------
    ID1 | 1.1.2011 | treatment1| 12$
    ---------------------------------------------------
    ID1 | 2.1.2011 | treatment2| 26$
    ---------------------------------------------------
    ID1 | 1.7.2011 | treatment3| 22$
    ----------------------------------------------------

    ID1 is Customer1(Foreignkey)


    If anyone could help me it would be to nice.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DATABASE relationship between tables

    If what you're saying is that you want to display all the data from the Visits table for a specific customer then all you do is filter your query with an appropriate WHERE clause, e.g.
    sql Code:
    1. SELECT * FROM Visits WHERE CustomerID = @CustomerID
    If you're actually saying something else then you'll need to actually say it because it's not clear from your description or your images.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DATABASE relationship between tables

    By the way, you really ought to be consistent in your naming and not doing so is one of my pet peeves. Personally, I prefer singular names for database tables but lots of people prefer plurals. What you should never do is mix and match. Your tables should be name either Customer and Visit or Customers and Visits. Pick a naming convention and stick with it.

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    Yes that is what i mean i want to show all visits for a specific customer, and all i have to do is to add an query in that datagridview?

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DATABASE relationship between tables

    It's got nothing to do with the DataGridView. It just displays whatever is in its DataSource. Exactly what you need to do depends on exactly how you're retrieving the data from the database in the first place. If you're using a data adapter then simply add the WHERE clause to your SQL code. If you have a typed DataSet and are using a table adapter, you need to add a query to that table adapter. To do that, open your DataSet in the designer and right-click on the table adapter for the Visits table. You'll add the SQL code with the WHERE clause to the query and name the associated methods FillByCustomerID and GetDataByCustomerID. You can then call FillByCustomerID instead of Fill to get just the data for one CustomerID instead of all the data.

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    I clicked on DataSet1.xsd and then rightclick Add > Queery >named FillbyCustomerId> and then i have entered
    Code:
     SELECT * FROM Visits WHERE CustomerID = @CustomerID
    know what should i do here is a screenshot.
    Name:  1234548484.jpg
Views: 358
Size:  30.8 KB

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    Do i have to call that Queery on FORM2 Load

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    SORRY* Right click in the Visits table add > queery.
    now where to call it

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DATABASE relationship between tables

    What is that method for? Where do you want what it is for to happen? That's where you call it.

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    I want these datas to show on datagridview1

    Code:
            Dim con As New SqlConnection
    
            Dim sqlConnection1 As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True")
            Dim cmd As New SqlCommand
            Dim reader As SqlDataReader
    
            cmd.CommandText = "SELECT * FROM Visits WHERE CustomerID = @CustomerID"
            cmd.CommandType = CommandType.Text
            cmd.Connection = sqlConnection1
    
            DataGridView1.DataSource = sqlConnection1
    
            sqlConnection1.Open()
    
            reader = cmd.ExecuteReader()
            ' Data is accessible through the DataReader object here.
    
            sqlConnection1.Close()
    here is my code that i tryed but doesnt work

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    Can u explain a little how to call it,

    I receive this error

    System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@CustomerID".'
    Last edited by ali3nn; Apr 12th, 2018 at 10:24 PM.

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DATABASE relationship between tables

    Quote Originally Posted by ali3nn View Post
    I want these datas to show on datagridview1

    Code:
            Dim con As New SqlConnection
    
            Dim sqlConnection1 As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True")
            Dim cmd As New SqlCommand
            Dim reader As SqlDataReader
    
            cmd.CommandText = "SELECT * FROM Visits WHERE CustomerID = @CustomerID"
            cmd.CommandType = CommandType.Text
            cmd.Connection = sqlConnection1
    
            DataGridView1.DataSource = sqlConnection1
    
            sqlConnection1.Open()
    
            reader = cmd.ExecuteReader()
            ' Data is accessible through the DataReader object here.
    
            sqlConnection1.Close()
    here is my code that i tryed but doesnt work
    First things first, you need to make your mind up whether you're using a typed DataSet or not. If you are then use it, i.e. retrieve and save data only using table adapters that are part of that typed DataSet. If you want to create ADO.NET objects yourself, e.g. SqlConnection objects and the like, then don't use a typed DataSet.

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DATABASE relationship between tables

    Quote Originally Posted by ali3nn View Post
    Can u explain a little how to call it,

    I receive this error

    System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@CustomerID".'
    That's probably because you need to use a positional place-holder for parameters rather than a name, i.e. use:
    sql Code:
    1. SELECT * FROM Visits WHERE CustomerID = ?
    instead of my previous example.

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    Yes i use Dataset1, i have to add this code in the formLoad that is datagridview1 ? right

  15. #15

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    I have added ur Sql code to my project know i receive this
    " System.Data.SqlClient.SqlException: 'Incorrect syntax near '?'.' "

  16. #16

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    Code:
        Private Sub Form5_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'DataSet1.Visits' table. You can move, or remove it, as needed.
            Me.VisitsTableAdapter.Fill(Me.DataSet1.Visits)
            'TODO: This line of code loads data into the 'DataSet1.Customer' table. You can move, or remove it, as needed.
            Me.CustomerTableAdapter.Fill(Me.DataSet1.Customer)
            'TODO: This line of code loads data into the 'DataSet1.Visits' table. You can move, or remove it, as needed.
            Me.VisitsTableAdapter.Fill(Me.DataSet1.Visits)
    
    
    
            Dim con As New SqlConnection
    
            Dim sqlConnection1 As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True")
            Dim cmd As New SqlCommand
            Dim reader As SqlDataReader
    
            cmd.CommandText = "SELECT * FROM Visits WHERE CustomerID = ?"
            cmd.CommandType = CommandType.Text
            cmd.Connection = sqlConnection1
    
    
    
    
    
            sqlConnection1.Open()
    
            reader = cmd.ExecuteReader()
            ' Data is accessible through the DataReader object here.
    
            sqlConnection1.Close()

  17. #17
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DATABASE relationship between tables

    I suspect that you may have done something else wrong because I'd have expected one of those two to work. Please show the exact SQL you're using as well as the VB to execute it.

  18. #18
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DATABASE relationship between tables

    Quote Originally Posted by ali3nn View Post
    Code:
        Private Sub Form5_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'DataSet1.Visits' table. You can move, or remove it, as needed.
            Me.VisitsTableAdapter.Fill(Me.DataSet1.Visits)
            'TODO: This line of code loads data into the 'DataSet1.Customer' table. You can move, or remove it, as needed.
            Me.CustomerTableAdapter.Fill(Me.DataSet1.Customer)
            'TODO: This line of code loads data into the 'DataSet1.Visits' table. You can move, or remove it, as needed.
            Me.VisitsTableAdapter.Fill(Me.DataSet1.Visits)
    
    
    
            Dim con As New SqlConnection
    
            Dim sqlConnection1 As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True")
            Dim cmd As New SqlCommand
            Dim reader As SqlDataReader
    
            cmd.CommandText = "SELECT * FROM Visits WHERE CustomerID = ?"
            cmd.CommandType = CommandType.Text
            cmd.Connection = sqlConnection1
    
    
    
    
    
            sqlConnection1.Open()
    
            reader = cmd.ExecuteReader()
            ' Data is accessible through the DataReader object here.
    
            sqlConnection1.Close()
    OK, you've just ignored most of what I've told you to do here. Please pay attention. Use a typed DataSet only or not at all. If you are using a typed DataSet then DO NOT create your own ADO.NET objects. You are creating a SqlConnection there after I explicitly told you not to. What I specifically told you to do was, after adding that query, to call the FillByCustomerID method instead of the Fill method to get just Visits data for one Customer instead of all the data and you are still calling Fill. If you're not going to do as instructed then you're wasting my time and your own.

  19. #19

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    My database is Serviced-Database .mdf as u can see, i created a new form and add a datagridview so know can u tell me how to call these records can u explain a little more, how and where to execute!

  20. #20

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    Okay, what do u mean with " to call the FillByCustomerID method instead of the Fill method " and can u tell me how to call it and where step by step if u can please!

  21. #21
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DATABASE relationship between tables

    I'm afraid that I don't come here to teach people the fundamentals of programming. I expect that they have already learned the basic stuff for themselves. If I tell someone to call method X or set property Y then I expect that they just do it. If you don't know what such basic stuff means then you should learn it before tackling things like database access. There is a tutorial link in my signature that I suggest that you follow and work through. After that, you won't have to ask how to do things like call a method.

  22. #22

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    I know i just wanted u to show me the way to call that on datagridview1 bcuz i dont know, i have done exactly what u say, so how to call that database records for 1 customer multiple datas.

  23. #23
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DATABASE relationship between tables

    I know that you just wanted me to show you because you don't want to take the time to learn properly. I can show you this one thing and then you'll be back next time asking how to do something just as basic. If you take the time to learn the fundamentals then you won't have to ask those basic questions over and over. You shouldn't use forums as a substitute for effort. They are, in my opinion, for the stuff that you can't do for yourself, not the stuff that you can't be bothered to do. With the tutorial I directed you to and the other information already in this thread, you have all you need. It's now up to you whether you choose to use it or not. I've done all I'll be doing here.

  24. #24

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    72

    Re: DATABASE relationship between tables

    Yes im already learning it , watchin tutorials, but its a simple question i wouldnt ask if i know it , the forum is to help each other , so i have done all this and i cannot finish it without askin u, thank u ! SORRY 4 ASKING!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width