I Use SQL 2000, Can You Use One Delete Query To Delete 2 Tables?

Nov 26, 2007

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 1 Replies


ADVERTISEMENT

One DELETE Sql Statement To Delete From Two Tables

Aug 12, 2007

I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?

View 5 Replies View Related

Delete From 2 Tables With 1 SQL Query

Mar 23, 2004

I have 2 tables that are joined together by a primary key (Order Number). Can I use one SQL query to delete from both of the tables. One table contains the order information from a client (Order Number, Customer Name etc). The other table has order information (Order Number, Item Number, Quantity Ordered etc.)

I need one statement that will allow me to remove the items from both tables. Can this be done.

Thanks in Advance

Wes

View 6 Replies View Related

Delete Query For Two Tables

Nov 26, 2013

I have two tables PROFILES & ROLE

CREATE TABLE PROFILES(
ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL
)

[Code] ...

This query joins both the tables and gets displayed in a grid.

select P.ID, Name, Address, Role, Applications
from PROFILES P
Inner Join ROLE R
ON P.ID= R.ID

I need to write a delete query which deletes data from both the table in a single query.

View 3 Replies View Related

Delete Duplicate Rows From Two Tables With Same Structure In Sql Server 2000

Aug 20, 2007

Hi

I want to delete the duplicate rows from two tables and get the resultant non-duplicate rows from both the tables into another table

View 4 Replies View Related

How To Run Delete Query / Delete Several Rows Just By One Click ?

Feb 16, 2008

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 2 Replies View Related

Delete Duplicate Entries From Tables In My Database Using Query Analyzer

Jun 25, 2004

Hello,

How can I delete duplicate entries from tables in my database using Query Analyzer, as there are many duplicate entries in my tables, I want to delete them.

Thanks in advance,
Uday.

View 4 Replies View Related

How To Delete Unwanted Data From Multiple Different Tables With One Single SQL Query?

Mar 18, 2008

This a microsoft SQL 2000 server.
I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID.
Here is my problem:
Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.

I know how to get the user table names from the DB as follows:

SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U' and name like 'Elect_Sub%Daily'

What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".

delete from tableName where Date_Stamp = '2008-3-18'

View 7 Replies View Related

SQL Server 2008 :: Maintenance Plan Delete History Trying To Delete Wrong Files

Sep 11, 2015

I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.

It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog

how I can get this corrected so I can get the Maintenance Plans to run correctly.

I have tried deleting and recreating the Plan but to no avail

View 0 Replies View Related

Master Data Services :: Hard Delete All Soft Delete Records (members) In Database

May 19, 2012

I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.

View 18 Replies View Related

Copy And Delete Table With Foreign Key References(...,...) On Delete Cascade?

Oct 23, 2004

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 1 Replies View Related

SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!

Nov 13, 2006

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
/
Table B Table C
/
Table D

So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, 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 C when deleting a row in table 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 from B where MeetingID = ID;
Delete from C 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 6 Replies View Related

Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another

Nov 17, 2006

I'm trying to clean up a database design and I'm in a situation 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 6 Replies View Related

Solution!-Create Access/Jet DB, Tables, Delete Tables, Compact Database

Feb 5, 2007

From Newbie to Newbie,



Add reference to:

'Microsoft ActiveX Data Objects 2.8 Library

'Microsoft ADO Ext.2.8 for DDL and Security

'Microsoft Jet and Replication Objects 2.6 Library

--------------------------------------------------------

Imports System.IO

Imports System.IO.File





Code Snippet

'BACKUP DATABASE

Public Shared Sub Restart()

End Sub



'You have to have a BackUps folder included into your release!

Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo


addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"


'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""



Try

File.Copy(z, f)

Catch ex As System.Exception

System.Windows.Forms.MessageBox.Show(ex.Message)

End Try



MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub






Code Snippet

'RESTORE DATABASE

Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String


With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName



'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With


'Notify user
MsgBox("Data restored successfully")


Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub








Code Snippet

'CREATE NEW DATABASE

Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;

Jet OLEDB:Engine Type=5")

Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column

Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table



'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet

OLEDB:Engine Type=5")


'Open the Catalog
Cat.ActiveConnection = Cn



'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"


'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable



Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")


'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)



'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)



'User notification )
MsgBox("A new empty database was created successfully")


'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing


'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If

End Sub








Code Snippet



'COMPACT DATABASE

Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine


'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program

FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")


'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")


'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")


'User notification
MsgBox("The database was compacted successfully")

End Sub

End Class

View 1 Replies View Related

Allow Single Row Delete From A Table But Not Bulk Delete

Sep 16, 2013

The requirement is: I should allow single row delete from a table but not bulk delete. An audit table should get updated if there is any single delete or single update. So I wrote the triggers as follows: for single and bulk delete

ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS

[code]...

When I try to run the website, the database error I am getting is:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

View 3 Replies View Related

Delete Doesn't Delete Rows, But @@ROWCOUNT Says It Did

Aug 20, 2007

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 3 Replies View Related

Cannot Amend Or Delete Subscription And Cannot Delete Report.

Nov 20, 2007



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 subscription I 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 and I'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 to fart 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 3 Replies View Related

Delete From Two Tables

Jul 31, 2006

        These are my selcet and delete commands, however I am trying to delete from two tables both Assets and AssetAttribute. The both have related colums AssetTypeId, AssetAttributeId. I need to slect them first then delete them. I need the one action delete to delete int eh same information from both tables. Help. I only have the one written for Asst table, I need to include the Assetattribute table. Please help me. SelectCommand="SELECT AssetId, AssetTypeId, Description, AssetAttributeId, SKU, Barcode, GovBarcode, WarehouseId, IsVehicle, DeploymentStatus FROM Asset, AssetAttribute WHERE AssetId = @AssetId" DeleteCommand="DELETE FROM Asset, AssetAttribute WHERE AssetId = @AssetId"

View 3 Replies View Related

Delete All Tables

Dec 4, 2006

Hi folk, whats the SQL syntax of deleting all user tables  of a specific database on a Microsoft SQL server?thanks in advance,mulata 

View 3 Replies View Related

How To Delete From Two Tables At Once

Jun 18, 2007

I have 2 tables "Orders" and "OrderProducts"
In my application, there are moments when I clean up these tables.
There is a query that looks for some flag in the "Orders" table, and deletes the records.
But there are related (PK-FK) records in the "OrderProducts" table.
How can I delete also these records in the same query?

View 2 Replies View Related

Need To Delete From Two Tables.

Oct 23, 2007

I've set up a grid control on my other page and trying to delete items via a check box.  I got it to work with deleting from one table but I need to delete from two tables at the same time.
How do I code using a stored procedure called Delete Resource instead of the "Delete from Titles where ...(code is below)
Now if I can add my other table called resources to the sql query that's fine as they both will use titleID to delete the info.Any help would be appreciated.  Thanks! 
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim gvIDs As String = ""
Dim chkBox As Boolean = False
'Navigate through each row in the GridView for checkbox itemsFor Each gv As GridViewRow In GridView1.Rows
Dim deleteChkBxItem As CheckBox = CType(gv.FindControl("deleteRec"), CheckBox)
If deleteChkBxItem.Checked Then
chkBox = True
gvIDs += CType(gv.FindControl("TitleID"), Label).Text.ToString + ","
End If
NextDim cn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(SqlDataSource1.ConnectionString)
If chkBox Then
TryDim deleteSQL As String = _
"DELETE from Titles WHERE TitleID IN (" + _
gvIDs.Substring(0, gvIDs.LastIndexOf(",")) + ")"Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(deleteSQL, cn)
cn.Open()
cmd.ExecuteNonQuery()
GridView1.DataBind()Catch Err As Data.SqlClient.SqlException
Response.Write(Err.Message.ToString)
Finally
cn.Close()
End Try
End IfProtected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim gvIDs As String = ""
Dim chkBox As Boolean = False
'Navigate through each row in the GridView for checkbox itemsFor Each gv As GridViewRow In GridView1.Rows
Dim deleteChkBxItem As CheckBox = CType(gv.FindControl("deleteRec"), CheckBox)
If deleteChkBxItem.Checked Then
chkBox = True
gvIDs += CType(gv.FindControl("TitleID"), Label).Text.ToString + ","
End If
NextDim cn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(SqlDataSource1.ConnectionString)
If chkBox Then
TryDim deleteSQL As String = _
"DELETE from Titles WHERE TitleID IN (" + _
gvIDs.Substring(0, gvIDs.LastIndexOf(",")) + ")"Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(deleteSQL, cn)
cn.Open()
cmd.ExecuteNonQuery()
GridView1.DataBind()Catch Err As Data.SqlClient.SqlException
Response.Write(Err.Message.ToString)
Finally
cn.Close()
End Try
End IfEnd Sub
End Class
 
 

View 2 Replies View Related

Delete From Tables

Oct 6, 2004

I have 3 tables have the same column ID.I want to delete records in one query like "delete from orders as o,ordersdetail as od,membersdns as m where o.ID = 821" but I get the error "incorrect syntax near as" .is this possible to delete records like this?

View 6 Replies View Related

Delete From Two Tables

Jul 31, 2006

These are my selcet and delete commands, however I am trying to delete from two tables both Assets and AssetAttribute. The both have related colums AssetTypeId, AssetAttributeId. I need to slect them first then delete them. I need the one action delete to delete int eh same information from both tables. Help. I only have the one written for Asst table, I need to include the Assetattribute table. Please help me.
SelectCommand="SELECT AssetId, AssetTypeId, Description, AssetAttributeId, SKU, Barcode, GovBarcode, WarehouseId, IsVehicle, DeploymentStatus FROM Asset, AssetAttribute WHERE AssetId = @AssetId"
DeleteCommand="DELETE FROM Asset, AssetAttribute WHERE AssetId = @AssetId"

View 3 Replies View Related

How To Delete All Tables

Apr 3, 2004

Hi All,

How can i delete all the tables in a DATABASE with a single shot!!

Thanx in advance

View 14 Replies View Related

Delete From ALL Tables

Nov 19, 2007

I have 5 tables in the same database named the following: do, name, olm, image, person, scr

They are all link together by and have the following field in each: FCN

I would like to delete the corresponding record in each table where the value of the field 'image_path' in the 'image' table is '999999.jpg'

What would the syntax be?

delete from do, name, olm, image, person, scr
where image.image_path = '999999.jpg'
innerjoin something????

Thanks

View 14 Replies View Related

Delete Tables

Feb 26, 2008

Hello,

To delete all tables from my database I use:
SELECT 'DROP table ' + table_NAME
FROM INFORMATION_SCHEMA.tables

To get the list of the tables. Then I run that list:
DROP table Folders
DROP table Files
...

Because of relationships I need to run it various times until all the tables disappear. Is there a way to everything in one command?

Thank You,
Miguel

View 4 Replies View Related

Delete All Tables

Oct 5, 2007



Hi,

How can i delete/drop all tables in a database using a script.

Thanks.

View 9 Replies View Related

Delete Tables Automatically

Nov 12, 2006

How can I delete tables in a SQLServer Database automatically (at the moment I just do it using Micorosft SQl Management Studio Express manually) But its important that I can do that automatically, the best thing would be if I can do that from a .NET programm.  thanks in advance,mulata 

View 6 Replies View Related

DELETE From Multiple Tables

Jan 15, 2007

I am converting all the inline sql in my ASP.NET app to stored procs and I am experiencing some difficultly with my shortest and least complex stored proc. I am new to both ASP.NET and SQL especially using stored procedures so this may have some extremely obvious flaw in it. I am trying to delete a entry that has parts in two tables. I created a stored procedure along the lines of: CREATE PROCEDURE DeleteEntry(Â Â Â Â Â @EntryIDINT)ASDELETE FROM firstTable WHERE entry_id = @EntryIDDELETE FROM secondTable WHERE entry_id = @EntryIDI have a remove button on a asp page that executes the stored procedure but it only removes the entry from the second table the first time I click it and then when I click it a second time it removes the entry from the first table.

View 1 Replies View Related

Delete From 3 Tables In Sql Server

Mar 25, 2004

Hi,

I am working on IBUYSTORE's db. I have added a backoffice/admin to the site.
I have a page where I retrieve a customer's details based on the customerid such as the following:

Dim Ssql As String = "SELECT O.CUSTOMERID, C.CUSTOMERID, C.PASSWORD, C.FULLNAME, C.EMAILADDRESS, O.ORDERID, OD.ORDERID , OD.PRODUCTID FROM customers AS C , ORDERS AS O , ORDERDETAILS AS OD WHERE C.customerID=O.CUSTOMERID AND OD.ORDERID=OD.ORDERID AND C.customerID = @cust_ID"

CMD As New SqlCommand(Ssql, oConn)
CMD.Parameters.Add("@cust_ID", id)

I am also using a sqldatareader.

I have a delete button. If the site manager clicks on that button i want to delete all that customer's details including all his orders from both orders and orderdetails tables.
I am not sure how to accomplish it.

The tables are as follows:
Customers: Orders: OrderDetails
customerid customerid orderid
orderid

I tried the following delete:

strDelete = "DELETE FROM CUSTOMERS where CustomerID=@CUST_id; DELETE FROM ORDERS WHERE CUSTOMERID=@CUST_id; DELETE FROM ORDERDETAILS WHERE ORDERID=@orderID "

Dim objCMD As New SqlCommand(strDelete, oConn)
objCMD.Parameters.Add("@CUST_id", id)
objCMD.Parameters.Add("@orderID", LBLORDER.Text)
oConn.Open()
objCMD.ExecuteNonQuery()
oConn.Close()


I get the following error:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders_Customers'. The conflict occurred in database 'Store', table 'Orders', column 'CustomerID'. DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_OrderDetails_Orders'. The conflict occurred in database 'Store', table 'OrderDetails', column 'OrderID'. The statement has been terminated. The statement has been terminated

View 2 Replies View Related

How To Delete Data From All The Tables?

Apr 7, 2008

I want to reset my application and delete all the data in all tables.

But I have question for this.

1. I do not know how to loop over all the tables and delete data.
2. the database have database diagram so threre are dependency with tables so the delete order is hard to decide.

Please give me a idea.

Thanks

Mark

View 4 Replies View Related

Delete From Multiple Tables

Jun 9, 2008

OK, this is a big problem, with multiple tables, but here goes. Here's my schema:

--------------------------------------------------
Events
--------------------------------------------------
ID | E_Title
--------------------------------------------------


--------------------------------------------------
EventOptionGroups
--------------------------------------------------
ID | EOG_EventID | EOG_OptionGroupID
--------------------------------------------------


--------------------------------------------------
OptionGroups
--------------------------------------------------
ID | OG_Title
--------------------------------------------------


--------------------------------------------------
Options
--------------------------------------------------
ID | O_OptionGroupID
--------------------------------------------------


--------------------------------------------------
EventRegistration
--------------------------------------------------
ID | ER_EventID
--------------------------------------------------


--------------------------------------------------
RegistrantOptions
--------------------------------------------------
ID | RO_EventRegistrationID | RO_OptionGroupID
--------------------------------------------------



OK, what I'm trying to do is, when I delete an event, I need to delete all the data associated with that event. So here's the thought process.

Delete Event based on ID
Delete all EventRegistration where ER_EventID = Event.ID
Delete all RegistrantOptions where RO_EventRegistrationID = EventRegistration.ID
Delete all EventOptionGroups where EOG_EventID = Event.ID
Delete all OptionGroups where OptionGroups.ID = EOG_OptionGroupID
Delete all Options where O_OptionGroupID = OptionGroups.ID

Sorry that it's so complicated, by I need help. There are foreign key constraints on the tables as well, so you have to work from the bottom back up.

View 2 Replies View Related

How To Delete Multiple Tables At Once

May 28, 2012

In my DB there some tables that I would like to delete. Is there a way to do it NOT one by one?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved