Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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.
Reply With Quote
  #3 (permalink)  
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?
Reply With Quote
  #4 (permalink)  
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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 05:34 AM.


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