Click here to Skip to main content
15,896,348 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i want to give a score to every aliasnaam i fill in .
C#
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 ([ScoreID]) REFERENCES [dbo].[Score] ([Id])
);


CREATE TABLE [dbo].[Score] (
    [Id]             INT IDENTITY (1, 1) NOT NULL,
    [Aantal_punten]  INT NULL,
    [Aantal_strikes] INT NULL,
    [Aantal_spares]  INT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


What I have tried:

i have tried some querys but it didnt help
C#
 public List<persoon> spelers()
        {

            SqlConnection data = new SqlConnection(Connectionstring);
            data.Open();
            string query = "Select Aliasnaam FROM Persoon INNER JOIN Score ON Score.Id = ScoreID";
            SqlCommand cmd = new SqlCommand(query, data);
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {

                    persoon speler = new persoon();

                    // Lees de gegevens van de student in.
                    speler.Voegtoe(reader.GetString(0));


                    // En voeg deze toe aan de lokale lijst.
                    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())
                    {

                       

                        // Lees de gegevens van de student in.
                        punten.voegtoeaantpnt(reader.GetInt32(0));
                        punten.voegtoeaantstr(reader.GetInt32(1));
                        punten.voegtoeaantsprs(reader.GetInt32(2));

                        // En voeg deze toe aan de lokale lijst.
                        Punten.Add(punten);

                    }
            }
            data.Close();
            return Punten;
pre>
Posted
Updated 3-Jan-18 13:39pm
v3

1 solution

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();
//Include
         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();

                    // Lees de gegevens van de student in.
                    speler.Voegtoe(reader.GetString(0));


                    // En voeg deze toe aan de lokale lijst.
                    Spelers.Add(speler);

                }

            }
            data.Close();
            return Spelers;


public List<score> punten()
        {
            SqlConnection data = new SqlConnection(Connectionstring);
            data.Open();
     //Include where in the last part for filtering. For Example The person ScoreID is equal to 01 then in the table Score you will only get the ScoreID equal to 01
// e.i. string query = "Select Aantal_punten,Aantal_strikes,Aantal_spares FROM Score where Score = "+01+"" ;

       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())
                    {
                        // Lees de gegevens van de student in.
                        punten.voegtoeaantpnt(reader.GetInt32(0));
                        punten.voegtoeaantstr(reader.GetInt32(1));
                        punten.voegtoeaantsprs(reader.GetInt32(2));

                        // En voeg deze toe aan de lokale lijst.
                        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.


C#
//ScoreID must get from table persoon.
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)"
//DO the SQL CONNECTION AND SQL COMMAND 

//the @ value will accept the values single quote special characters for secure insertion.
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!!.
 
Share this answer
 
v2
Comments
Member 13554627 4-Jan-18 6:03am    
thank you very much for commenting, im trying to do what you said. but i can't change the tables, i get an update error.
Samuel Corpuz 4-Jan-18 21:10pm    
drop and create the table

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900