CREATE TABLE [dbo].[Persoon] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Aliasnaam] NCHAR (10) NOT NULL,
[ScoreID] INT NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_Persoon_ToTable] FOREIGN KEY [dbo].[Persoon].([ScoreID]) REFERENCES [dbo].[Score].([ScoreID])
);
CREATE TABLE [dbo].[Score] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[ScoreID] INT NULL,
[Aantal_punten] INT NULL,
[Aantal_strikes] INT NULL,
[Aantal_spares] INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
Foreign key name must ALWAYS the same.
In your Table Score Column [Id] is a primary key. It is different from foreign key.
Just include another Column name [ScoreID] for Unique key constraint in Score Table.
public List<persoon> spelers()
{
SqlConnection data = new SqlConnection(Connectionstring);
data.Open();
string query = "Select P.Aliasnaam,P.ScoreID FROM Persoon as 'P' INNER JOIN Score as 'S' ON S.ScoreID = P.ScoreID where P.ScoreID = "+01+"";
SqlCommand cmd = new SqlCommand(query, data);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
persoon speler = new persoon();
speler.Voegtoe(reader.GetString(0));
Spelers.Add(speler);
}
}
data.Close();
return Spelers;
public List<score> punten()
{
SqlConnection data = new SqlConnection(Connectionstring);
data.Open();
string query = "Select Aantal_punten,Aantal_strikes,Aantal_spares FROM Score" ;
SqlCommand cmd = new SqlCommand(query, data);
score punten = new score();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
punten.voegtoeaantpnt(reader.GetInt32(0));
punten.voegtoeaantstr(reader.GetInt32(1));
punten.voegtoeaantsprs(reader.GetInt32(2));
Punten.Add(punten);
}
}
data.Close();
return Punten;
pre>
Use the Insert query if you want to insert score board in every Person.
Insert Query for Score table must be.
public void InsertValues(int ScoreID)
{
string query="INSERT INTO Score (id,ScoreId,Aantal_punten,Aantal_strikes,Aantal_spares) VALUES (@id,@ScoreID, @Aantal_punten,@Aantal_strikes,@Aantal_spares)"
command.Parameters.AddWithValue("@id","abc")
command.Parameters.AddWithValue("@ScoreID","abc")
command.Parameters.AddWithValue("@Aantal_punten","abc")
command.Parameters.AddWithValue("@Aantal_strikes","abc")
command.Parameters.AddWithValue("@Aantal_spares","abc")
command.ExecuteNonQuery();
}
The rest is up to you Good Luck!!.