InfoPath Dev

I have an Infopath for and I need to submit the whole form to a webservices which in turn submits xml to the stored procedure.  That stored procedure has to several OPENXML statements to insert the data to different tables and the other link tables.  I took the <my:myfields><my.field1></my.field1>....</my.myFields> (the main data source structure and created the stored procedure accordingly with out the my: ); the sproc works fine for the structure; but when i pass the entire form to the webservice; nothing gets inserted and throwing me the can not insert NULL into the link table(meaning no record is inserted in the other tables; thats why no ids are returned); so i am thinking may be its not the same structure, i think it was.  Can someone HELP me with this.  How can I intercept and see what gets transmitted to the webservice?  I googled and am not getting much information on this. So I am seeking experts help. 

I appreciate your help in this regard.

Hello,

Here is the below code to submit the xml,

string connectionString = "Get the connection string";

SqlConnection sqlConnection = new SqlConnection(connectionString);

SqlParameter sqlParameter = new SqlParameter("xmlDocument", <Inpur XML String>);

try

{

sqlConnection.Open();

SqlCommand sqlCommand = new SqlCommand("<Stored Procedure Name>", sqlConnection);

sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

sqlCommand.Parameters.Add(sqlParameter);

int index = sqlCommand.ExecuteNonQuery();

if (index > 0)

{

          statusMessage = "Successfully submitted to database.";

      }           

}

catch (Exception ex)

{

   statusMessage = ex.ToString();

}

Note: Stored procedure should be same as your schema structure of form.

Fiddler2 will allow you to inspect web requests.

I am new to this Infopath. I don't get what you are trying to say. I have exactly the same code you have given in my web services.  It accepts the xmldocument as the input parameter and calls the sproc with that.  That sproc has bunch of openxml insert statement to insert the data into multiple tables.  In the form's button, i have rule that submits to a webservice (with entire form as xmldocument).  You want me to put this code in the submit button's click event instead of submitting directly to the webservice? Please guide me thru this. Thanks.

Hello,

Whichever I gave the code is for web service. As you mentioned in the reply, you copied my code into web service and try once to submit the xml document using this web service.

Then you have to take the web service connection in InfoPath and pass your xml document to that method and submit it via rule or code.

Shiva,

   I already had this code in the webservice and tested with the sample xml structure which is similar to the forms structure and it works. after that only i created the rule in the button and selected 'Submit to the web service", gave my WSDL address, chose the web method, checked entire form as XML document option, clicked ok. I previewed the form and clicked the button and its not inserting any record in the DB. So i am thinking there might be some structure diff. between my sample & the xml doc. that gets passed by the form. How can intercept and view the structure?  Thats my problem.

PS: I tried fiddler2; it didn't catch the web session; because the form is developed on the same machine where the webserice reside. So using the htp://localhst:port/webservicename.asmx?WSDL to access the webservice.  

Hi, 

Try using only one parameter of xml data type in store procedure to submit entire form as XML. Because if you use multiple parameters in stored procedure, then you are not able to pass values to that parameters from InfoPath when submitting entire form as XML document.
I think this will work.

Hi BigMo and welcome,

You know this is why we created the Database Accelerator Web Service (DBXL) back in 2004 (now in it's 6th release - i.e. 2.3). You could have been done by now and thrown in document level permissioning, audit tracking, integration with ShP workflow, etc. in the extra time you saved. No coding required. Check out the free trial: http://www.infopathdev.com/files/folders/dbxldownload/entry33823.aspx.