How do I fill in my foreign keys?

Question

1.00/5 (1 vote)

See more:

'Cannot insert the value NULL into column 'GroepID', table 'C:\USERS\ALAINDEVRIJ\SOURCE\REPOS\KILLER APP FUN 12\BOWLINGSCORE\BIN\DEBUG\DATABASE1.MDF.dbo.Reservering'; column does not allow nulls. INSERT fails.
The statement has been terminated.'

i don't what value i should give because i want to insert this.

SqlConnection data = new SqlConnection(connectionstring);
           data.Open();
          SqlCommand cmd = new SqlCommand("INSERT INTO Reservering(Banen,Tijdsduur,Datum,GroepID) VALUES (@Tijdsduur,@Banen, @Datum,@GroepID)", data);
           cmd.Parameters.AddWithValue("@Tijdsduur", numtijd.Value);
           cmd.Parameters.AddWithValue("@Banen", numaantbn.Value);
           cmd.Parameters.AddWithValue("@Datum", SqlDbType.Date).Value= dateTimePicker1.Value.Date;
           cmd.Parameters.AddWithValue("@GroepID", )
           cmd.ExecuteScalar();

data.close();

What I have tried:

this is my table:
CREATE TABLE [dbo].[Reservering] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[GroepID] INT NOT NULL,
[Datum] DATE NOT NULL,
[Tijdsduur] INT NOT NULL,
[Banen] INT NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT ReserveringFK FOREIGN KEY ([GroepID]) REFERENCES [dbo].[Groep] ([Id])

Posted 19-Dec-17 9:23am

Member 13554627

Updated 19-Dec-17 10:09am

v2

Add a Solution

Comments

an0ther1 19-Dec-17 15:52pm

Where should you get the value for your Group Id?
If you are inserting a child record (EG: You are making an itinerary (Group table) & the Reserving table is a line item) you insert your parent record & retrieve the Record Id & then insert the child record.
Take a look at the following link; https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-identity-or-autonumber-values
If the Group Id is selected by the user (normally via a drop-down) then pass that value

Kind Regards

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RickZeeland · Answer 1 · 2017-12-19T10:01:00

In the line:

cmd.Parameters.AddWithValue("@GroepID", )

you must pass a value that exists in table Groep, if you don't know the value at the time of the insert you could pass a default value e.g. 0 that you know always exists in Groep.

Another way would be to (temporary) remove the foreign key constraint.

Dave Kreskowiak · Answer 2 · 2017-12-19T10:09:00

Well, you have to have the ID of the record of the related table,. I have no idea what those names you're using mean, so I'll have to go with the generic description.

You've got two tables with a 1 to Many relationship. A parent table and a child table, we'll call them Master and Detail. The Master table has a column in it, MasterId. This is the Primary Key for the Master table and holds a unique, auto-generated value for each record in the table. The rest of the columns don't matter.

The Detail table also has a column for it's Primary Key, called DetailId. Again, this is an auto-generated value that uniquely identifies each record in the table. Now, to make the relationship work where each group of records that belongs to a Master table record, a second column is needed. This is a Foreign Key column and holds the MasterId value of the record the Detail record belongs to. This column cannot be null because every Detail record needs to be associated to a Master record, no exceptions!

So, in this example, in order for this to work, you have to create a Master table record, get it's MasterId value back from the database, then use that value in the Foreign Key column when you insert the related records into the Detail table. You can't do an insert without it because the value cannot be null.

So, in your code, where is this GroepId supposed to be coming from?

How do I fill in my foreign keys?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0