Wrox Programmer Forums
Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
| Search | Today's Posts | Mark Forums Read
C# Programming questions specific to the Microsoft C# language. See also the forum Beginning Visual C# to discuss that specific Wrox book and code.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 1st, 2004, 04:21 PM
Authorized User
 
Join Date: Aug 2004
Location: Amsterdam, NH, Netherlands.
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Alcapone
Default T-SQL Delete statement

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
 
Old November 1st, 2004, 08:13 PM
Authorized User
 
Join Date: Jul 2004
Location: Saint Peters, Missouri, USA.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old November 2nd, 2004, 04:25 AM
Authorized User
 
Join Date: Aug 2004
Location: Amsterdam, NH, Netherlands.
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Alcapone
Default

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?
 
Old November 3rd, 2004, 01:08 PM
Authorized User
 
Join Date: Jul 2004
Location: Saint Peters, Missouri, USA.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.




Similar Threads
Thread Thread Starter Forum Replies Last Post
convert a SQL Statement from MS Access to a SQL Corey Access 6 March 28th, 2007 12:33 PM
problem with delete statement thas123 SQL Server 2000 6 March 23rd, 2006 01:13 PM
Delete Statement Error Jane SQL Language 1 October 21st, 2005 01:45 PM
sql delete statement sand133 VB Databases Basics 2 July 7th, 2004 10:56 PM
DELETE statement with ASP.NET kaz VS.NET 2002/2003 1 December 9th, 2003 09:35 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.