I am using WCF to create a small application in which I am using XML Parameter for inserting data into SQL Server..
But I am getting the following error while saving.. Can anyone help on this.. since I am new into wcf and xml..
"Cannot find either column "c" or the user-defined function or aggregate "c.c.value", or the name is ambiguous."
I have written an sp like this..
ALTER PROC [dbo].[usp_Mast_UserActions]
(
@P_DocAction INT,
@P_XMLData VARCHAR(MAX),
@P_UserId VARCHAR(20),
@P_ErrMsg VARCHAR(500) OUT
)
AS
BEGIN
DECLARE @XML XML
SELECT @XML = CAST(@P_XMLData AS XML)
IF (@P_DocAction = 0)
BEGIN
SELECT *
FROM UserMast
WHERE UserId LIKE + @P_UserId
END
IF (@P_DocAction = 1)
BEGIN
INSERT INTO [UserMast]
([UserId],[UserName],[Password],[Gender],[DOB],[Address],[State],[Country]
,[Email],[MobileNo],[LastModifiedBy],[LastModifiedOn],[Deleted])
SELECT
u.c.value('(UserId/text())[1]', 'VARCHAR(20)') AS UserId,
u.c.value('(UserName/text())[1]', 'VARCHAR(50)') AS UserName,
u.c.value('(Password/text())[1]', 'VARCHAR(10)') AS Password,
u.c.value('(Gender/text())[1]', 'INT') AS Gender,
u.c.value('(DOB/text())[1]', 'DATETIME') AS DOB,
u.c.value('(Address/text())[1]', 'VARCHAR(200)') AS Address,
u.c.value('(State/text())[1]', 'INT') AS State,
u.c.value('(Country/text())[1]', 'INT') AS Country,
u.c.value('(Email/text())[1]', 'VARCHAR(50)') AS Email,
u.c.value('(MobileNo/text())[1]', 'NUMERIC(18,0)') AS MobileNo,
u.c.value('(LastModifiedBy/text())[1]', 'VARCHAR(10)') AS LastModifiedBy,
u.c.value('(LastModifiedOn/text())[1]', 'DATETIME') AS LastModifiedOn,
u.c.value('(Deleted/text())[1]', 'INT') AS Deleted
FROM @XML.nodes('UserMast') u(c)
END
END
This is the content of wcf service.
public void SetData(UserMast objUser)
{
DataAccess da = new DataAccess();
da.SetData(objUser);
}
public void SetData(UserMast objUser)
{
General objGeneral = new General();
objGeneral.OpenConnection();
objGeneral.SCmd.CommandText = "dbo.usp_Mast_UserActions";
SqlParameter P_DocAction = new SqlParameter("@P_DocAction", SqlDbType.Int);
P_DocAction.Value = 1;
objGeneral.SCmd.Parameters.Add(P_DocAction);
SqlParameter P_XmlData = new SqlParameter("@P_XMLData", SqlDbType.VarChar,2000);
P_XmlData.Value = objGeneral.SerializeObject(objUser);
objGeneral.SCmd.Parameters.Add(P_XmlData);
SqlParameter P_UserId = new SqlParameter("@P_UserId", SqlDbType.VarChar, 20);
P_UserId.Value = "";
objGeneral.SCmd.Parameters.Add(P_UserId);
SqlParameter P_ErrMsg = new SqlParameter("@P_ErrMsg", SqlDbType.VarChar, 20);
P_ErrMsg.Direction = ParameterDirection.Output;
P_ErrMsg.Value = "";
objGeneral.SCmd.Parameters.Add(P_ErrMsg);
objGeneral.SCmd.CommandType = CommandType.StoredProcedure;
objGeneral.SCmd.ExecuteNonQuery();
}
This is the content in form...
private void btnSave_Click(object sender, EventArgs e)
{
//ServiceReference1.EDiaryServiceClient objUserClient = new
ServiceReference1.EDiaryServiceClient("WSHttpBinding_IEDiaryService");
EDiaryService objEDiaryService = new EDiaryService();
//IList<Customer> c = new List<Customer>();
UserMast objUser2 = new UserMast();
objUser2.UserId = txtUserId.Text;
objUser2.UserName = txtName.Text;
objUser2.Password = txtPWD.Text;
objUser2.MobileNo = Convert.ToInt32(txtMobile.Text);
objUser2.Email = txtEmail.Text;
objUser2.State = 0;
objUser2.Country = 0;
objUser2.Gender = cboGender.Text;
objUser2.DOB = dateTimePicker1.Value;
objUser2.Address = txtAddres.Text;
objUser2.LastModifiedBy = "";
objUser2.LastModifiedOn = DateTime.Now;
objUser2.Deleted = 0;
objEDiaryService.SetData(objUser2);
}
public string SerializeObject(Object objPar)
{
string retval = null;
if (objPar != null)
{
StringBuilder sb = new StringBuilder();
using (XmlWriter writer = XmlWriter.Create(sb, new XmlWriterSettings() { OmitXmlDeclaration = true }))
{
new XmlSerializer(objPar.GetType()).Serialize(writer, objPar);
}
retval = sb.ToString();
}
return retval;
}
Can anyone help on this.. since I am new into wcf and xml..