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


Top 5 Contributors of the Month
Sharon Maxwell
Post New Web Links

How to insert or update XML columns

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
 
 --Works ok to update XML by explicitly entering XML, or one-by-one:    
declare @XMLNODE table (id int identity, doc xml)
    insert @XMLNODE (doc) values ('<Root><Elements><Items><OldItem><ID>1</ID><Show Pointer="yes" /><Display Pointer="Display"><Detail1>some Details</Detail1></DisplayDetails></OldItem></Items>      </Elements></Root>'  )
  --This is just an XML variable to get the output, but not related to the ID in table:      
 
DECLARE @XMLOutput XML
  &n


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.

Insert/Update OLE Attachment(Binary datatype) using SQL server 2000

  
Hi All, In the same senario how we can Insert/Update OLE Attachment(Binary datatype)  from one source table to other destination table by using SQL Server 2000 not with C# fronend code. Appreciated if you can give fix on this regads, Divakar.

INSERT statement with OUTPUT clause, referencing outer query columns

  
I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

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.  

Insert the results of a stored procedure into specific columns

  
Hello, I'm trying to update a table with values from a stored procedure. Here is what I've got: Open My_Cursor DECLARE @username varchar(200), @add1 varchar(200), @add2 varchar(200), @city varchar(200), @st varchar(200), @zip varchar(200) Fetch NEXT FROM MY_Cursor INTO @username WHILE @@FETCH_STATUS = 0 BEGIN insert into sheet1$ (f19,f20,f21,f22,f23) EXEC [dbo].[GetPrimaryEEsAddress] @dependentUsername = @username WHERE username = @username FETCH NEXT FROM MY_Cursor INTO @username END Getting error: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'WHERE'.

Insert columns into GridView and rendercontrol error

  
Hi I´m inserting columns into a gridview at run time using: gvRageReport.Columns.Insert(index, column) When I render the gridview in orther to export to excel all the excel columns are empty except the new insert columns, but when I add the new columns using gvRageReport.Columns.Add(column) the excell is exported successfully.Any idea?

update/insert parent child at the same time

  
Hi Al, Is there a way to create a contact form that takes the name. address and other info for the master record; Then, somehow, on the same page, allow the user to enter multiple contact info as chid records (i.e. phone, email, etc.) All this using the Objec DataSource or SQL DataSource (which ever is easy) and doesn't require writing too much code. Any reference material welcomed. Thanks, rod.

Possible to insert & update in formview?

  
I have a formview where DefaultMode="Insert". Underneath, I have a gridview that displays the information from the inserts. Is there a way to update the information as well as insert? Maybe there is a better way to do what I'm trying to do. Any help is appreciated. Thanks!

Possible to update and insert from formview?

  
I have a formview where DefaultMode="Insert". Underneath, I have a gridview that displays the information from the inserts. Is there a way to update the information as well as insert? Maybe there is a better way to do what I'm trying to do. Any help is appreciated. Thanks!

Concurrent INSERT and UPDATE

  
I created a table with the following columns in SQL server 2008 express version,   create table action (id int identity , comment varchar(256), description varchar(max), opendate date, closedate date) I was trying to verify whether inserts will slow down once the table has large number of records. Hence i started inserting in loop (30 times) 10000 records. This was done using Microsoft jdbc driver ver 4.0. The time taken was almost in the range 8-10 seconds. In between i also tried to update the 'description' column for all the rows from sql client window. This affected the insert operations and it took 31 seconds for inserting one set of 10,000 rows. Please suggest how to handle such scenarios. Is the slowness because of the entire table update?

EntityDataSource Insert, Update, and ConvertEmptyStringToNull

  
I've noticed that when using an EntityDataSource with FormViews, GridViews, and other databound controls, that empty strings are not always converted to null for inserts and updates. This isn't an issue when using templated DynamicControls, but when normal WebControls are bound to the datasource. For example: <asp:TextBox ... Text='<%# Bind("NullableColumnName") %>'  During inserts and updates, when the text box has an empty string, the value saved to the table cell in the db is an empty string rather than NULL. This is avoidable if normal <asp:Parameter Name="NullableColumnName" Type="String" /> parameters are added to the InsertParameters and UpdateParameters collections, since the default property of ConvertEmptyStringToNull is true. Is there another way to ensure empty strings get converted to null, other than using DynamicControls with EDS? I'm just curious what is happening behind the scenes here. Data values of type Int32 are automatically converted to null when they are bound to an empty string, as are datetime values. It only seems to be normal strings that get pushed to the EDM without conversion to null. Any explanations?

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, _

SQL Checking the table before insert or update

  
I need your help with a SQL Stored Procedure PLEASE!!!GOAL: I need to check TableA for the ItemID if the ItemID Is Not found Insert a new record. If the ItemID Is found Update existing record.***********************Below is Incorrect***********************Please HELP!!!!IF @ItemID is not null          Set NewRecord =  Select ItemID FROM ContentItems Where EXISTS  (select ItemID from ContentForm_Fields where ItemID=@ItemID)IF NewRecord is not null   Update ContentForm_FieldsELSE Insert ContentForm_Fields

update and insert together

  
hi experts I have the table "@temp" that contian id. and another table "myTable" that contain id, counter the field of id in both tables is equal. but not exists all @temp.id in the myTable.id. i want to 1 - increment counter if @temp.id exist in the myTable (@temp.id = myTable.id) 2 - and if the id not exists in the myTable, then create new row for the id and set counter to 1 i know i should update using inner join for state one,but what about another one?. i looking for a fast and optimized solution. Do using the Cursor is good way or there is a better way? Best Regards. Morteza  

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?  

Insert-Update to SAP from SharePoint - Best Approach in terms of development and deployment

  
Hi, I am having a requirement where I need to design and develop a workflow solution that interacts with the SAP to insert/update the data. Here are few questions that I am looking for some guidance... 1) If I develop the Insert-update operations using BCS and a web-service (or WCF) in SharePoint designer, how to use it with workflow ? Also , the stuff that is configured using SharePoint designer , how to deploy it on Staging and Production? 2) If the above approach has quite a few limitations, then what should be the best approach for such requirement?   Thanks.  

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.
Categories: 
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