-
Apr 12th, 2018, 06:28 PM
#1
Thread Starter
Lively Member
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.
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.
-
Apr 12th, 2018, 06:52 PM
#2
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:
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.
-
Apr 12th, 2018, 06:55 PM
#3
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.
-
Apr 12th, 2018, 07:05 PM
#4
Thread Starter
Lively Member
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?
-
Apr 12th, 2018, 07:25 PM
#5
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.
-
Apr 12th, 2018, 07:36 PM
#6
Thread Starter
Lively Member
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.
-
Apr 12th, 2018, 07:58 PM
#7
Thread Starter
Lively Member
Re: DATABASE relationship between tables
Do i have to call that Queery on FORM2 Load
-
Apr 12th, 2018, 08:02 PM
#8
Thread Starter
Lively Member
Re: DATABASE relationship between tables
SORRY* Right click in the Visits table add > queery.
now where to call it
-
Apr 12th, 2018, 09:37 PM
#9
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.
-
Apr 12th, 2018, 09:42 PM
#10
Thread Starter
Lively Member
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
-
Apr 12th, 2018, 09:43 PM
#11
Thread Starter
Lively Member
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.
-
Apr 12th, 2018, 10:43 PM
#12
Re: DATABASE relationship between tables
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.
-
Apr 12th, 2018, 10:44 PM
#13
Re: DATABASE relationship between tables
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.
-
Apr 12th, 2018, 10:58 PM
#14
Thread Starter
Lively Member
Re: DATABASE relationship between tables
Yes i use Dataset1, i have to add this code in the formLoad that is datagridview1 ? right
-
Apr 12th, 2018, 11:03 PM
#15
Thread Starter
Lively Member
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 '?'.' "
-
Apr 12th, 2018, 11:05 PM
#16
Thread Starter
Lively Member
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()
-
Apr 12th, 2018, 11:05 PM
#17
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.
-
Apr 12th, 2018, 11:08 PM
#18
Re: DATABASE relationship between tables
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.
-
Apr 12th, 2018, 11:09 PM
#19
Thread Starter
Lively Member
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!
-
Apr 12th, 2018, 11:15 PM
#20
Thread Starter
Lively Member
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!
-
Apr 12th, 2018, 11:56 PM
#21
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.
-
Apr 13th, 2018, 06:25 AM
#22
Thread Starter
Lively Member
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.
-
Apr 13th, 2018, 09:08 AM
#23
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.
-
Apr 13th, 2018, 10:08 AM
#24
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|