Unable to Update Multiple Records Using OpenXML

Posted by Sharpcnet under C# on 3/9/2014 | Points: 10 | Views : 679 | Status : [Member] | Replies : 2
Trying to update multiple records in SQL Server 2008 R2. There are 2 columns to update - AppraisalId & Feedback, after matching the ActivityId. There are no errors, but the update isn't happening. I can see the xml string with data. Where am I going wrong?

Code-behind :

btnSave_Click()
{
dataset ds = new dataset();
datatable dt = new datatable;

dt.columns.add("ActivityId");
dt.columns.add("AppraisalId");
dt.columns.add("Feedback");

foreach(gridviewrow gr in gvmain.rows)
{
hiddenfield hd = (hiddenfield)gr.findcontrol("hdactivityid");
dropdownlist ddl = (dropdownlist)gr.findcontrol("ddlappraisal");
textbox txt = (textbox)gr.findcontrol("txtfeedback");

datarow dr = dt.newrow();
dr["activityid"] = guid.parse(hd.value);
dr["appraisalid"] = ddl.selectedindex > 0 ? ddl.selectedvalue : null;
dr["feedback"] = txt.text.trim();

dt.rows.add(dr);
}

ds.tables.add(dt);
objDAL.SaveAppraisals(ds.GetXml());
}


DAL:

public void SaveAppraisals(string xmldoc)
{
sqlcommand cmd = new sqlcommand("SaveAppraisals",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@xmldoc", xmldoc);
con.open();
cmd.executenonquery();
con.close();
}


SQL:

alter procedure SaveAppraisals
@xmldoc xml
as
begin
set nocount on;
declare @doc int

exec sp_xml_preparedocument @doc output,@xmldoc

update tblActivity
set AppraisalId = ox.AppraisalId,
Feedback=ox.Feedback
from openxml(@doc,'NewDataSet/Table1', 2)
with(ActivityId uniqueidentifier, AppraisalId int, Feedback) ox
where tblActivity.ActivityId = ox.ActivityId

exec sp_xml_removedocument @doc
end





Responses

Posted by: Bandi on: 3/10/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down

Add Try..catch inside SaveAppraisals and check whether procedure is executing or not


This link might help you
http://www.c-sharpcorner.com/uploadfile/17e8f6/updating-multiple-records-in-sql-server-by-using-openxml-method-or-by-using-dataadapter-in-Asp-Net/


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 3/10/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer
http://support.microsoft.com/kb/315968

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response