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