i have a probelm in store procedure, the problem is that when i want the record is same then update query execute and when i insert new record then execute insert query, but here is just work insert query, so plz how to spcify IF condintion in procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[GetDatanew]
(@xmlstr NTEXT,
@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)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @docHandle int
exec sp_xml_preparedocument @docHandle OUTPUT, @xmlstr
INSERT INTO cenmst(cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp)
SELECT *
FROM OPENXML (@docHandle, '/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
here is my code how can i pass parameter in Code please check my code
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 = "GetDataNew";
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;
}
}
elizabeth143
Member
17 Points
25 Posts
Update query not working when i run stored procedure
Jan 02, 2013 03:01 PM|LINK
i have a probelm in store procedure, the problem is that when i want the record is same then update query execute and when i insert new record then execute insert query, but here is just work insert query, so plz how to spcify IF condintion in procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[GetDatanew]
(@xmlstr NTEXT,
@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)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @docHandle int
exec sp_xml_preparedocument @docHandle OUTPUT, @xmlstr
UPDATE cenmst SET cennbr = @cennbr , fulnme = @fulnme, adr1 = @adr1, adr2 = @adr2, ctynme = @ctynme, stecde = @stecde,
zipcde = @zipcde, telnbr = @telnbr, faxnbr = @faxnbr, upddte = @upddte, updtim = @updtim, updusr = @updusr, [timestamp] = @timestamp, timstp = @timstp WHERE cennbr = @cennbr
INSERT INTO cenmst(cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp)
SELECT *
FROM OPENXML (@docHandle, '/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 @docHandle
END
TabAlleman
All-Star
15571 Points
2700 Posts
Re: Update query not working when i run stored procedure
Jan 02, 2013 04:00 PM|LINK
IF EXISTS (SELECT * FROM cenmst WHERE cennbr=@cennbr)
UPDATE cenmst SET ...
ELSE
INSERT INTO cenmst (....
elizabeth143
Member
17 Points
25 Posts
Re: Update query not working when i run stored procedure
Jan 02, 2013 05:11 PM|LINK
when i execute then give me the error like that
ALTER procedure [dbo].[GetDatanew]
(@xmlstr NTEXT,
@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)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @docHandle int
exec sp_xml_preparedocument @docHandle OUTPUT, @xmlstr
IF EXISTS (SELECT * FROM cenmst WHERE cennbr=@cennbr)
UPDATE cenmst SET cennbr = @cennbr , fulnme = @fulnme, adr1 = @adr1, adr2 = @adr2, ctynme = @ctynme, stecde = @stecde,
zipcde = @zipcde, telnbr = @telnbr, faxnbr = @faxnbr, upddte = @upddte, updtim = @updtim, updusr = @updusr, [timestamp] = @timestamp, timstp = @timstp WHERE cennbr = @cennbr
ELSE
INSERT INTO cenmst(cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp)
SELECT *
FROM OPENXML (@docHandle, '/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 @docHandle
END
Honnappa
Participant
1112 Points
534 Posts
Re: Update query not working when i run stored procedure
Jan 02, 2013 05:58 PM|LINK
U are not supplying the @cennbr parameter. pass the @cennbr parameter from code and it will work
My Profile
elizabeth143
Member
17 Points
25 Posts
Re: Update query not working when i run stored procedure
Jan 02, 2013 07:17 PM|LINK
here is my code how can i pass parameter in Code please check my code
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 = "GetDataNew";
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;
}
}
ramramesh
Member
458 Points
158 Posts
Re: Update query not working when i run stored procedure
Jan 03, 2013 02:26 PM|LINK
you asign and pass value 1 and 2 to your SP
for 1="inser",2="Update"
then check condition was if (Value==1)
//Inser
else
//Update
Good luck
TabAlleman
All-Star
15571 Points
2700 Posts
Re: Update query not working when i run stored procedure
Jan 03, 2013 04:18 PM|LINK
See this line in your code:
sqlcmd.Parameters.AddWithValue("@xmlstr", strxml);
This line is passing the value of strxml to your procedure parameter @xmlstr.
You need to do this with ALL of the parameters in your procedure.