The method I wrote to delete records is working if there's one record, but not for more than one. This method takes a string, and I've examined what is passed in and everything looks ok, but then no error occurs where there's more than one record, but no delete occurs either.
Here's what I see in my Trace.Warn statement: Delete From Photo_TBL where PhotoID IN ('223,224')
So the sql looks fine but I can't figure out why it's not working. Here's my method for deleting. Can you see what might be wrong? Thanks
public void PerformDeletion(string photoID)
{
//Response.Write(photoID);
// Set up SqlCommand, connection to db, sql statement, etc.
SqlCommand DeleteCommand = new SqlCommand();
DeleteCommand.Connection = DBConnectionClass.myConnection;
DeleteCommand.CommandType = CommandType.Text;
// Store Primary Key photoID passed here from DeleteRows_Click
// in a parameter for DeleteCommand
SqlParameter DeletePrimaryKeyParam = new SqlParameter();
DeletePrimaryKeyParam.ParameterName = "@PhotoID";
DeletePrimaryKeyParam.Value = photoID.ToString();
// Insert new parameter into command object
DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);
// Delete row, open connection, execute, close connection
DeleteCommand.CommandText = ("Delete From Photo_TBL where PhotoID IN ('" + photoID + "')");
Trace.Warn(DeleteCommand.CommandText);
// DeleteCommand.Connection.Close();
DeleteCommand.Connection.Open();
DeleteCommand.ExecuteNonQuery();
DeleteCommand.Connection.Close();
// Call BindData so GridView is binded and most recent changes appear
BindData();
}
cmd.CommandText = "DELETE FROM LOGCALL_TABLE WHERE LOGCALL_TABLE.OpenCall like 'X' AND LOGCALL_TABLE.StopTime like '" & Format(Range("I" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.EndTime like '" & Format(Range("J" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.ClientName like '" & Range("B" & CStr(ActiveCell.Row)).Value & "' AND LOGCALL_TABLE.Representative like '" & Range("C1").Value & "' and LOGCALL_TABLE.DateOnCall like '" & Date & "';" cmd.Execute conn.Close End Sub
My delete record from database table below is not working, do anyone here know why delete is not working.
Code Snippet
Private Sub BindingNavigatorDeleteItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorDeleteItem.Click
Dim conn As New SqlClient.SqlConnection(My.Settings.HACS) Dim strSQL As String Dim cmd As New SqlCommand strSQL = ("DELETE FROM CUSTOMER WHERE CUSTOMER_ID =" & Me.IDTextBox.Text)
If conn.State <> ConnectionState.Open Then conn.Open()
If MessageBox.Show("Are you sure you want to delete the current record?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question, _ MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.OK Then
When I debug my code I see the string going into the parameter correclty, but the the delete statement doesnt work and I'm not sure why. Does this look ok? // Set up SqlCommand, connection to db, sql statement, etc. SqlCommand DeleteCommand = new SqlCommand(); DeleteCommand.Connection = DBConnectionClass.myConnection; DeleteCommand.CommandType = CommandType.Text;
// Store Primary Key photoID passed here from DeleteRows_Click // in a parameter for DeleteCommand SqlParameter DeletePrimaryKeyParam = new SqlParameter(); DeletePrimaryKeyParam.ParameterName = "@PhotoID"; DeletePrimaryKeyParam.Value = photoID.ToString();
// Insert new parameter into command object DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);
// Delete row, open connection, execute, close connection DeleteCommand.CommandText = "Delete From Photo_TBL where PhotoID IN (@PhotoID)"; Response.Write(DeleteCommand.CommandText); // DeleteCommand.Connection.Close(); DeleteCommand.Connection.Open(); DeleteCommand.ExecuteNonQuery(); DeleteCommand.Connection.Close();
I grouped everything together so you see it all. I'm not getting any errors but nothing is happening. I had this working and then I converted to Stored Procedures and now it's not. CREATE PROCEDURE UpdateCartItem(@itemQuantity int,@cartItemID varchar)ASUPDATE CartItems Set pounds=@itemQuantityWHERE cartItemID=@cartItemIDGO <asp:Button CssClass="scEdit" ID="btnEdit" Runat="server" Text="Update" CommandName="Update"></asp:Button> Sub dlstShoppingCart_UpdateCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdUpdateCartItem As SqlCommand Dim UpdateCartItem Dim strCartItemID As String Dim txtQuantity As TextBox strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) txtQuantity = e.Item.FindControl("txtQuantity") connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdUpdateCartItem = New SqlCommand(UpdateCartItem, connStr) cmdUpdateCartItem.CommandType = CommandType.StoredProcedure cmdUpdateCartItem.Parameters.Add("@cartItemID", strCartItemID) cmdUpdateCartItem.Parameters.Add("@itemQuantity", txtQuantity.Text) connStr.Open() cmdUpdateCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub ____________________________________________________________ CREATE PROCEDURE DeleteCartItem(@orderID Float(8),@itemID nVarChar(50))ASDELETEFROM CartItemsWHERE orderID = @orderID AND itemID = @itemIDGO <asp:Button CssClass="scEdit" ID="btnRemove" Runat="server" Text="Remove" CommandName="Delete"></asp:Button> Sub dlstShoppingCart_DeleteCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdDeleteCartItem As SqlCommand Dim DeleteCartItem Dim strCartItemID strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdDeleteCartItem = New SqlCommand(DeleteCartItem, connStr) cmdDeleteCartItem.CommandType = CommandType.StoredProcedure cmdDeleteCartItem.Parameters.Add("@cartItemID", strCartItemID) connStr.Open() cmdDeleteCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub
if i try to delete data via MFC from Microsofts SQL Server 2000 i get following message: Data can be read only.. Weird i can write and read already. do i have to setup anything in Micrsofts SQL Server 2000 ??
Hi all, i m using VS 2005 and I have to display records with feature of INSERT / DELETE ITEMS But when i connect to Sql Server Database and select * from columns but here when clicking the "Advance" button , i do not get "Advance Sql generation Option " highlighted. Instead , it is turned off. i.e The Following options are not highlighting ------ Generate Insert, Update, Delete statements ------ use optimistic concurrency Plz guide me anyone..... is anything wrong with our VS 2005 software installed? Bilal
I've written a SQL script to go in and pull student grades for honor roll. The first subquery in the script eliminates any students that have any D's, and the second sub query is supposed to give me a only students that have no more than 1 C of any type as the Honor Roll criteria is that a student has to have a GPA between 3.0 and 3.49 and no more than 1 C. If I run each individual sub query by itself they work and pull the students that they are supposed to. If I run the main query along with the first sub query to eliminate students with D's everything works. If I run the 2nd sub query by itself it will pull the students that meet the criteria. If I run the main query along with both sub queries or the main along with the 2nd sub query it doesn't work. Here is what the query looks like that I'm trying to run. Thanks in advance for any help.
I need to write a query that requires respective fields referencing from multiple tables. For example, here are the tables: Main Table: InfoID Team1 Player1 Team1 Table: Player_ref Player Team_Player_ref Player1 John doh Table: Team_ref Team Team_Player_ref Team1 My Team
Ideal result Table from query: InfoID Count John Doh 1 My Team 2
Any suggestion to creat the Ideal Results table from query? Normally, I could do it if it only referenced from 1 table, I would do an inner join, however, since there are 2 referenece table, doing inner join wouldn't work. A proposed suggestion would certainly be nice. Thanks in advance. --daydreamstuck at the current problem
I have a package with two sequence containers, each containing two SQL tasks and a data flow task, executed in that order. I want to encapsulate the data flow task in a transaction but not the SQL tasks. I have the TransactionOption property set to 'required' on the data flow tasks and 'supported' on the SQL tasks and the sequence containers. When I run the package I get a distributed transaction error on the first SQL task of the second sequence container:
"[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE DistTransTbl2" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
The only way I can get the package to succeed is to set the TransactionOption = 'required' on the sequence containers and 'supported' on all subordinate tasks. This is not what I want, however. Any ideas?
I am pretty new to reporting software and I was just wondering in reporting services 2005 when working with multiple datasets and data connection string in order to populate text boxes if the data is a number it is prefixed with SUM at the start of the expression and if it is text it is prefixed with FIRST even though it is just for one value.
I was wondering if this is normal or have I messed up somewhere?
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.
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.
I have 30 or 40 tables in a database called Temp with names like PV_ and i am trying to figure out how to loop through and delete them. I've searched all over the web and forums looking for an answer but can't seem to find it. Does anyone know how to delete tables with the keyword like?
Hello All, I have a table: idSurrogate int identity id1 int id2 int
id1 + id2 is a unique index
I need to delete multiple rows from the table given a list of id1 and a list of id2 In other words @id1List = '10,20,30' @id2List = '1,3,5' I need to delete these 3 rows from the table 1) @id1=10 and @id2=1 2) @id1=20 and @id2=3 3) @id1=30 and @id2=5
I am a bit lazy today - can anyone help out with a delete sql stmt
Hello I am trying to delete multiple rows in my sql 2000 database, I have used the following sysntax but I keep getting errors: DELETE From NameList WHERE LName = 'Smith';and LName = 'Jones';and LName = 'Peters';and LName = 'Adams';and LName = 'Conner';and LName = 'Simon'; I have tried editing the syntax in a variety of ways, but I just can't find the correct solution. I have Googled and so far not found another syntax format. What am I doing wrong. Thanks. Lynn
Hi: I have 3 tables namely: 1 Category(CategoryID(int), CategoryName(varchar), 2 SubCategory( CategoryID(int),SubcategoryID(int),SubcategoryName) 3 Productlist (ProductID(int),ProductName(varchar),CategoryID(int), CategoryName(varchar),SubcategoryID(int),SubcategoryName(varchar)) how to delete correspoding subcategories of category from SubCategory,Productlist tables using triggers Ex: Category :TV Subcategory:ColorTV,Plasma,LCD...Plz Send me the query.... Thanks
I have a table with a large number of records that I need to delete, before attempt to perform the delete I also archived the records to another table.
So I need to delete all of these selected records stored in the archive table from the main table. I can now reference all the records that qualify for the delete in the main table by performing a join on the archive table like so:
select * from my_main_table a join my_archive_table b on a.distinct_id=b.distinct_id and a.surrogate_key=b.surrogate_key and a.identifier=b.indentifier
So all the records check out to be the ones I'd like to perform a delete on but I just can't figure out how to perform a delete of the records with little or no change to the existing query.
Obviously something like this won't work:
delete from my_main_table a join my_archive_table b on a.distinct_id=b.distinct_id and a.surrogate_key=b.surrogate_key and a.identifier=b.indentifier
Though it would be nice if it did.:D
So my question is how would I use the existing query with some modification to delete only the records that this query returns. I've tried selection of records in the main table based on the existing records in the archive table but it can return a higher number of records than what I know is expected. I actually need the join specified to be in place to do it.
I have an SQL 2000 server. I have multiple tables in the db that have a row with a time stamp of '10-23-2007'. What I am trying to do is delete these specific rows because they don't belong. So I need to query the db for table names that are like 'elect_Sub%' and then execute a query on those tables that would delete the row with the time_stamp '10-23-2007'. I know that I have to use the db schema to get the table names, but I need help in writing the sql script that will automatically scroll through the tables.
i am using asp.net vb, i have 2 table show as below if i want to delete the forumid 1 row,then how would i delete the topic table who belong to the forumid 1. how would 1 do it if i am using gridview Forum table Forumid | Forumname 1 | hi 2 | me Topic table Topicid | Forumid | Topicname 1 | 1 | yo 2 | 1 | everyone 3 | 1 | google
How can I run through an array of ID's and delete the fields associated to it in a single table? Â I have a lot of bad data that needs to be removed. Â BTW, I'm using the SQL Server Management Studio to do this.
I'm currently doing by hand by using the following.
USE database; DECLARE @id integer SET @id = XXXXX DELETE FROM table WHERE id = @id;
I would like to use the same code but with a slight variation to support an array.
fetch next from emailCursor into @Name,@info, @Recipients,@MyMessage
end close emailCursor deallocate emailCursor
Currently I have two users in my table that I want to send email to, but when the stored procedure runs I got two emails for the last user in the table instead one for each user.
I have the following scenario : CustomerDetail customerid customername status app_no
[status = 0 means customer virtually deleted]
CustomerArchive archiveno [autoincrement] customerid customername status
At the end of the month, I have to physically delete customers. I have written two stored procs:
proc1 create proc spoc_startdeletion as declare @app_no int select @app_no = (select app_no from customerdetail where status=0) EXEC spoc_insertcustomerarchive @app_no -- After transferrin, physically delete delete from customerdetail where status=0
proc2 create proc spoc_insertcustomerarchive @app_no int as insert into customerarchive(customerid,customername,status) select customerid,customername,status from customerdetail where app_no = @app_no
It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value'
How can i transfer multiple rows one by one from the customerdetail to customerarchive and then delete the rows once they are transferred.
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'
I have a requirement to delete all the orphans users for the databases. The issue I am having is with when database principal owns a schema in the DB, User cannt be dropped.
How do I transfer it to DBO in case I am looping multiple databases. This is what I got so far .
declare @is_read_only nvarchar (200) Select @is_read_only = is_read_only from master.sys.databases where name='test' /* This should be a parameter value */ IF @IS_READ_ONLY= 0 BEGIN Declare @SQL as varchar (200)
I frequently have the problem where I have a list of items to delete ina temp table, such asProjectId Description------------- ----------------1 test12 test43 test34 test2And I want to delete all those items from another table.. What is thebest way to do that? If I use two IN clauses it will do it where itmatches anything in both, not the exact combination of the two. I can'tdo joins in a delete clause like an update, so how is this typicallyhandled?The only way I can see so far to get around it is to concatenate thecolumns like CAST(ProjectId as varchar) + '-' + Description and do anIN clause on that which is pretty nasty.Any better way?
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?