Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







How Do I Delete That Row?


Hey guys, I'm new to all this. All I want to do is to delete that first row. How do I do that?

Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("dbase1").ConnectionString)

Dim sqlstring As String = "SELECT * FROM [Itmes] WHERE ([ID] = @ID)"

Dim adapter As New SqlDataAdapter(sqlstring, conn)

Dim ds As New DataSet

Dim rows As DataRowCollection

adapter.SelectCommand.Parameters.Add("@ID", SqlDbType.Int).Value = "29

rows = ds.Tables(0).Rows

rows(0).delete()

First, is this an efficient way to find that rows or should I do this in a completely different way? And to the question, how do I update the database and delete that rows(0)?

If this isn't a good way, how should I do it differently?


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Copy And Delete Table With &"Foreign Key References(...,...) On Delete Cascade?
Hello:
Need some serious help with this one...

Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...

Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.

The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.

This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.

So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?

The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.

Thanks!
Sky

View Replies !   View Related
Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another
I'm trying to clean up a database design and I'm in asituation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks



View Replies !   View Related
SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!
I am having great difficulty with cascading deletes, delete triggers and referential integrity.

The database is in First Normal Form.

I have some tables that are child tables with two foreign keyes to two different parent tables, for example:

Table A
/
TableB Table C
/
Table D

So if I try to turn on cascading deletes for A/B, A/C, B/D and C/Drelationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.

SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.

Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table Cwhen deleting a row intable A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.

When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????

This is an example of my delete trigger:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete fromB where MeetingID = ID;
Delete fromC where MeetingID = ID;

And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.

So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.

So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).

Hope this makes sense...
Thanks,
Josh




View Replies !   View Related
How To Run Delete Query / Delete Several Rows Just By One Click ?
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question) 

View Replies !   View Related
I Use SQL 2000, Can You Use One Delete Query To Delete 2 Tables?
this is my Delete Query NO 1
alter table ZT_Master disable trigger All
Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
alter table ZT_Master enable trigger All
 
I have troble in Delete Query No 2
here is a select statemnt , I need to delete them
select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey)  And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
I tried modified it as below
delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey)  And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
but this doesn't works..
 
can you please help?
and can I combine these 2 SQL Query into one Sql Query? thank you

View Replies !   View Related
Delete Doesn't Delete Rows, But @@ROWCOUNT Says It Did
I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?

View Replies !   View Related
Cannot Amend Or Delete Subscription And Cannot Delete Report.


Hi,

I have a problem with one report on my server. A user has requested that I exclude him from receiving a timed email subscription to several reports. I was able to amend all the subscriptions except one. When I try to remove his email address from the subscriptionI receive this error:

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help







For more information about this error navigate to the report server on the local server machine, or enable remote errors


Online no help couldn't offer any advice at all, so I thought I'd just delete the subscription and recreate it again, but I receive the same message. "Okay, no problem, I'll just delete the report and redeploy it and set up the subscription so all the other users aren't affected", says I. "Oh, no!", says the report server, and then it give me this message:





System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---



What's even weirder is that I'm the owner and creator of the report andI'm a system admin and content manager on the report server and I set up the subscription when the report was initially deployed. Surely I should have sufficient rights tofart around with this subscription/report as I see fit?

I have rebooted the server, redeployed the report, checked credentials on the data source and tried amending and deleting from both the report manager and management studio but still I am prevented from doing so.

Any help would be much appreciated.

Thanks in advance,

Paul

View Replies !   View Related
Delete From Vs Delete With Subquery In Transaction
First, this is not my code.

This one is weird and I am missing something fundamental on this one. A developer was getting a timeout with this...


CREATE PROCEDURE p_CM_DeleteBatch
(
@SubmitterTranID VARCHAR(50)
)
AS
DECLARE
@COUNT INT,
@COMMIT INT

SET @COUNT = 0
SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1

select @COUNT = COUNT(*) from claimsreceived
where (claimstatus NOT IN ('Keyed', 'Imported')) AND
SubmitterTranID = @SubmitterTranID

IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete
BEGIN
BEGIN TRANSACTION
DELETE FROM INVOICECLAIMMAPPING WHERE CLMRECDID IN (SELECT DISTINCT CLMRECDID FROM CLAIMSRECEIVED WHERE SUBMITTERTRANID = @SUBMITTERTRANID)
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPayment WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsreceivedPayorServices where ClmRecdPyID in (SELECT ClmRecdPyID
FROM ClaimsReceivedPayors
WHERE SubmitterTranID = @SubmitterTranID)

IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceivedPayors WHERE ClmRecdid in (SELECT ClmRecdID
FROM ClaimsReceived
WHERE SubmitterTranID = @SubmitterTranID)
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

IF (@COMMIT = 1)
BEGIN
--ROLLBACK TRANSACTION --For Testing Purpose ONLY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN (-1)
END
END
ELSE
BEGIN
RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1)
END
GO


I applied a couple of indices and got ride of the uncorrelated subqueries


CREATE PROCEDURE p_CM_DeleteBatch
(
@SubmitterTranID VARCHAR(50)
)
AS
DECLARE
@COUNT INT,
@COMMIT INT

SET @COUNT = 0
SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1

select @COUNT = COUNT(*) from claimsreceived
where (claimstatus NOT IN ('Keyed', 'Imported')) AND
SubmitterTranID = @SubmitterTranID

IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete
BEGIN
BEGIN TRANSACTION

DELETE INVOICECLAIMMAPPING
FROM INVOICECLAIMMAPPING
JOIN CLAIMSRECEIVED
ON INVOICECLAIMMAPPING.CLMRECDID = CLAIMSRECEIVED.CLMRECDID
WHERE CLAIMSRECEIVED.SUBMITTERTRANID = @SUBMITTERTRANID

IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPayment
WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE ClaimsreceivedPayorServices
FROM ClaimsreceivedPayorServices
JOIN ClaimsReceivedPayors
ON ClaimsreceivedPayorServices.ClmRecdPyID = ClaimsReceivedPayors.ClmRecPyID
WHERE ClaimsReceivedPayors.SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE ClaimsReceivedPayors
FROM ClaimsReceivedPayors
JOIN ClaimsReceived
ON ClaimsReceivedPayors.ClmRecdid = ClaimsReceived.ClmRecdid
WHERE ClaimsReceived.SubmitterTranID = @SubmitterTranID

IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

IF (@COMMIT = 1)
BEGIN
--ROLLBACK TRANSACTION --For Testing Purpose ONLY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN (-1)
END
END
ELSE
BEGIN
RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON

GO


Suddenly this constraint was being violated with the change


ALTER TABLE [dbo].[ClaimsReceivedPayorServices] ADD CONSTRAINT [FK_ClaimsReceivedPayorServices_CLAIMSRECEIVEDPAYOR S] FOREIGN KEY
(
[ClmRecdPyID]
) REFERENCES [CLAIMSRECEIVEDPAYORS] (
[CLMRECPYID]
)


Is the delete on ClaimsReceivedPayors starting before the delete on ClaimsreceivedPayorServices finishes? If so why would it matter between the join and subquery? This one is making me depressed because I can not explain it.

View Replies !   View Related
To Delete Or Not To Delete - DESIGN ISSUE!
Hi all

Need some advice solving a little problem I have with my database!

Current setup:

I have a person table that is made up of 39 columns. I also allow for person records to be deleted but I do this by having another table I call LogicallyDeletedrecords. This table is made up of the PersonId, Reason for deletion/suppression and a date time stamp. To access Live records I created a view based on my Person table which contains a WHERE clause to exclude records that exist in the LogicallyDeletedrecords. Similarly, I have another view DeadPersonData which contains Person records that have been removed. Hope it all makes sense so far! Now on to my worries!

The problem:

My Person table contains 9+ million records. The LogicallyDeletedrecords table has 500k+ but I anticipate further growth over the coming weeks/months. My worry is that my LivePersonData view will be too slow to access as my LogicallyDeletedrecords table grows. What’s more, as part of my Load routine, I have to make sure that Person data loaded on to the system is excluded if that same person exists as a deleted member. Both of these actions could slow down my system as the deleted table grows.

My thoughts:

I’ve been thinking of physically deleting dead Person records from my person table (possibly creating an archive table to hold them). But then if I delete them how do I cross check the details when new Person details get loaded?! As I said, my current LogicallyDeletedrecords table holds the PersonId, ReasonDeleted and CreationStamp. The only way is to add further columns which I use to match Person Details?

Any design suggestions would be welcome!

View Replies !   View Related
One DELETE Sql Statement To Delete From Two Tables
I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?

View Replies !   View Related
Soft Delete In Table, Why Merge Agent Report Hards Delete On Table ?
Hi seniors

there are two tables involve in replication let say table1 and replicated table is also rep.table1.

we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1so download and report hard delete operation and delete the record in replicated table which is very crucial.

plz let me know where am i wrong and how i put it into right way.

there is no triggers on published tables and noother trigger is created on published table.

regards

Ahmad Drshen

View Replies !   View Related
VS2005 - Using Detailsview To Update, Insert And Delete Rows From SQL 2005 Database. Delete And Insert Work But Update Does Not - No Errors Returned
  Using VS 2005 DetailsView to insert, delete, and update rows in SQL 2005 database.  insert and delete work but update does not.  I recieve no errors and the detailsView comes back unchanged (as well as table row is unchanged).   I am trying to use as little code behind as possible. However I do have ItemUpdating routines that seem to work (i.e Checking table for new login duplicates and encrypting passwords).  The following is the source code generated by VS2005:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="frmDbRegionMgrNew.aspx.vb" Inherits="frmDbRegionMgrNew" Title="Region Manager DB Update" Theme="detailsVeiwTheme" %><%@ Import Namespace="System.Data" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"><title>Untitled Page</title> </head><body><form id="form1" runat="server"><div><asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Style="z-index: 101; left: 14px; position: absolute; top: 10px" Width="199px">Data Base Maintenance </asp:Label><asp:HyperLink ID="HyperLink1" runat="server" BackColor="ActiveBorder" BorderColor="ActiveBorder"BorderStyle="Outset" Font-Bold="True" Font-Size="X-Small" ForeColor="#004000"Height="31px" NavigateUrl="DataBaseMaint.aspx" Style="z-index: 133; left: 524px;position: absolute; top: 7px" Width="96px">DB Main Menu</asp:HyperLink><br /><br /><br /><table style="width: 654px"><tr><td style="width: 120px"><asp:Label ID="Label2" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Text="Regional Manager" Width="189px"></asp:Label></td><td style="width: 100px"></td><td style="width: 203px"></td></tr><tr><td style="width: 120px" valign="top"><asp:Label ID="Label3" runat="server" Font-Bold="True" Font-Size="Small" ForeColor="#004000"Style="z-index: 128; left: 2px; position: absolute; top: 115px" Width="128px">Select Greenhouse -></asp:Label></td><td style="width: 100px" valign="top"><asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"DataTextField="Name" DataValueField="GrnHseID"></asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>"SelectCommand="SELECT [GrnHseID], [Name] FROM [Greenhouse]"></asp:SqlDataSource></td><td style="width: 203px">&nbsp;<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"DataKeyNames="RegionMgrID" DataSourceID="SqlDataSource2" Height="50px" Width="125px"><Fields><asp:TemplateField HeaderText="RegionMgrID" InsertVisible="False" SortExpression="RegionMgrID"><EditItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Eval("RegionMgrID") %>'></asp:Label></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("RegionMgrID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="GrnHseID" SortExpression="GrnHseID"><EditItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Eval("GrnHseID") %>'></asp:Label></EditItemTemplate><InsertItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:TextBox></InsertItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundField DataField="DispLvl" HeaderText="DispLvl" SortExpression="DispLvl" /><asp:BoundField DataField="FName" HeaderText="FName" SortExpression="FName" /><asp:BoundField DataField="Minit" HeaderText="Minit" SortExpression="Minit" /><asp:BoundField DataField="LName" HeaderText="LName" SortExpression="LName" /><asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /><asp:BoundField DataField="Ext" HeaderText="Ext" SortExpression="Ext" /><asp:BoundField DataField="Cell" HeaderText="Cell" SortExpression="Cell" /><asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" /><asp:BoundField DataField="Login" HeaderText="Login" SortExpression="Login" /><asp:BoundField DataField="Password" HeaderText="Password" SortExpression="Password" /><asp:BoundField DataField="AccessLvl" HeaderText="AccessLvl" SortExpression="AccessLvl" /><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /></Fields></asp:DetailsView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>" DeleteCommand="DELETE FROM [RegionMgr] WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"InsertCommand="INSERT INTO [RegionMgr] ([GrnHseID], [DispLvl], [FName], [Minit], [LName], [Phone], [Ext], [Cell], , [Login], [Password], [AccessLvl]) VALUES (@GrnHseID, @DispLvl, @FName, @Minit, @LName, @Phone, @Ext, @Cell, @Email, @Login, @Password, @AccessLvl)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [RegionMgr] WHERE ([GrnHseID] = @GrnHseID)"UpdateCommand="UPDATE [RegionMgr] SET [GrnHseID] = @GrnHseID, [DispLvl] = @DispLvl, [FName] = @FName, [Minit] = @Minit, [LName] = @LName, [Phone] = @Phone, [Ext] = @Ext, [Cell] = @Cell, = @Email, [Login] = @Login, [Password] = @Password, [AccessLvl] = @AccessLvl WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"><DeleteParameters><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></DeleteParameters><UpdateParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></UpdateParameters><SelectParameters><asp:ControlParameter ControlID="DropDownList1" Name="GrnHseID" PropertyName="SelectedValue"Type="Int32" /></SelectParameters><InsertParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /></InsertParameters></asp:SqlDataSource>&nbsp; &nbsp; &nbsp;&nbsp;</td></tr><tr><td style="width: 120px"></td><td style="width: 100px"></td><td style="width: 203px"></td></tr></table></div></form></body></html>Appreciate any and all help!Dave

View Replies !   View Related
Delete
Hi Guys,
I wrote sqlquery for select users for a role.its working fine now i am trying to delete users using same query.I ma getting error. here is my delete query
Select u.name from sysusers u, sysusers g, sysmembers m where g.name = @rolename and g.uid = m.groupuid and g.issqlrole = 1 and u.uid = m.memberuid   -->its working fine
Delete u.name from sysusers u, sysusers g, sysmembers m where g.name = @rolename and g.uid = m.groupuid and g.issqlrole = 1 and u.uid = m.memberuid ---> Getting error
Here rolename is database rolename.
my question is, Is this correct way to delete roleusers and role at a time? if not can any one help to do?
 
 

View Replies !   View Related
Delete
Hello,I have 3 tables:  [Article] > ArticleId, ...  [Category] > CategoryId, ...  [CategoriesInArticles] > ArticleId, CategoryIdGiven an @ArticleId, I need to:  1. Delete the record in [Article] with the given @ArticleId  2. Delete all records in [CategoriesInArticles] with the given @ArticleId  3. Delete the records in [Categories] which CategoryId was deleted in (2) BUT only if the CategoryId      is not associated with other articles in [CategoriesInArticles].How can I do this?Thanks,Miguel

View Replies !   View Related
30 Day Old Delete
I have a database of posts which i want to delete after 30days of being on the site, my code so far gets all the data out of the table and then if their is more than 0 rows it loops within the tblTable and finds all the posts made within the last 30days and deletes them, my problem is how do I get it to work out 30days ago, for 30days time, its just " DateTime.Now.AddDays(30);" but i cant seem to do something as simple for back in time, heres my code :     private void DeleteOldStuff()    {        string strSQL = "SELECT * FROM ForumPosts";        SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BlinkConnectionString"].ConnectionString);        Connection.Open();        SqlCommand comm = new SqlCommand(strSQL, Connection);        SqlDataAdapter da = new SqlDataAdapter(comm);        tblData = new DataTable();        da.Fill(tblData);        Connection.Close();        if (tblData.Rows.Count > 0)        {            for (int i = 0; i < tblData.Rows.Count; i++)            {                DataRow dr = tblData.Rows[i];                if ("30DAYSAGO" <= dr["DateCreated"])                {                    Connection.Open();                    SqlCommand Delete = new SqlCommand("DELETE FROM ForumPosts WHERE PostID = '" + dr["PostID"].ToString() +"'", Connection);                    Delete.ExecuteNonQuery();                    Connection.Close();                }            }        }    }Any help / advice is appriciated! Thanks John 

View Replies !   View Related
SQL Delete
Hello, I just cannot think of the answer to this this morning and would appreciate some help on it. Scenario for these two tables:FooFooID PKDescriptionBarBarID PKFooID FKNo cascade delete on the FK relationship, I want to handle it explicitly based on logic.I want to delete Bar for a specific value of BarID, and want to delete all Foo that are in the intersecting table Bar by the FK.I begin a transaction, and here is the issue:If I delete Foo first, I get a FK constraint issue, there is a record in Bar corresponding to it.If I delete Bar first, the FK does not exist for the query to find the children in Foo. As silly as it sounds I just cannot think of the answer right now, thanks for any help.  

View Replies !   View Related
Cannot Delete SQL Job
I have several jobs in SQL 2005 that I cannot delete. I get "The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id" the conflict occured in database "msdb", table "dbo.sysmaintplan_subplans" columd 'job_id'.
How can I fix this?
TIA <JP>

View Replies !   View Related
How To Use TOP In DELETE
Hi,
I have a large database in MSSQL 2005 (rows around 3,788,299 : size 4GB)
Two questions
1. SQL query ~ select top 100 * from [tablename], how do capture the next 100 record?
2. SQL query ~ delete top 100 from [tablename] --- MSSQL show me syntax error? How can I only delete the top 100 record?
 
thk.

View Replies !   View Related
Not Able To Delete A Row !
I am using the Issue Tracker Starting Kit and I migrated not too long ago the DB from sql 2000 to 2005.  Now in Microsoft Management Studio when I try to delete a row from the IssueComments table I get the following error message:
No rows were deleted.A problem occurred attempting to delete ros 4215Error Source: Microsoft.VisualStudio.DataTools.Error Message: The updated rows has changed or been deleted since data was last retrieved.
Correct the errors and attempt to delete the row again
If someone has some explainations or advises at what I should look into to resolve my problem I would appreciate greatly. 
Thank you

View Replies !   View Related
Is This DELETE Possible?
I am getting error messages when I try to delete from a table usingthe values in the table itself. The intent is to delete all rows fromTableA where col_2 matches any of the col_1 values.DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =y.col_2)Error msg: The table 'TableA' is ambiguous.Can this be done with SQL or should I use T-SQL with cursors here?

View Replies !   View Related
Delete Help
i have table Controller (ControllerID, ControllerNAme, isActive)and tables SendControllerForCitizenClaim (SendID, ControllerID, Region),table SendControllerForCompanyClaim (SendID, ControllerID, REgion)..and so on .. i have 14 tables of SendController each for a certainpurpose.I have a procedure for deleting controllers. I want that if a controlleris sent (i.e its id is in at least one of the tables) then isActive=0otherwise if the controller id isn't present in neither of the 14 tablesthen delete controler record.How do i do that wihout having to write 14 If Not exists statements??*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !   View Related
Help With Delete
I am having an issue with the way SQL server deletes data. I amtrying to delete data from the DB and have it completetly gone.Here is what I did1. added a table with a row of image data2. inserted a text file into the row with a string i could searchfor.3. opened the mdf file up in a hex editor and found the data4. deleted the row in sql server5. reopened the file in the hex editor and the data was still thereno matter what i do i cant get rid of the data from the file. i havetried shrinkdatabase, update and updatetext to overwrite the data andnothing works.anyone know how to REALLY delete things from an SQL DB?Thanks

View Replies !   View Related
I Cant Delete A Row
i cant delete a row on my table. when i click delete it say : "Insufficient key column information for updating or refreshing......" what can i do?

View Replies !   View Related
DELETE On FK
I am having 2 tables :

products :

product_id (PK, int, not null)
reports_id (FK, int, not null)
name

reports :

reports_id (PK, int , not null)
dateR



If I : DELETE FROM reports WHERE reports_id = 100

how can I create a contraints in MS SQL 2000 that automaticly all the products with reports_id = 100 are deleted too ?

thank you

View Replies !   View Related
How To Delete?..
what codes should i use if i want to delete a record in sql using vb6?.,

because this doesnt work..

Confirm = MsgBox ("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
If Confirm = vbYes Then
adodc1.Recordset.Delete
MsgBox "Record Deleted!", , "Message"
Else
MsgBox "Record Not Deleted!", , "Message"
End If

-grrr.,they are teaching us about sql now??.,
gggrrrrrr!!!!.,our skuL is useless!.,

View Replies !   View Related
Delete From
Dear friends,

I have a table called CAT with a PK field ID_CAT where it data type is a varchar(30).
I can post it without any error type but if I send a statement for delete a record
using any fields in the table, the follow message error rise in a exception:


Project DigitalCAT2004.exe raised exception class EOleException with
message 'Syntax error converting the varchar value '01-02503/99' to a
column of data type int'. Process stopped. Use Step or Run to continue.

Im using Delphi with ADO components for acess the server, with Command component
where Im using the follow statement :

CMD.CommandText:='DELETE FROM CAT WHERE ID_CAT='+Edit1.Text;
CMD.Execute;

Please somebody help me cos I dont know more what I can do.
Thanks for attetion

Leonardo Almeida

View Replies !   View Related
Cannot Delete A Row
Hi All,
I got error message : "Msg 431,Level 16, State 0 Unable to bind foreignkey constraint. Too many tables involved in
query." when I tried to delete a record from a 'parent' table. Although, before the deletion, I already delete all related
records in 'children' tables whose fields references the key of that table.
I checked the 'object dependencies'and included all those tables that 'depends' on that 'parent' table in my delete SQL.
Anybody has any idea? I recall I read somewhere that's there's a limit (160 ... not sure) of tables that can reference to the key of
a 'parent' (master) table.
Thanks for all your help.
David Nguyen.

View Replies !   View Related
Delete A Job
I am trying to delete some jobs that were created by a maintenance plan. When I try, it gives me the message "Cannot add, update or delete a job that originated from an MSX server" Any ideas how to get rid of this job?

Thanks,
Ken Nicholson

View Replies !   View Related
Delete With No Log
I need to delete half of the rows in one of the tables. Is there anyway to delete rows without writing to the transaction log?

View Replies !   View Related
Why? How Can I Delete The Sql.log
I have the sql.log in my system drive.
But I can not delete it.
Because, somebody used it..
I can not found any programe used it.
Help me..Please!!

Gemmy

View Replies !   View Related
How Can I Delete???
What is the best way to delete 80 million rows from a table?? This rows are not needed and will never be needed.
Thanks,
Hiku

View Replies !   View Related
Sorry, How Do I Delete In SQL
I think its a really easy Q for those who know, I dont know a thing about sql but a contractor has stuffed me and I need to remove all the test data from a bunch of tables in a database.

Is there an easy way (or a way) to remove all the info in the rows of data in numerous tables without me messing up the tables for future use?

I though using query manager and just DELETE * FROM table_name

is that going to mess things up?

Appreciate the help.

View Replies !   View Related
DELETE...FROM...WHERE...NOT IN
I am trying to delete a specific record where it does not exist in another related table
ex. delete a registration if it does not have an associated invoice

ALTER PROCEDURE
dbo.DeleteRegistrationByRegistrationID(@registrationID int)
As
Delete
@registrationID
From
registrations
Where
registrations.registrationID
NOT IN (
SELECT
invoices.registrationID
FROM
invoices
WHERE
invoices.registrationID = @registrationID);

View Replies !   View Related
Delete
Hi everyone. Im here again to ask help.

DECLARE @myString varchar(max)
SET @myString = 'abcdefghijklmnop'

How could I delete the first and last letter of the string?

Thanks :)

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

View Replies !   View Related
Using Top In Delete
Hi All,

How are rows ordered in Database? When we delete Top 10 from a table, which rows gets deleted?

(I believe the database is based on the relational algebra, where there is no particular ordering of elements(rows))

Please correct if I am wrong,

Thanks,

Prakash.P

View Replies !   View Related
Delete
writes "how to delete a table"

View Replies !   View Related
PLEASE DELETE
Please delete. Your members are unable help. Thanks anyway!

View Replies !   View Related
Delete RPT
Hi There Guys please assist.

Hi have an issue and can't seem to find any solution.

When creating a SQL Script i call it rpt.sql or avg.txt the system deletes the file when trying to create the same script again windows says that the file already exsist.

Can some please assist.

View Replies !   View Related
Delete
If I have lots of packages within my rates and if I want to delete them all,
Any suggestion on script please.
let me explain a bit more
I have got rate code and which is divided in rate header and rate ditails, I attached my packages to rate ditails instead of rate header and need to delete them all.

View Replies !   View Related
Delete's Value
Has anyone ever had an issue when they try to commit changes back to the database based on a value they enter in a application...the value that u would use(i.e Select * From Customers Where CustomerID = 'value')to get the rest of the value's pertaining to that value you enter gets deleted from the database....its really wierd but i have that problem

View Replies !   View Related
DELETE!
Hi everybody!
I have a table with two columns ,PK=Primary Key and Name :
PK Name
1 A
2 B
3 C
4 A
5 B
6 A
7 B
8 C
9 D
10 B
I want to delete repeated rows that have similar names.
I need a scpoe DELETE !
after that i want to have this:
PK Name
1 A
2 B
3C
4 D
Thanks!

View Replies !   View Related
Delete Min Ids


There are duplicates in my table and I am trying to write a sql query to delete the Id column which has the min value and therefore leaving the ID with the max value in the table.
This is what I have but not sure how to complete the where clause in the delete part.

Thanks

delete from tblPricesHistoryCopy
where
????
select
min(HistoryID) as 'HistoryID',
securityname,
closingdate
from
tblPricesHistoryCopy
group by
securityname,
closingdate
having count(closingdate) > 1

View Replies !   View Related
Cant Delete Row
I ran into a scenerio this morning

I made a table with no relations or primary id

It is an error log table basically

I went to delete a row and I got an error of

Cant delete row because it does not make them unigue or it alters multiple rows(3)

I didnt want this by no means, what did i not sdet right and how do you delete the rows without deleting the table and starting out new again?

Davids Learning

View Replies !   View Related
Delete Records ???
hello friends.
i have table1 in aspnetdb.mdf and i have picturefile of columname.. datatype of picturefile is vchar(50)
i want to delete my record automatically from my table1 after two weeks from inserting my record date..
i want to delete my picture file that located /pictures/tree.jpg (example).. pictures/tree.jpg was uploaded as picturefile on table1 by user before
how can i do this ?
cheers

View Replies !   View Related
Is There Any Sql Query To Delete Any Particular Row
In sql server ...is there any opton to delete any particular row..
i have table student
no name
1    raja
2    pravin
3   abraham
 
suppose i what to delete 2nd row is there any option to delete whithout specifying no or name just only row(ie 2)
pl reply me....

View Replies !   View Related
Delete Query?
Hi friends, I want to delete more than record using id.i pass group of id(with string concat) like 10|11|20|25. in stored procedure i want to delete corresponding record.10112025 any idea?Thanks,Durai 

View Replies !   View Related
How To Delete A Row From Database
hello
i am using sqldatasource 2005:
i am using this
<sqldatasource id="a">
deletecommand=delete from table where rec_key=@rec_key
<deleteparameters>
<asp: sessionparameter sessionfield="session_rec_key" type=string>
this works fine
but if i use this
deletecommand=delete from table where rec_key=@rec_key and name=@name
<deleteparameters>
<asp: sessionparameter sessionfield="session_rec_key" fieldname="rec_key" type=string>
<asp: sessionparameter sessionfield="session_name" fieldname="name" type=string>
this gives error saying scalar variable @name is not defined.
 
but the value is going properly.
please help me find the solution.
thanks
 

View Replies !   View Related
Delete Query
Can any one please correct this query.Shall i write like this.Please correct this query.
 Delete globalDocs.dbo.gdoc_File set IsTrue=1 where FileID='abc'

View Replies !   View Related
Sql Delete Question
I want to delete records from one table where the id is equal to the id in another table, but I'm having problems:Here's my sql:DELETE FROM AccessLogTestINNER JOIN AccessLogBadHits ON AccessLogTest.accessid=AccessLogBadHits.accessidWHERE AccessLogTest.accessid=AccessLogBadHits.accessid  The error is: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'inner'. I know this has a really simple answer, I just can't seem to figure out whats wrong here. 

View Replies !   View Related
Delete From Database
i have 3 tables
 User  (userid(P.K.),username,userpassword,usertypeid(F.K.))
UserRights (userrightsid(P.K.), insert, update, delete, select, modulename)
User_UserRights(user_userrightsid(P.K.), userid(F.K.), userrights_id(F.K.))
i want to delete all the data from this tables for a particular id how can i do it
i want a stored procedure for this please anyone can provide with the full procedure.
 

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved