.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

DataSet/DataTable to SqlXml SqlDbType

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :ASP.Net


how would i convert DataSet/DataTable to SqlXml SqlDbType and save it to Sql Server Table?

I'm using Sql Server 2008 R2.

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
    if (ViewState["dsTest"] != null)
        DataTable dt = ((DataSet)ViewState["dsTest"]).Tables["Info"];
        SqlXml Qxml = new SqlXml();
        //convert DataTable to SqlXml[Qxml]
        e.Command.Parameters["@Info"].Value = Qxml;

    <asp:Parameter Name="Info" Type="Object" />
Whatever i try, i get back error:

System.Data.SqlClient.SqlException: Operand type clash: sql_variant is incompatible with xml


Thanks for the help.

View Complete Post

More Related Resource Links

Data Points: DataSet and DataTable in ADO.NET 2.0


ADO. NET 2. 0 sports some exciting enhancements to the core classes found in ADO. NET 1. x and introduces a variety of new classes, all of which promise to improve performance, flexibility, and efficiency.

John Papa

MSDN Magazine November 2005

delete a row in datatable using linq to dataset

hihow can i delete a row in datatable using linq to dataset?I have a dataset in my project, need to delete some rows filtered by IDhow can i do?

Crystal Report with DataSet, DataTable (Having Problems)

Hello All,     I am a bit new with Crystal Reporting and I was given the Job to take an exisitng report , modify it and load it on a website... the report part is done but the loading is been a pain. So here is what i did,    protected void Button1_Click(object sender, EventArgs e) { SqlConnection cn = new SqlConnection("server=ss;uid=sss;pwd=ss;database=sss"); cn.Open(); #region quer //Query Here, This is working fine #endregion ReplaceEscape(qry); SqlCommand cmd = new SqlCommand(qry, cn); cmd.CommandTimeout = 1000; cmd.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet myDataSet = new DataSet(); da.Fill(myDataSet, "DSAllStatQuery"); ReportDocument cryRpt = new ReportDocument(); cryRpt.SetDataSource(myDataSet); cryRpt.Load(@"C:\Users\erodriguez\Documents\Visual Studio 2010\WebSites\AllStats\CrystalReport.rpt"); CrystalReportViewer1.ReportSource = cryRpt; cn.Close(); } public static string ReplaceEscape(string str) { str = str.Replace("'", "''"); return str; } I crea

Returning DataSet/Datatable from Webmethod to Javascript

I have a simple web method written in the code-behind (not a separate asmx) of a test aspx page. All it does is return a DataTable(or DataSet) both are failing, gives a blank 500 error saying "There was an error processing the request" If I switch the return variable type to String, it works fine I think my issue has to do with the version of the System.Web.Extensions, is that the assembly that actually contains the JSON communication implementation? I've read all kinds of tutorials about how it should be possible to return the DataTable to javascript and then read the properties with javascript syntax. For some reason it isn't working for me. My web extensions is imported through my web.config with the following version <add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>

Xmlnode to Dataset/DataTable (GetListItems Sharepoint Webservices)


How to convert XmlNode to Dataset or Datatable and below is my code..

 DataSet dsList = new DataSet();

            Web_Reference.Lists listService = new Web_Reference.Lists();

            listService.PreAuthenticate = true;
            NetworkCredential Cred = new NetworkCredential();
            Cred.Domain = Domain;
            Cred.UserName = Login;
            Cred.Password = password;

            listService.Credentials = Cred;

            string listName = "{0F775034-47EA-486F-97AE-50C5BD9011B0}";
            string viewName = "{8F441557-E75B-4BAC-A3EF-B61FF0351DBE}"; ;
            XmlDocument xmlDoc = new System.Xml.XmlDocument();

            System.Xml.XmlNode ndQuery = xmlDoc.CreateNode(System.Xml.XmlNodeType.Element, "Query", "");
            System.Xml.XmlNode ndViewFields = xmlDoc.CreateNode(System.Xml.XmlNodeType.Element, "ViewFields", "");
            System.Xml.XmlNode ndQueryOptions = xmlDoc.CreateNode(System.Xml.XmlNodeType.Element, "QueryOp

How to return datatable from a typed dataset using WCF service and Jun CTP ?


How to return datatable from a typed dataset using WCF service/



       In one of my application I am using the Typed Dataset that contains many tables. I want to return a particular Datatable form the dataset as following.


Case 1. The following code is working, but the problem is I can not use .EnforceConstraints = false property as this property only available with Dataset.  (I want to use this property because I want to return only selected fields from the table and rest of the fields may have not null columns)

Adding rows to a databound DataSet/DataTable from a ThreadPool thread does not update UI


I have an WPF .NET 4.0 application which creates a TreeView in the main thread, and databinds it to a DataSet which contains two DataTables. The datatables have a simple relationship between them which represents a two-level tree. When the treeview is initially drawn, it correctly displays all items from the two datatables as a two level tree.

When the DataTables are updated from the UI thread (add/delete/modify a row), the treeview is refreshed and the changes to the databound DataSet DataTables are visible in the treeview.

If changes to the DataTables are made from a ThreadPool thread, no exceptions are thrown, but the UI is not updated. I am using a threadpool thread because the changes will be initiated from multiple network devices and processing their data is fairly CPU intensive.

I am assuming that change notifications are not being propogated across threads for DataSets/DataTables.  Is this assumption correct? If so, are their recommended best practices to overcome this limitation?

VS2010 Prof restart after new dataset and then dragging datatable


Dear all,

I am using VS2010 Prof on Windows XP SP3 on MySQL.

When I new item (DataSet), drag the DataTable.Then wizard prompt up for choosing connection and sql query. When I paste below SQL and then click OK, it restart. Do you know Why??? Anything that I am wrong???


FROM retail_transaction RT INNER JOIN trans_item_line TIL
WHERE RT.type_id <> 2
 AND ((RT.Start_DateTime >= ? AND RT.Start_DateTime <=?) OR 1=?)

How to filter XML recored with out using datatable or dataset.


hi there,

               i want filter data with out using Dataset or datatable, any one help me.



thanks to ypu

Introducing ADO.NET and the Typed DataSet

In reviewing the applications it seemed everything they were doing involved database reads, writes, and manipulation. The development staff had been reviewing the articles and code samples for ADO.NET. They had spent many years refining their existing ADO based infrastructure and perfecting their use of ADO

Create and Use a Typed DataSet by Using Visual C# .NET

This article shows you how to create and use a typed DataSet in a small Web application. Typed DataSets, which inherit from the DataSet class, create first-class members of a DataSet's DataTables and DataColumns, yielding the many benefits that come with the use of strong typing.

Get Top 1 Record from Dataset

Many of them in need of getting the Top 1 or Top 10 records from a Dataset without going to SQL query. It is possible to do from Code Behind. I have given the code below,
The below code is to get single record from a Dataset. Also I have given for getting 8 records at Green Color.

Returning a DataSet From a Web Services: Step-by-Step

This Example Shows how to create a webservice which returns a DataSet and a Client Which displays the DataSet in a Grid.


This Example Shows how to create a webservice which returns a DataSet and a Client Which displays the DataSet in a Grid.

How to bind GridView and DropDownList controls using DataTable object

In .net framework 2.0, you can load a DataTable in memory by consuming a data source using a DataReader. In this version you will find the flexibility in accessing the DataReader means write access code once and use again and again using DataReader. To see the functionality of DataTable consider an example that is created using VS2005.

DataTable - Load from IDataReader - CreateDataReader - ReadXml WriteXml - Free ADO.NET Tutorials

The DataTable in ADO.NET 2.0 has been seriously beefed up and I absolutely love it. Not everything we consume deals with multiple resultsets and requires the ever popular DataSet. With the cool new features in the ADO.NET 2.0 DataTable, we can get many of the cool features in the DataSet without all the extra baggage and memory footprint particular to multiple resultsets.

Load DataTable from IDataReader

Persist large dataset across ASP.NET pages. No database


Can anyone give me suggestions on how to implement the following requirement?

1. A online wizard (step-by step operation) processes user requests across several ASP.NET pages.  The session data is stored in memory until the user submits the request.  Once the request is submitted, a XML file is created and the data are passed to XML.

# Case Study: The parent page has a "select" button.  Click "select" will pop up a child page.  The users can select 1000+ items from a datagrid in the child page.  Once the user clicks "confirm", the selected items in the child page will be populated to the selected item gridveiw in the parent page.  The user can keep selecting more in the child page and the selected items will be appended to the gridview in the parent page.


- What's the best way to extract strings from a text file and convert to a dataset in memory and then present it in a datagrid in UI?

- What's the best way to persist and manipulate a large dataset across pages?  Session? or ViewState? Can it be accomplished by LINQ?

The app doesn't interact with a DB.  Once the request is submitted, it will convert the in memory data to a XML file.  What technology can be used to accomplished this?&nb

Datatable in session


I am creating a simple shopping cart using a datatable that is passed into a session object.  I have a small mini cart that displays the total number of items in the cart as well as the running total - this is displayed in a web control.  When the web control starts up I run a try/catch to see if the session object exists, if it doesn't I create the datatable for the cart and store it in the session.  I then have a datalist control that contains all the information for my products including an "add to cart" button.  When the page loads everything looks fine.  If I click on an "add to cart" button nothing happens to the session information.  If I click on it again, the session information updates - displaying only one item is in the cart and then counts up correctly there after.  Additionally, after adding an item, if I select a different item in the list, the same issue occurs.  I am not sure why this is happeing, if anyone can give me an idea I would appreciate it. 

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