updation query not working in store procedure please correct the error RSS

• • Reply
  

  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

  error: Procedure or function 'GetDatanew' expects parameter '@cennbr', which was not supplied.

   

  Zubair258
  • Moved by tmorton on Dec 21, 2012 02:43 PM
• • Reply
  

  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'

  Please remember to Mark As Answer if helpful
  
  Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)
  
• • Reply
  

  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

  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
  Zubair258
• • Reply
  

  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 ?

  Zubair258
• • Reply
  

  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.

  Regards,
  Nikunj Nandaniya
  My Blog
• • Reply
  

  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

  zubair258

  PRIMARY KEY constraint 'PK_cenmst'. Cannot insert duplicate key in object 'dbo.cenmst'.

  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,

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store
  
  
• • Reply
  

  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 ?

  Zubair258