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.

Attachment 158175

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.

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:

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.

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.

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?

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.

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.
Attachment 158179

Do i have to call that Queery on FORM2 Load

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

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

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

Can u explain a little how to call it,

I receive this error

System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@CustomerID".'

Quote:

---

Originally Posted by ali3nn

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.

Quote:

---

Originally Posted by ali3nn

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:

SELECT * FROM Visits WHERE CustomerID = ?

instead of my previous example.

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

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

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()

---

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.

Quote:

---

Originally Posted by ali3nn

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.

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!

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!

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.

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.

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.

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!