SQL Error using ASP

[dom24]

Hi,

I'm getting the following error in my asp code and i'm not sure how to fix it.

Microsoft OLE DB Provider for SQL Server error '80040e2f'

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Validation_Staff'. The conflict occurred in database 'TrainingEvaluation', table 'Staff', column 'StaffNo'.

/Evaluation/you-result.asp, line 29

The code I have at the moment is:

<%
Dim strSQL, objRS, strStaffNo, strCoursekey, strCourseDate, strtrainer, strQ1, strQ2, strQ3, strQ4, strQ5, strOther
strStaffNo = Request.QueryString("StaffNo")
strCoursekey = Request.QueryString("CourseKey")
strCourseDate = Request.QueryString("Trainer")
strTrainer = Request.QueryString("CourseDate")
strQ1 = Request.QueryString("Q1")
strQ2 = Request.QueryString("Q2")
strQ3 = Request.QueryString("Q3")
strQ4 = Request.QueryString("Q4")
strQ5 = Request.QueryString("Q5")
strOther = Request.QueryString("Other")

strSQL = "Insert into Validation (StaffNo, CourseKey, TrainerNo, CourseDate, ValidationQ1, ValidationQ2, ValidationQ3, ValidationQ3a, ValidationQ4, ValidationQ5) values ('" & strStaffNo & "'" &_
", '" & strCourseKey & "', '" & strTrainer & "', '" & strCourseDate & "', '" & strQ1 & "', '" & strQ2 & "'" &_
", '" & strQ3 & "', '" & strQ4 & "', '" & strQ5 & "', '" & strOther & "') "

Response.Write "Thank you for completing this course feedback form."

GetConnection
Set objRS = Conn.Execute(strSQL)

%>

I have a primary key field named StaffNo in the Staff table, and a foreign key inthe Validation table named StaffNo. I'm trying to insert data into the Validation table from a form with radio buttons being used for Validation 1, Validation 2, Validation3, etc.

Thanks.

 

[mrdenny]

Your problem is the StaffNo constraint. Be sure that the data you are trying to insert into the StaffNo Column in Validation is in the Staff table.

One thing that I find helpfull is to print out the value of the SQL Statement when debugging, a "Response.write strSQL" will work nicly. This way you can see exactly what is being sent to the SQL Server.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[gradley]

Something else you may want to consider (if it looks like the StaffNO is valid) is to perform a TRIM() on the field prior to inserting it.

This has happend to me in the past because the insert is trying to insert blank spaces after the field causing a FK violation.

 

[mrdenny]

Gradley is correct. I made the assumption that the StaffNo field is numeric. Which now that I look at your code it appears not to be. (You have single quotes around the value.) You are probally getting spaces, or some other character that sql doesn't like passed from the asp script.

The easiest way to see will be to write the statement to the screen to see what is happening.

Denny

--Anything is possible. All it takes is a little research. (Me)