Justin Jacob

Justin Jacob

  • NA
  • 7
  • 2.1k

WCF wth XML Parameter

Jun 11 2013 1:54 PM
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..

Answers (5)