declare @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
insert into cenmst (cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp)
select cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp
from (SELECT * FROM OPENXML(@hDoc, '/mDoc/mTrans/mSet/mRow/mCol', 1)
WITH ( id VARCHAR(11),VALUE VARCHAR(50) 'text()')) as sourceTable pivot (min(value) for id
in ([CenNbr],[FulNme],[Adr1],[Adr2],[CtyNme],[SteCde],[ZipCde],[TelNbr],[FaxNbr],[UpdDte],[UpdTim],[UpdUsr],[TimeStamp],[TimStp]))AS P
SELECT t.* FROM cenmst t
LEFT OUTER JOIN cenmst c
ON t.cennbr = c.cennbr WHERE c.cennbr IS NULL
UPDATE t SET cennbr=t.cennbr, fulnme=t.fulnme, adr1=t.adr1, adr2=t.adr2, ctynme=t.ctynme, stecde=t.stecde,
zipcde=t.zipcde, telnbr=t.telnbr, faxnbr= t.faxnbr, upddte=t.upddte, updtim=t.updtim, updusr=t.updusr,
[timestamp]= t.[timestamp],timstp = t.timstp
FROM cenmst t JOIN cenmst c ON t.cennbr = c.cennbr where t.cennbr=@cennbr
EXEC sp_xml_removedocument @hDoc
END
updating query is not working but insert query is working well, i want to update duplicate row data on same row ,
here is error
error: Procedure or function 'GetDatanew' expects parameter '@cennbr', which was not supplied.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[GetDatanew] (@xmlstr ntext, @cennbr char(6) = Null) as BEGIN if (@cennbr = Null) declare @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr insert into cenmst (cennbr, fulnme, adr1,
adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp) select cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp from (SELECT * FROM OPENXML(@hDoc, '/mDoc/mTrans/mSet/mRow/mCol',
1) WITH ( id VARCHAR(11),VALUE VARCHAR(50) 'text()')) as sourceTable pivot (min(value) for id in ([CenNbr],[FulNme],[Adr1],[Adr2],[CtyNme],[SteCde],[ZipCde],[TelNbr],[FaxNbr],[UpdDte],[UpdTim],[UpdUsr],[TimeStamp],[TimStp]))AS P UPDATE cenmst SET cenmst.fulnme
= xmlcenmst.FulNme, cenmst.adr1 = xmlcenmst.Adr1, cenmst.adr2 = xmlcenmst.Adr2, cenmst.ctynme = xmlcenmst.CtyNme, cenmst.stecde = xmlcenmst.SteCde, cenmst.zipcde = xmlcenmst.ZipCde, cenmst.telnbr = xmlcenmst.TelNbr, cenmst.faxnbr = xmlcenmst.FaxNbr, cenmst.upddte
= xmlcenmst.UpdDte, cenmst.updtim = xmlcenmst.UpdTim, cenmst.updusr = xmlcenmst.UpdUsr, cenmst.[timestamp] = xmlcenmst.[TimeStamp], cenmst.[timstp] = xmlcenmst.[TimStp] FROM OPENXML(@hDoc, '/mDoc/mTrans/mSet/mRow/mCol', 1) WITH ( fulnme char(36), adr1 char(36),adr2
char(36), ctynme char(20),stecde char(2), zipcde char(10),telnbr char(14), faxnbr char(14),upddte char(8), updtim char(8), updusr char(8), [timestamp] int, timstp int) xmlcenmst WHERE cennbr = @cennbr EXEC sp_xml_removedocument @hDoc END insertion at a time
working correctly but when i again same value insert into table it give me error Violation of PRIMARY KEY constraint 'PK_cenmst'. Cannot insert duplicate key in object 'dbo.cenmst'. please fixed that issue for me thanks sir
From your error message, it seems you set PK_cenmst in your cenmst table, when you update your table, the primary key column value is not unique, so the update didn't working. Please make sure the primary key column's values should be unique in your table,
otherwise, it will occur a contraints error. Please check your table value before you update your table.
Best Regards,
Please mark the replies as answers if they help or unmark if not.
Feedback to us
*************************************** store procedure is here ******************************************************
USE [plrmaa]
GO
/****** Object: StoredProcedure [dbo].[GetDatanew] Script Date: 12/28/2012 19:22:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetDatanew]
(@xmlstr ntext)
as BEGIN
declare @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
select cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp
from (SELECT * FROM OPENXML(@hDoc, '/mDoc/mTrans/mSet/mRow/mCol', 1)
WITH ( id VARCHAR(11),VALUE VARCHAR(50) 'text()')) as sourceTable pivot (min(value) for id
in ([CenNbr],[FulNme],[Adr1],[Adr2],[CtyNme],[SteCde],[ZipCde],[TelNbr],[FaxNbr],[UpdDte],[UpdTim],[UpdUsr],[TimeStamp],[TimStp]))AS P
EXEC sp_xml_removedocument @hDoc
END
************************************* here is code **************************************
app_code/service.cs code here
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Text;
using System.Configuration;
using System.Xml.Linq;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class Service : System.Web.Services.WebService
{
SqlConnection con;
SqlDataAdapter adap;
DataSet ds, ds1;
string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
[WebMethod]
public DataSet webservices()
{
con = new SqlConnection(@"Data Source=ZUBAIRKHALIL;Initial Catalog=plrmaa;Integrated Security=True;MultipleActiveResultSets=True");
ds = new DataSet();
ds.ReadXml(Server.MapPath("MDEMO_data.xml"));
string strxml = XDocument.Load(Server.MapPath("MDEMO_data.xml")).ToString();
SqlConnection sqlconn = new SqlConnection(connStr);
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.Connection = sqlconn;
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.CommandText = "GetData";
sqlcmd.Parameters.AddWithValue("@xmlstr", strxml);
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
adap = new SqlDataAdapter("select * from CenMst", con);
ds1 = new DataSet();
adap.Fill(ds1, "CenMst");
return ds1;
zubair258
Member
112 Points
129 Posts
updation query not working in store procedure please correct the error
Dec 21, 2012 01:29 PM|LINK
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetDatanew]
(@xmlstr ntext,
@cennbr char(6) = 'heeo')
as BEGIN
declare @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
insert into cenmst (cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp)
select cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp
from (SELECT * FROM OPENXML(@hDoc, '/mDoc/mTrans/mSet/mRow/mCol', 1)
WITH ( id VARCHAR(11),VALUE VARCHAR(50) 'text()')) as sourceTable pivot (min(value) for id
in ([CenNbr],[FulNme],[Adr1],[Adr2],[CtyNme],[SteCde],[ZipCde],[TelNbr],[FaxNbr],[UpdDte],[UpdTim],[UpdUsr],[TimeStamp],[TimStp]))AS P
SELECT t.* FROM cenmst t
LEFT OUTER JOIN cenmst c
ON t.cennbr = c.cennbr WHERE c.cennbr IS NULL
UPDATE t SET cennbr=t.cennbr, fulnme=t.fulnme, adr1=t.adr1, adr2=t.adr2, ctynme=t.ctynme, stecde=t.stecde,
zipcde=t.zipcde, telnbr=t.telnbr, faxnbr= t.faxnbr, upddte=t.upddte, updtim=t.updtim, updusr=t.updusr,
[timestamp]= t.[timestamp],timstp = t.timstp
FROM cenmst t JOIN cenmst c ON t.cennbr = c.cennbr where t.cennbr=@cennbr
EXEC sp_xml_removedocument @hDoc
END
updating query is not working but insert query is working well, i want to update duplicate row data on same row ,
here is error
christiandev
Star
8607 Points
1841 Posts
Re: updation query not working in store procedure please correct the error
Dec 21, 2012 03:38 PM|LINK
try setting the default for @cennbr to null and then checking the value against null...
if (@cennbr = null)
set @cennbr = 'heeo'
Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)
zubair258
Member
112 Points
129 Posts
Re: updation query not working in store procedure please correct the error
Dec 21, 2012 05:37 PM|LINK
zubair258
Member
112 Points
129 Posts
Re: updation query not working in store procedure please correct the error
Dec 22, 2012 11:57 AM|LINK
can you solve my problem please ?
nikunjnandan...
Participant
882 Points
223 Posts
Re: updation query not working in store procedure please correct the error
Dec 23, 2012 06:12 AM|LINK
Hiii,
From error it shows that you are not passing those parameters.
please check you code whether you are passing paramaeter or not??
Here is http://nikunjnandaniya.blogspot.in/2011/11/insert-and-update-data-using-xml-file.html for demo code for using xml to insert update data.
Nikunj Nandaniya
My Blog
Chen Yu - MS...
All-Star
21584 Points
2493 Posts
Microsoft
Re: updation query not working in store procedure please correct the error
Dec 27, 2012 07:52 AM|LINK
Hi,
From your error message, it seems you set PK_cenmst in your cenmst table, when you update your table, the primary key column value is not unique, so the update didn't working. Please make sure the primary key column's values should be unique in your table, otherwise, it will occur a contraints error. Please check your table value before you update your table.
Best Regards,
Feedback to us
Develop and promote your apps in Windows Store
zubair258
Member
112 Points
129 Posts
Re: updation query not working in store procedure please correct the error
Dec 28, 2012 01:28 PM|LINK
the clinet make database structre and the table column set just priamary key not auto update the column
here is colun script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[cenmst](
[cennbr] [char](6) NOT NULL,
[fulnme] [char](36) NULL,
[adr1] [char](36) NULL,
[adr2] [char](36) NULL,
[ctynme] [char](20) NULL,
[stecde] [char](2) NULL,
[zipcde] [char](10) NULL,
[telnbr] [char](14) NULL,
[faxnbr] [char](14) NULL,
[upddte] [char](8) NULL,
[updtim] [char](8) NULL,
[updusr] [char](6) NULL,
[timestamp] [int] NULL,
[timstp] [int] NULL,
CONSTRAINT [PK_cenmst] PRIMARY KEY NONCLUSTERED
(
[cennbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
*************************************** store procedure is here ******************************************************
USE [plrmaa]
GO
/****** Object: StoredProcedure [dbo].[GetDatanew] Script Date: 12/28/2012 19:22:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetDatanew]
(@xmlstr ntext)
as BEGIN
declare @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
UPDATE cenmst
SET cenmst.cennbr = xmlcenmst.CenNbr,
cenmst.fulnme = xmlcenmst.FulNme,
cenmst.adr1 = xmlcenmst.Adr1,
cenmst.adr2 = xmlcenmst.Adr2,
cenmst.ctynme = xmlcenmst.CtyNme,
cenmst.stecde = xmlcenmst.SteCde,
cenmst.zipcde = xmlcenmst.ZipCde,
cenmst.telnbr = xmlcenmst.TelNbr,
cenmst.faxnbr = xmlcenmst.FaxNbr,
cenmst.upddte = xmlcenmst.UpdDte,
cenmst.updtim = xmlcenmst.UpdTim,
cenmst.updusr = xmlcenmst.UpdUsr,
cenmst.[timestamp] = xmlcenmst.[TimeStamp],
cenmst.[timstp] = xmlcenmst.[TimStp]
FROM OPENXML(@hDoc, '/mDoc/mTrans/mSet/mRow/mCol')
WITH (cennbr char(6), fulnme char(36), adr1 char(36),adr2 char(36),
ctynme char(20),stecde char(2), zipcde char(10),telnbr char(14), faxnbr char(14),upddte char(8),
updtim char(8), updusr char(8), [timestamp] int, timstp int) xmlcenmst
WHERE cenmst.cennbr = xmlcenmst.cennbr
insert into cenmst (cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp)
select cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp
from (SELECT * FROM OPENXML(@hDoc, '/mDoc/mTrans/mSet/mRow/mCol', 1)
WITH ( id VARCHAR(11),VALUE VARCHAR(50) 'text()')) as sourceTable pivot (min(value) for id
in ([CenNbr],[FulNme],[Adr1],[Adr2],[CtyNme],[SteCde],[ZipCde],[TelNbr],[FaxNbr],[UpdDte],[UpdTim],[UpdUsr],[TimeStamp],[TimStp]))AS P
EXEC sp_xml_removedocument @hDoc
END
************************************* here is code **************************************
app_code/service.cs code here
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Text;
using System.Configuration;
using System.Xml.Linq;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class Service : System.Web.Services.WebService
{
SqlConnection con;
SqlDataAdapter adap;
DataSet ds, ds1;
string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
[WebMethod]
public DataSet webservices()
{
con = new SqlConnection(@"Data Source=ZUBAIRKHALIL;Initial Catalog=plrmaa;Integrated Security=True;MultipleActiveResultSets=True");
ds = new DataSet();
ds.ReadXml(Server.MapPath("MDEMO_data.xml"));
string strxml = XDocument.Load(Server.MapPath("MDEMO_data.xml")).ToString();
SqlConnection sqlconn = new SqlConnection(connStr);
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.Connection = sqlconn;
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.CommandText = "GetData";
sqlcmd.Parameters.AddWithValue("@xmlstr", strxml);
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
adap = new SqlDataAdapter("select * from CenMst", con);
ds1 = new DataSet();
adap.Fill(ds1, "CenMst");
return ds1;
}
}
************************************************** service.asmx *****************************
<%@ WebService Language="C#" CodeBehind="~/App_Code/Service.cs" class="Service" %>
*********************************************** here is XML file ***************************
<?xml version="1.0" encoding="UTF-8" ?>
<mDoc>
<mTrans id="Transfer Request">
<mSet id="WbrTransfer">
<mRow id="CenMst">
<mCol id="CenNbr">MDEMO</mCol>
<mCol id="FulNme">NM</mCol>
<mCol id="Adr1">610 FREEDOM BUSINESS CENTER</mCol>
<mCol id="Adr2"></mCol>
<mCol id="CtyNme">KOP</mCol>
<mCol id="SteCde">PA</mCol>
<mCol id="ZipCde">19406</mCol>
<mCol id="TelNbr">(610)265-6606</mCol>
<mCol id="FaxNbr">(610)337-6107</mCol>
<mCol id="UpdDte">1</mCol>
<mCol id="UpdTim">1</mCol>
<mCol id="UpdUsr">12</mCol>
<mCol id="TimeStamp">12</mCol>
<mCol id="TimStp">1235</mCol>
</mRow>
</mSet>
</mTrans>
</mDoc>
please solve my problem the error is same, what the solution of that problem ?