Hi Paresh,
After seeing that web service is the only way to go, I created one.
However I have problem submitting data still. Below is the
specifications of the problem. I really need help with this.
Please provide any help for the following problem. Thanks a lot in
advance.
I have 2 tables in SQL Server 2005 database that has parent-child
relationship: Project table contains info of all projects with
ProjectID (string) as primary key, Product table contains all products
info of all projects with NumRow (int, auto-increment) as primary key
and ProjectID as foreign key to Project table.
I have created an InfoPath 2007 browser-compatible form based on web
service built in VS 2005 and deployed in Sharepoint 2007 server. The
web service webmethod GetProject takes the form's parameter(Project
ID) to query the database, and display both Project & Products info
associated with that Project ID. This was successful. After the
details are displayed, the users make changes and add more products
via repeating section with controls and hit SUBMIT. This is where
InfoPath gives an error:
---------------------------------
The SOAP response indicates that an error occurred on the server:
Server was unable to process request. ---> Value cannot be null.
Parameter name: dataRows
---------------------------------
The SUBMIT button submits the DataSet to the web service webmethod
UpdateProject which takes 2 parameters, the DataSet and the
projectID. Below is the code for the web service:
using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;
[WebService(Namespace = "
http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class PMWebService : System.Web.Services.WebService
{
public PMWebService()
{
//Uncomment the following line if using designed components
//InitializeComponent();
}
//[WebMethod]
//public string HelloWorld() {
// return "Hello World";
//}
[WebMethod]
public DataSet GetProject(string projectID)
{
SqlConnection dbConnection = new
SqlConnection("server=xxxxxx;uid=wwpsadmin;pwd=xxxxxxxxx;database=theProjec
tHistory;");
SqlDataAdapter projectCommand = new SqlDataAdapter("select *
from Project where ProjectID = '" + projectID + "'", dbConnection);
SqlDataAdapter productCommand = new SqlDataAdapter("select *
from Product where ProjectID = '" + projectID + "'", dbConnection);
DataSet ds = new DataSet();
try
{
//Fill DataSet, and then set DataRelation to move through
the DataGrid.
dbConnection.Open();
projectCommand.FillSchema(ds, SchemaType.Mapped,
"Project");
projectCommand.Fill(ds, "Project");
productCommand.FillSchema(ds, SchemaType.Mapped,
"Products");
productCommand.Fill(ds, "Products");
ds.Relations.Add("ProjectMetrics",
ds.Tables["Project"].Columns["ProjectID"],
ds.Tables["Products"].Columns["ProjectID"]);
DataColumn dc =
ds.Tables["Project"].Columns["ProjectID"];
//dc.AutoIncrement = true;
//dc.AutoIncrementSeed = -1;
//dc.AutoIncrementStep = -1;
}
catch (SqlException ex)
{
Console.Write(ex.Message.ToString());
Console.Write(ex.InnerException.ToString());
}
return ds;
}
[WebMethod]
public DataSet UpdateProject(string projectID, DataSet ds)
{
SqlConnection dbConnection = new
SqlConnection("server=xxxxxxxx;uid=wwpsadmin;pwd=xxxxxxxx;database=theProje
ctHistory;");
SqlDataAdapter projectCommand = new SqlDataAdapter("select *
from Project where ProjectID = '" + projectID + "'", dbConnection);
SqlDataAdapter productCommand = new SqlDataAdapter("select *
from Product where ProjectID = '" + projectID + "'", dbConnection);
try
{
dbConnection.Open();
// Get commands for the Project table.
// Reselect record after insert to get new Identity
value.
// You must get the schema, which you did in GetData(),
before you get commands;
// otherwise, the Command builder tries to insert new
rows, based
// on the Identity column.
SqlCommandBuilder cb = new
SqlCommandBuilder(projectCommand);
projectCommand.DeleteCommand = cb.GetDeleteCommand();
projectCommand.UpdateCommand = cb.GetUpdateCommand();
projectCommand.InsertCommand = cb.GetInsertCommand();
projectCommand.InsertCommand.CommandText =
String.Concat(projectCommand.InsertCommand.CommandText, "; Select *
From Project Where ProjectID = @@IDENTITY");
//UpdateRowSource tells the DataAdapter that there will be
a re-selected record.
projectCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
//cb = null;
// Get commands for the Order Details table.
// Must set the QuotePrefix and QuoteSuffix;
// otherwise, the CommandBuilder does not put brackets
([])
// around the table name.
SqlCommandBuilder cb1 = new
SqlCommandBuilder(productCommand);
cb1.QuotePrefix = "[";
cb1.QuoteSuffix = "]";
productCommand.DeleteCommand = cb1.GetDeleteCommand();
productCommand.InsertCommand = cb1.GetInsertCommand();
productCommand.UpdateCommand = cb1.GetUpdateCommand();
// Create a new DataAdapter based on the original one to
prevent the
// CommandBuilder from modifying the SQL statements,
// specifically the custom InsertCommand.
// You do not need this if you roll your own commands and
parameters
// or if you use the Visual Tools to do it.
SqlDataAdapter projComm = new SqlDataAdapter();
projComm.DeleteCommand = projectCommand.DeleteCommand;
projComm.InsertCommand = projectCommand.InsertCommand;
projComm.UpdateCommand = projectCommand.UpdateCommand;
// Use a delegate to prevent AcceptChanges from occurring
on Deletes and Inserts.
// This is for a limitation of the DataAdapter; see
Q313540.
projComm.RowUpdated += new
SqlRowUpdatedEventHandler(OnProjectRowUpdated);
productCommand.RowUpdated += new
SqlRowUpdatedEventHandler(OnDetailsRowUpdated);
productCommand.Update(GetDeletedRows(ds.Tables["Product"]));
projComm.Update(GetDeletedRows(ds.Tables["Project"]));
DataRow[] dsArray = ds.Tables["Project"].Select("", "",
DataViewRowState.ModifiedCurrent);
projComm.Update(ds.Tables["Project"].Select("", "",
DataViewRowState.ModifiedCurrent));
productCommand.Update(ds.Tables["Product"].Select("", "",
DataViewRowState.ModifiedCurrent));
projComm.Update(ds.Tables["Project"].Select("", "",
DataViewRowState.Added));
ds.EnforceConstraints = false;
productCommand.Update(ds.Tables["Product"].Select("", "",
DataViewRowState.Added));
ds.EnforceConstraints = true;
dbConnection.Close();
}
catch (SqlException ex)
{
Console.Write(ex.Message.ToString());
Console.Write(ex.InnerException.ToString());
}
return ds;
}
protected static void OnProjectRowUpdated(object sender,
SqlRowUpdatedEventArgs args)
{
if (args.StatementType == StatementType.Insert ||
args.StatementType == StatementType.Delete)
args.Status = UpdateStatus.SkipCurrentRow;
}
protected static void OnDetailsRowUpdated(object sender,
SqlRowUpdatedEventArgs args)
{
if (args.StatementType == StatementType.Insert)
{
// Do not allow the AcceptChanges to occur on this row.
args.Status = UpdateStatus.SkipCurrentRow;
// Get the current, actual primary key value so that you
can plug it back
// in after you get the correct original value that was
generated for the child row.
string currentkey = (string)args.Row["ProjectID"];
// This is where you get a correct original value key that
is stored to the child row.
// You pull the original, pseudo key value from the
parent, plug it in as the child row's primary key
// field, and then accept changes on it. Specifically,
this is why you turned off EnforceConstraints.
args.Row["ProjectID"] =
args.Row.GetParentRow("ProjectMetrics")["ProjectID",
DataRowVersion.Original];
args.Row.AcceptChanges();
// Store the actual primary key value in the foreign key
column of the child row.
args.Row["ProjectID"] = currentkey;
}
if (args.StatementType == StatementType.Delete)
args.Status = UpdateStatus.SkipCurrentRow;
}
private DataRow[] GetDeletedRows(DataTable dt)
{
DataRow[] dr;
if (dt == null)
return null;
dr = dt.Select("", "", DataViewRowState.Deleted);
if (dr.Length == 0 || dr[0] != null)
return dr;
// Workaround:
// With a remoted DataSet, Select returns the array elements
// that are filled with Nothing/null instead of DataRow
objects.
for (int i = 0; i < (int)dt.Rows.Count; i++)
{
if (dt.Rows
.RowState == DataRowState.Deleted)
dr = dt.Rows;
}
return dr;
}