Hi,

I am trying to delete some records from my database with the use of this statement but i keep getting the message the the subquery return one or more records and that is not allowed in this query.

this is the statement

delete from deelname from deelname
inner join trainees on dbo.trainees.trainee_uno = dbo.deelname.trainee_uno inner join
dbo.course on dbo.deelname.course_uno = dbo.course.study_uno
where(dbo.deelname.trainee_uno =(select trainee_uno from trainees where lastname like '%aerts%'
and course_uno =(select Study_uno from course where name like '%test a%')))

when i check the code to see if there are any errors query analyzer shows that the code is correct.

Anybody know what i am doing wrong?

Regards,
Martijn

Your SQL command above is right but how is it represented in C# code? What is your SqlCommand method? Delete (also Update and Insert) commands in SQL return no data but do send an integer value for the number of rows impacted. You should use the "ExecuteNonQuery" method.

If you are, then I suspect you haven't framed the single quotes in the C# string correctly and a SELECT statement portion of your SQL command is returning a result set.

this is the code as it looks like in c#

string strConn = "server=localhost;uid=sa;pwd=sa;database=Studi e" ;
SqlConnection conn = new SqlConnection(strConn) ;
string strString = "delete from deelname from deelname" + " " +
"inner join trainees on dbo.trainees.trainee_uno = dbo.deelname.trainee_uno inner join" + " " +
"dbo.course on dbo.deelname.course_uno = dbo.course.study_uno" + " " +
"where(dbo.deelname.trainee_uno =(select trainee_uno from trainees where lastname like '%" +lstb_SelcUsrs.SelectedItem.ToString()+ "%' " + " " +
"and course_uno =(select Study_uno from course where name like '%"+ cmb_curs.SelectedItem.ToString()+"%')))" ;
SqlCommand myCmd = new SqlCommand(strString, conn) ;
conn.Open();
myCmd.ExecuteNonQuery();
conn.Close();

Correct?

Got it. Your SQL code looks well formed but has fatal logic in both subqueries. You are correctly returning one column but you must also insure only one row. The "WHERE" equal equation will not work with multiple values to compare. The "lastname" and "name" columns in your trainee and course tables, especially with the "LIKE" argument, can return multiple rows.