C# SQL Server database error

Question

I got the following error for my code:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: The UPDATE statement conflicted with the FOREIGN KEY constraint

"FK_TutorID". The conflict occurred in database "NPTC", table "dbo.Tutor", column 'TutorID'.

This is my code:

// Instantiate a SqlConnection object with the COnnection String read.
SqlConnection conn = new SqlConnection(strConn.ToString());

// Instantiate a sqlcommand object, provide a update sql statement to add
// class to tutor record specified by a classID
SqlCommand cmd = new SqlCommand("
    UPDATE TuitionClass 
    SET TutorID = @tutorID 
    FROM TuitionClass 
    INNER JOIN Tutor ON TuitionClass.tutorID = Tutor.tutorID 
                     AND TuitionClassID = @selectedTuitionClassID", conn);

// Define the parameter used in SQL statement, value for the parameter is retrieved 
// from class's property
cmd.Parameters.AddWithValue("@tutorID", TutorID);
cmd.Parameters.AddWithValue("@selectedTuitionClassID", tuitionClassID);

// open a Database connection
conn.Open();

// Execute the SQL statement
int count = cmd.ExecuteNonQuery();

// Close database connection

I'm not sure where it has gone wrong because I think my code is correct?

Code may be correct but the error message is telling you exactly what's wrong. You need to check out the foreign key relationship you have on that table. — Bryan Mudge
– Bryan Mudge, Commented Aug 3, 2015 at 15:13
The conflict occurred in database "NPTC", table "dbo.Tutor", column 'TutorID'. this is your issue. You're (probably) attempting to update the TutorID to a value that does not exist from the originating table — Kritner
– Kritner, Commented Aug 3, 2015 at 15:15
Why are you doing a JOIN in your UPDATE statement? I don't see that it serves any purpose. — Tab Alleman
– Tab Alleman, Commented Aug 3, 2015 at 15:15
Looks like you're trying to set the TutorID on a TuitionClass row to a value that isn't found in the Tutor table. Hence the foreign key constraint error. — maniak1982
– maniak1982, Commented Aug 3, 2015 at 15:15
You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results... — marc_s
– marc_s, Commented Aug 3, 2015 at 16:17

Robert Fricke · Accepted Answer · 2015-08-03 15:16:08Z

4

You passed an invalid parameterer here:

cmd.Parameters.AddWithValue("@tutorID", TutorID);

The TutorID does not match a Tutor in the database.

answered Aug 3, 2015 at 15:16

Robert Fricke

3,6531 gold badge23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# SQL Server database error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related