Web Services

Re: Not able to insert multi rows from InfoPath to SQL Server using WebServices

Patrick Halstead — Mon, 10 Jan 2011 15:44:27 GMT

Dude, that code doesn't add more than one so it won't work for repeating. This is a lot more difficult than you think. It's the reason we developed a Web Service back in 2004 that does this for arbitrarily deep repeating XML structures. It's not something you can code up in a day or two. Think about it.

Re: Not able to insert multi rows from InfoPath to SQL Server using WebServices

Amigo1982 — Mon, 10 Jan 2011 14:52:03 GMT

Hi Patrick

Thanks for your quick reply, i m passing it as just parameter here is the code of my WS, its calling my stored procedure and inserting one record only which it is the first record in the repeating section in my InfoPath and stop, do i need to do loop here or do anything in InfoPath?

[WebMethod]
    public bool UpdateHREducation(String EmplID, String Education, String Desc, DateTime StartDate, DateTime EndDate)
    {
        string strCommandText = "dbo.ALS_spUpdateHREducation";

        using (SqlConnection conn = new
        SqlConnection("server=10.0.11.47 ;database=DynamicsAX1;uid=info;pwd=info123456"))
        {
            SqlCommand cmd = new SqlCommand(strCommandText, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter paramEmplID = new SqlParameter("@EMPLID", SqlDbType.NVarChar);
            paramEmplID.Value = EmplID;
            cmd.Parameters.Add(paramEmplID);
            SqlParameter paramEdu = new SqlParameter("@Education", SqlDbType.NVarChar);
            paramEdu.Value = Education;
            cmd.Parameters.Add(paramEdu);
            SqlParameter paramDesc = new SqlParameter("@Desc", SqlDbType.NVarChar);
            paramDesc.Value = Desc;
            cmd.Parameters.Add(paramDesc);
            SqlParameter paramStartDate = new SqlParameter("@StartDate", SqlDbType.DateTime);
            paramStartDate.Value = StartDate;
            cmd.Parameters.Add(paramStartDate);
            SqlParameter paramEndDate = new SqlParameter("@EndDate", SqlDbType.DateTime);
            paramEndDate.Value = EndDate;

conn.Open();

            int i = 0;
            i = cmd.ExecuteNonQuery();
            conn.Close();
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

Re: Not able to insert multi rows from InfoPath to SQL Server using WebServices

Patrick Halstead — Mon, 10 Jan 2011 14:40:32 GMT

Hi Amigo1982 and welcome,

Does your Web Service take the entire XML or just a parameter? To pass it a repeating structure may require passing the entire string to the WS and having it map it to SQL. Qdabra Software, sponsor of this site, provides a database Web Service called DBXL that allows you to set up a mapping like this. It supports arbitrarily deep mappings to SQL. Go to Qdabra.com->Products and try a free trial download. It will save you time and money.

Not able to insert multi rows from InfoPath to SQL Server using WebServices

Amigo1982 — Mon, 10 Jan 2011 14:34:01 GMT

I Have created WS which get and update SQL Server DB , and i have created InfoPath form to connect to this web service.

Now everything is working fine and no errors , but when i click submit only the first row in the repeating section is updated in the Database and others are ignored.

Anyone having idea what could be the problem?