.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links


Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
Does INSERTing a row equivalent to DELETE & INSERT? Please provide more info on this.

View Complete Post

More Related Resource Links

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?

Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Large volume of INSERT/DELETE

 I support the DB for a 3rd party application. Lately users have been complaining about the application being sluggish. Checking into it I found that the vendor decided to write their own locking system (much like peoplesoft does/did). In short, I have a very small table (maxes out at around 3000 rows, 5 columns (int, varchar(30), int, varchar(30), int, int). They store locking records in the table, they are inserted and deleted at a rate of about 10 per sec/600-900 per min. The PK is composite across the columns required to enforce uniqueness. There is no identity column. The IO involved in these inserts and deletes results in enormous amounts of writelog waits. Since the application is built around this custom record locking, and this table is the record of who has what locked, delays on these inserts and deletes translates to delays on everything. I already have plans to address the disk subsystem. In the interim, is there anything I can do to relieve IO this table generates from a design perspective?

Allow or deny roles to update, insert and delete records.

Hello, I have two roles "Admin" and "Basic". I also have a listview on the web page. My goal is that to make "Admin" role has the highest privilege to deal with records such as "insert", "update" and "delete". For the role "Basic", it only can update the records. Is there any sample code for that? Thanks.  

Is this a bug in MERGE statement with DELETE/INSERT?

This looks to me like a bug with MERGE statement. This does not work on SQL 2008 and 2008 R2. Note that the first commneted statement works properly but the second does not. Is there any explanation why not, other than a bug? Duplication script below. use tempdb go /* IF object_id('dbo.Test', 'U') IS NOT NULL DROP TABLE dbo.Test go IF object_id('dbo.Src', 'U') IS NOT NULL DROP TABLE dbo.Src go */ IF object_id('dbo.Test', 'U') IS NULL BEGIN     CREATE TABLE dbo.Test     (         intID int NOT NULL IDENTITY PRIMARY KEY         ,sysID int NOT NULL         ,ioID int NOT NULL         ,Code nvarchar(10) NOT NULL     )     INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A')       CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test] ON [dbo].[Test]     (           [sysID] ASC,           [ioID] ASC     )     WHERE ([ioID] IN ((1)))     WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

MS Linq To SQL Samples - Insert/Update/Delete

Hi, I'm stumbling along trying my hand at Linq to SQL and have come across this code below that was provided by MS.http://msdn.microsoft.com/en-us/vbasic/bb737928.aspxThe problem is there is no ".add" method for Categories to chose in the statement db2.Categories.add(newCategory)I've seen similar videos here on ASP .NET that uses code very much like the example below, but I am unable to run this example connected to a Northwind SQL database using Web Developer 2010 Express. This is the One-to-Many example listed under Linq To SQL Samples - Insert/Update/Delete. Thanks for any help! Shawn Dim db2 = New DataClassesDataContext Dim ds = New DataLoadOptions() ds.LoadWith(Of Category)(Function(p) p.Products) db2.LoadOptions = ds Dim newCategory = New Category With {.CategoryName = "Widgets", _ .Description = "Widgets are the customer-facing analogues " & _ "to sprockets and cogs.", _ .Picture = Nothing} Dim newProduct = New Product With {.ProductName = "Blue Widget", _ .UnitPrice = 34.56#, _ .Category = newCategory, _

Update, delete, insert

I have two databases.  When I am using MS Visual Web Developer, I can drag the table onto the webpage and it gives me check boxes to Insert, Update, Delete,... When I do the exact thing using the other database, it only has check boxes forpaging, sorting and selection.  What is the difference?  I seem to have all teh same permissions in both databases. How can I make the second database update, delete and insert?  

Update, Delete, Insert Options

I have two databases.  When I am using MS Visual Web Developer, I can drag the table onto the webpage and it gives me check boxes to Insert, Update, Delete,... When I do the exact thing using the other database, it only has check boxes forpaging, sorting and selection.  What is the difference?  I seem to have all teh same permissions in both databases.

Trigger without UPdate,INSERT and DELETE


I have SQL SERVER 2005 and I have a table with a Cloumn DATETIME data Type,

I want to ceate a trigger that checks the table rows and delete the record (s) when it has (DATETIME colume value - today date()) >20  days.

Is it possible to write a trigger for that? 

I am new to SQL Server and trigger.




Enabling Edit , delete and insert in Data view with linked sources


Hello ....


I create a data view in sharepoint designer 2007 that has linked sources (Join between two lists) ....


How can I enable edit, delete and insert links in this data view  ????.... taking in consideration that options available in data view properties appear in gray ...



Insert, Update, Delete with ajax


I'm kind of new to ajax. I normally just use  the canlendar and some popup stuffs.

i'm wondering what is the practice to doing Insert, Update, and Delete on  GridView/FormView with ajax.

Is UpdatePanel the best thing i can use it with Insert, Update, and Delete?

How do I control using ListView when to show the Update/Delete/Insert buttons and when not?


It should be simple, but I didn't found yet how to do it.

There is only one user (Admin with user name and passowrd, he has a cookie so I know when he is watching the page) I would like to show him the Update/Delete/Insert button options in my ListView, but to the other users I don't want them to be able doing this kind of things, So it should be hidden from them.

How I am doing it in C# code?

Showing/Hiding these buttons?


Insert, Update and delete locks



I looking for the best way to set locks for the following sql commands:





into Users










SSIS : Insert, Update, Delete records from destination table by comparing records in source table.


Requirement: Inse

rt, Update, Delete records from destination table by comparing records in source table.

Update: if records exist in both the table compare them, and update value in destination table if value is different.

Insert: if record doesn't exist in destination table, add new record in destination table.

Delete: if record exist in destination table but not in source table, delete record from destination table.

----> I have created an SSIS package that does all this task but in order to perform delete operation, I have to store all records of destination table into a temporary table.
And since it's not the most efficient way, I have created another SSIS package by using Lookup & conditional Splits
method, now it works fine with inserting and updating information but I am not sure on how to handle removing record operation!

I would really appreciate if you could help me with this.

(In order to archive inserting and deleting records, I have referred following thread!
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx )

Help required regarding audit (Update/INSERT/DELETE) a table in Database.


Hi all


Help required regarding audit (Update/INSERT/DELETE) a table in Database.



I have table having with imp Data lets say ID, some of users are setting ID's to their own values with out informing us. I know it is not possible using Application what every my Database using.


Now I want find that who is doing this


CURSOR performance on multiple delete/insert and maybe poor table design


We have table documents and table documents_additonal_data, joined via key (docnum, doctype), important to know is that in documents_additonal_data we just have two more fileds valuetype and value (varchar(MAX)), and every record in doc can have variable 0-n valuetypes so there is no schema enforced in any way.

Now I've made cursor that needs to transfer history data from one older table to docs, must use cursor with order by clause because transaction_id, some bussines logic etc.  In short it takes values, have some IF logic on cursor variables, checks if there is already record for that document, some more IF logic on variables (with few simple selects) and calls insert procedure.

About insert procedure, it doesn't update values but reads current, stores old to XMLvalue (xml made with select) in storage table, deletes current, and inserts new data, reason for that is probably multiple indexes  (docs is primarly 'read from' table) and millions of records, update was slower probably because in add_data on update it usually needs to delete, insert, update in the same time. Insert procedure takes XML parameter for additional_data which is then translated and inserted via exec sp_xml_preparedocum

Clear FormView after Insert or Delete


How do I clear my formview after a record has been inserted or deleted? At the moment it just sits in the previous state with the same details in the fields, still with the Insert button. Either it would be good to drop the formview altogether (I have a grid view on the same page) or move into the edit mode for the just inserted record.

Also the same with the delete - the formview stays on screen with the deleted record details and the delete button still available - I know it has fired though as my gridview is updated.


Insert,Delete Update Operations with XML


i already bind my grid with xml file using dataset as dataset has property of reading xml

but now i want to perform operations on xml like Inserting of Data,Deleting of Data and Updating can anyone give me short tutoral how we can perform this thankx you

my xml

<?xml version="1.0" encoding="utf-8" ?>

Loading with Gridview

  private void loadxml()
        //throw new NotImplementedException();

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend