I have a piece of code that uses the db-library with sql server 2000/2005 and runs the following delete statement:
DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some Val2' ) AND Col3 IN( integer1 ) AND Col4 IN( integer2 ) AND Col5 IN( 'Some Val3' )
on TABLE1, uploads data into TABLE1 through bulk loading, calls a stored procedure that uses the data, and then deletes the data through the SAME delete statement with EXACTLY the same parameter values. The first delete statement is always successful, but the second statement intermittently gives the following error:
0,0,MS SQL Server Message :
SQL Server message 512, state 1, severity 16:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SQL Server message 3621, state 0, severity 0:
The statement has been terminated.
Note: I was initially using the equality operator instead of the IN operator in the query but that gave the same results.
The table has the schema
Table1( Col1 varchar(50) NULL, Col2 varchar(50) NULL, Col3 int NULL, Col4 int NULL, Col5 varchar(128) NULL, Col6 varchar(128) NULL, Col7 varchar(100) NULL, Col8 varchar(50) NULL, Col9 varchar(50) NULL, Col10 int NULL, Col11 int NULL, Col12 float NULL, Col13 float NULL, Col14 float NULL, Col15 float NULL )
Can somebody tell me whats going wrong here? I can easily ignore this error because my work is done after the stored proc but I fear amassing a lot of useless data in the table over time. Also http://support.microso ft.com/kb/195491 talks about a case where the delete statement is actually successful but still causes an error when using ADO. I vaguely remember hearing somewhere that delete causes a lot of problems if the table doesn't have primary keys. Is this correct?
1  3936   ck9663 2,878
Recognized Expert Specialist
I have a piece of code that uses the db-library with sql server 2000/2005 and runs the following delete statement:
DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some Val2' ) AND Col3 IN( integer1 ) AND Col4 IN( integer2 ) AND Col5 IN( 'Some Val3' )
on TABLE1, uploads data into TABLE1 through bulk loading, calls a stored procedure that uses the data, and then deletes the data through the SAME delete statement with EXACTLY the same parameter values. The first delete statement is always successful, but the second statement intermittently gives the following error:
0,0,MS SQL Server Message :
SQL Server message 512, state 1, severity 16:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SQL Server message 3621, state 0, severity 0:
The statement has been terminated.
Note: I was initially using the equality operator instead of the IN operator in the query but that gave the same results.
The table has the schema
Table1( Col1 varchar(50) NULL, Col2 varchar(50) NULL, Col3 int NULL, Col4 int NULL, Col5 varchar(128) NULL, Col6 varchar(128) NULL, Col7 varchar(100) NULL, Col8 varchar(50) NULL, Col9 varchar(50) NULL, Col10 int NULL, Col11 int NULL, Col12 float NULL, Col13 float NULL, Col14 float NULL, Col15 float NULL )
Can somebody tell me whats going wrong here? I can easily ignore this error because my work is done after the stored proc but I fear amassing a lot of useless data in the table over time. Also http://support.microso ft.com/kb/195491 talks about a case where the delete statement is actually successful but still causes an error when using ADO. I vaguely remember hearing somewhere that delete causes a lot of problems if the table doesn't have primary keys. Is this correct?
That error happens when you're running a query that is expected to return a single result but returned two or more. It's usually when you use the result of a query and store in a variable or use it in an expression.One of the usual reason is there is a duplicate on the table. Since your DELETE query does not contain any subquery, I can only assume your error is somewhere else on your stored proc and not on your DAO.
-- CK
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: xuatla |
last post by:
Hi,
I encountered the following compile error of c++ and hope to get your help.
test2.cpp: In member function `CTest CTest::operator+=(CTest&)':
test2.cpp:79: error: no match for 'operator=' in '*this =
CTest::operator+(CTest&)((+t2))'
test2.cpp:49: error: candidates are: CTest CTest::operator=(CTest&)
make: *** Error 1
|
by: cousaert |
last post by:
Newsgroups: alt.comp.lang.learn.c-c++
Date: Fri, 27 Aug 2004 12:36:11 +0200
Lines: 36
User-Agent: KNode/0.7.6
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
I get the error "jump to case label" when I try to compile some code with
this in:
|
by: Peter Frost |
last post by:
Please help
I don't know if this is possible but what I would really like to do is
to use On Error Goto to capture the code that is being executed when
an error occurs.
Any help would be much appreciated.
Thanks in advance
|
by: Polly |
last post by:
I had a macro that ran a parameter query and created and opened an
Excel file with the system date as part of the file name, but I had to
change the file name by hand. So I converted the macro to code using
tools-->references.
The converted macro included the following statement:
DoCmd.OutputTo acQuery, "qselLabelsBloodLog_output",
"MicrosoftExcelBiff8(*.xls)", "S:\susan_cheree\lb041129.xls", True,
"", 0
|
by: Squirrel |
last post by:
I have a command button on a subform to delete a record.
The only statement in the subroutine is:
DoCmd.RunCommand acCmdDeleteRecord
The subform's recordsource is "select * from tblVisit order by VisitDt"
I'm getting this error message:
Errno is 2465. Err.description is "Can't find field '|' referred to in your
expression"
| | |
by: Nathan Bloomfield |
last post by:
Hi there,
I am having difficulty with a piece of code which would work wonders
for my application if only the error trapping worked properly.
Basically, it works as follows:
- adds records from rsSource into rsDest
- if it finds a key violation then it deletes the current record from
rsDest and adds the new record from rsSource. This works perfectly -
but only for the first found duplicate record, it brings up the error
|
by: Akinyemi |
last post by:
I created a Database which I named "Address".
I went through the Control Panel and created a DSN to enable me connect to the Database through ODBC.
I then created a Form with the same fields as that of the Database. I wrote the necessary codes and ran the Program. Below are my codes and the runtime error I received:
Run-Time Error -2147217904(80040e10):
Too few parameters.
Expected 1
|
by: prakashwadhwani |
last post by:
The Delete Event/Proc & "Save_Fields_In_Form_Header" Event/Proc in
my form were working perfectly.
However, after I added a call to the "Save_Fields_In_Form_Header"
Event/Proc in the "Current" Event of my form, and I now try to Delete
a record, I get ... Run-Time Error 3218 - Could not Update; Currently
Locked. My Access application then effectively freezes forcing me to
shut Access down & re-start.
The record DOES get deleted...
|
by: EManning |
last post by:
Using A2003. I'm receiving this error when returning from a
"DoCmd.OpenReport..." statement. I have a tab control with a subform
on every tab. The user selects an item from a combobox at the top of
the main form which refreshes all the subforms. If this DoCmd
statement is not executed, then I don't receive the error. I tried a
"DoCmd.OpenQuery..." instead of a report but get the same error.
I want the user to be able to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
