.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

Problem inserting into XML column

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
I have a C# application that inserts XML into Sql Server 2005.  When the message is more than 300 lines or so, it doesn't make it there.  The data shows in the application variables (stringWriter) but doesn't get inserted and that's confirmed looking in Profiler. The stored procedure is simply:          usp_WS_OrderEntry_InsertWSOrder @doc=@p1,@WSOrderId=@p2 output       INSERT INTO WS_OrderEntry_Order (OrderXML)       VALUES(@doc)  ..... C# .....             tableAdapter.usp_WS_OrderEntry_InsertWSOrder(stringWriter.ToString(), ref wsOrderId); .... In any cases, the variable "stringWriter" contains the data (as text). In profiler for a successfull call: declare @p1 xml set @p1=convert(xml,N'<OrderHeader><CustomerNumber>1234</CustomerNumber><Pr.....</PO></ShipToName> declare @p2 int set @p2=1077 exec dbo.usp_WS_OrderEntry_InsertWSOrder @doc=@p1,@WSOrderId=@p2 output select @p2 In unsuccessfull call: It shokes on the convert part and doesn't display any data (and doesn't insert as well).  I looked in LOGs, events and didn't find anything. Any help appreciated.  Thanks.


View Complete Post


More Related Resource Links

Problem inserting a null value into date column in SQL table

  

Hi all,

I am having a hard time inserting a null date into a SQL table.  Please be aware that I am using the Microsoft Application Data Blocks and not pure ADO.net.  I find a million examples of how to do this with ADO.net but cannot find anything about this using the MS Data Application Blocks.

Here is my VB.net code:

Save(Session("ref_no"),  txtdischarge_date.Text) 

Sub Save(ByVal ref_no As Integer, discharge_date As Date)

        SqlHelper.ExecuteNonQuery(connString, ref_no, discharge_date), "MyTableInsert")

End Sub

The textbox txtdischarge_date can either be a valid date or blank.  If it contains a valid date then the insert works properly.  But if the textbox  is blank, then I want to insert a null value into my date column.  I cannot get this to work.  I get this error when I run this and txtdischarge_date is blank:

Conversion from string "" to type 'Date' is not valid

Here is the code for my stored procedure, MyTableInsert:

ALTER Procedure MyTableInsert
   (@ref_no int,
    @discharge_date datetime = NULL)

problem inserting using a stored procedure and identity column.

  

I recently moved a .net site from one machine to another, now for some reason one of the stored procedures is throwing an exception when attempting to insert! 

Exception Details: System.Data.SqlClient.SqlException: An explicit value for the identity column in table 'dbo.tbl_Events' can only be specified when a column list is used and IDENTITY_INSERT is ON

BTW, the column in question does have the identity set to Yes in management studio

I was using originally SQL 2005, now its on SQLexpress 2008

stored procedure:

ALTER

@EventID

PROCEDURE [dbo].[proc_EventsAddEdit]int,

@EventName

varchar(200),<

Fetch Identity column just after inserting a row in table

  
Hi, Please help me with this question. I have a table and I insert a row into it. How can i select the latest inserted row? Can the 'inserted table' keyword be used outside trigger? ( I mean can we use it in above scenario?? If yes how)   Thanks in advance Tiya

wpf charting column series data binding problem

  
I'm trying to build a chart using the columnSeries from the wpf toolkit and I appear to be having trouble with the data binding. Here is the xaml: <Grid>    <chartingToolkit:ColumnSeries Height="18" HorizontalAlignment="Left" Margin="188,169,0,0" Name="columnSeries1" VerticalAlignment="Top" Width="18" IndependentValueBinding="{Binding Path=Date}" DependentValueBinding="{Binding Path=Value}" /></Grid> In the code behind, I'm calling a stored procedure and putting the result in a datatable like this: string connString = ConfigurationManager.ConnectionStrings["string"].ConnectionString;            using (SqlConnection cn = new SqlConnection(connString))            {                DataTable dt = new DataTable("T1");                cn.Open();                SqlCommand cmd = new SqlCommand("T1_sp", cn);                cmd.CommandType = CommandType.StoredProcedure;                  SqlDataAdapter da = new SqlDataAdapter(cmd);                da.Fill(dt);                columnSeries1.ItemsSource = dt.DefaultView;                cn.Close();            } No error is being generated but I'm not getting a column chart either. If I take the same command and put it into a datagrid, it works fine. What do I need to do to get this into my column chart? If it helps,

Problem while retrieving data from a lookup column from sharepoint linq.

  
Hi I am working with sharepoint linq concept. When I am giving a query with lookup column It's working fine.  But when i am trying to retrieve the data from a look up column to spgridview. I was unable to get the output. Here is the above code which i have worked.   string strTitle = string.Empty; string strStatus=string.Empty; var context = new LinqSampleDataContext(SPContext.Current.Web.Url); EntityList<SharePoint2010ConceptsItem> Concept = context.GetList<SharePoint2010ConceptsItem>("SharePoint 2010 Concepts"); var query = from cs in Concept where cs.Status.Title.ToString()!="Completed" select new { cs.Title, cs.Id, //cs.Status }; sgvConcepts.DataSource =query; sgvConcepts.DataBind();   Any help me plz how to retrieve the data from a lockup column.bvnprasad

Problem with Retrieving data from lookup column using SharePoint 2010 Linq Concept

  
Hi I am working with sharepoint linq concept. When I am giving a query with lookup column It's working fine.  But when i am trying to retrieve the data from a look up column to spgridview. I was unable to get the output. Here is the above code which i have worked.   string strTitle = string.Empty; string strStatus=string.Empty; var context = new LinqSampleDataContext(SPContext.Current.Web.Url); EntityList<SharePoint2010ConceptsItem> Concept = context.GetList<SharePoint2010ConceptsItem>("SharePoint 2010 Concepts"); var query = from cs in Concept where cs.Status.Title.ToString()!="Completed" select new { cs.Title, cs.Id, //cs.Status }; sgvConcepts.DataSource =query; sgvConcepts.DataBind();   Any help me plz how to retrieve the data from a lockup column.bvnprasad

Problem when using calculated column as column group in a view

  
Hi,   I have a list with a calculated column named 'FullName', the formula is: "FirstName & " " &  LastName". I created a view group by 'FullName' column. The follow error appeared on the column:   &lt;!-- #RENDER FAILED --&gt;   Please help

GridView column/row data binding problem

  
Hi All, I have a table with the following structure: Attribute------------- AttributeIdWeekNoDayNoAttribute1Attribute2Attribute3Attribute4Attribute5 ........ likewise. I want to display these data in a gridview in the following manner                                  Sunday    Monday Tuesday ............ (till Saturday) - should be gridview columns, I can retrieve this using dayno Attribute 1 Attribute 2 Attribute 3 Attribute 4 .......... like wise attributes should be taken in to rows.Is this possible in a gridview, if so how can I do that?

Problem Facing While Inserting records into table which os dynamicaly created

  
Hi,I have created table into database dynamicaly,while inserting records into that dynamic table i faced problem.The name "STD000001" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. this error occured.I created dynamic table by ,declare @STUDENT_Table varchar(50)set @STUDENT_Table = [dbo].[GetStudentTable](@SchoolID)declare @cmd varchar(5000)Set @cmd = 'Create Table  ' + @STUDENT_Table + '(   [student_id] [varchar](10)NOT NULL,    [school_id] [varchar](50)NULL,    [class1] [varchar](50)NULL,    [division1] [varchar](50)NULL,    [educalis_id] [varchar](16)NULL,    [roll_no] [varchar](10)NULL,    [first_name] [varchar](50)NULL,    [middle_name] [varchar](50)NULL,    [last_name] [varchar](50)NULL,    [gender] [varchar](6)NULL,    [father_name] [varchar](50)NULL,    [mother_name] [varchar](50)NULL,    [father_office_address] [varchar](200)NULL,    [mother_office_address] [varchar](200)NULL,    [home_address] [varchar](200)NULL,    [father_mobile_no] [varchar](10)NULL,    [mother_mobile_no] [varchar](10)NU

Problem when Inserting xml data into a table using OpenXml

  
Hi, i stuck with a problemam trying to insert data from xml file to sql server.i made query like thisSET NOCOUNT ON DECLARE @hDoc INT EXEC sp_xml_preparedocument @hDoc OUTPUT, '<NewDataSet>  <xs:schema id=''NewDataSet'' xmlns='''' xmlns:xs=''http://www.w3.org/2001/XMLSchema'' xmlns:msdata=''urn:schemas-microsoft-com:xml-msdata''>  <xs:element name=''Job''>     <xs:complexType>      <xs:sequence>        <xs:element name=''BillToNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''Colors'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CompanyNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CsrCode'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CsrName'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CustomerPoNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''DivisionOriginal'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''EstimateNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''FinalDeliveryDate'' type=''xs:string'' minOccurs=''0'' /> <xs:element name

Problem with an XML type column in this SQL Statement

  

If I use an XML type variable (@xml) everything is fine with the following SQL:

SELECT p.number as Position,
  x.value('(../name)[1]','VARCHAR(200)') AS SectionName,
  x.value('(./label)[1]','VARCHAR(200)') AS Name
FROM master..spt_values p
CROSS APPLY @xml.nodes('/xxxx_settings/section/setting[position()=sql:column("number")]') n(x) where p.type = 'p'

However, when I try to store my XML data in a table that has an XML column the following query doesn't work:

SELECT p.number as Position,
  x.value('(../name)[1]','VARCHAR(200)') AS SectionName,
  x.value('(./label)[1]','VARCHAR(200)') AS Name
FROM master..spt_values p,
  dbo.XmlDataHolder a
CROSS APPLY a.XmlData.nodes('/xxxx_settings/section/setting[position()=sql:column("p.number")]') n(x) where p.type = 'p'

The error is "The column prefix 'p' does not match with a table name or alias name used in the query."

What am I missing here?


Problem with getting extra data in column of gridview

  

I have created a gridview based on an SQL Datasource.  The grid view contains several columns(fields) one of which is called "Amount".  In the footer of the gridview their is a field that contains the sum of all of the values contained in the "Amount" field.

By problem is that in instead of getting the value of the Amount displayed in the "Amount" field of each row, I am getting a '0' (Example: 0 25.78 when I should just be getting 25.78.  There is a space between the '0' and the '25.78'.

Code is as follows:

 

<asp:TemplateField HeaderText="Amount" SortExpression="Amount">

problem with inserting records??

  

When i am inserting my data i am getting this error 

An explicit value for the identity column in table 'emp' can only be specified when a column list is used and IDENTITY_INSERT is ON.


i have set Identitty specification and is identity of the table as yes so i am getting this problem whenevr i set it to no i am not getting any error,can anyone explain me why i am getting this error??


help on triggers inserting record problem?

  

I have created a trigger and i want to insert a record in emp table whenever i insert i must get the confirmation that record is added i want to do this

using triggers.

USE [mine]
GO
/****** Object:  Trigger [dbo].[insert2]    Script Date: 10/03/2010 09:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[insert2]  
   on [dbo].[emp]  
    for insert  
    as  
    declare @sno int,@result varchar(50),@empid int,@name varchar(50)  
    select @empid=empid,@name=@name from emp where @empid=empid 
    insert into triglog values (@empid,@name)  

above is my code emp is my primary table and triglog is the table where i want to get the confirmation that data is aded when a new record is inserted into emp,actually i want to know when a new record is added? is there anyway to know this when i execute the code im getting error like inserting null values into sno and result but how to pull the values from emp and enter it in triglog im confused help with this??thanks.


trigger problem inserting null values??

  

I have created a trigger for insert and whenever i insert the data the trigger should fire and notify some value inserted i want to create log table to save this information,now i have created a trigger

USE [mine]
GO
/****** Object: Trigger [dbo].[insert2]  Script Date: 10/03/2010 15:00:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[insert2] 
  on [dbo].[emp] 
  after insert 
  as 
  declare @sno int,@result varchar(50),@empid int,@name varchar(50) 
select @empid=empid,@name=@name from emp  
 insert into triglog values(@empid,@name)  
  
  
but when

Problem inserting ID value from one SQL table to another using CreateUserWizard

  

Hi,

I am designing a registration form where I use CreateUserWizard. I am inserting data into two different tables while use is created. First table is Categories (columns: CustomerID, CatCode) where I insert CatCodes based on selections made in CheckBoxList and second table is Customers (columns: CustomerID, FName, LName, Email) where I register personal details (first, last name, email etc). CustomerID is generated automatically when client registers.

What I want to do is to connect Categories and Customers tables so when client selects CatCodes during registration and selected values are written into the Categories table CatCode column, appropriate CustomerID to be written there as well (in CustomerID column which is a ForeignKey from Cusomers table).

For example: Client is registered, his CustomerID (1001) is created during registration. He also selected three categories (1,2,3) which are written into the Categories table. Now as I have CustomerID in Categories Table as well (ForeignKey from Customer Table CustomerD column), I want generated CustomerID to show up in Categories table so i could later identify what catgories did client 1001 selected.

Category codes (CatCode) is inserted using code behind file in C#:

 protected void CreateUserWizard1_CreatedUser(obje

Problem with translation of column names

  

Hi,

we have a Sharepoint 2007 Installation with german language packs.

I created a new english site collection and inside the new collection a new document library.
And there I have the problem. The system columns like "modified by" and "modified" have german names: "Geändert von" and "Geändert".

Any idea how I can solve this problem?

Thanks in advance
Joe


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