OK, here's the C# code from the web service ...
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
namespace dsdata
{
[System.Web.Services.WebService(Namespace="
http://dsdata.us/webservices/")]
public class primary : System.Web.Services.WebService
{
public primary()
{
//CODEGEN: This call is required by the ASP.NET Web Services Designer
InitializeComponent();
}
#region Component Designer generated code
//Required by the Web Services Designer
private IContainer components = null;
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if(disposing && components != null)
{
components.Dispose();
}
base.Dispose(disposing);
}
#endregion
[WebMethod]
public System.String GetData(System.DateTime StartDateTime)
{
System.String strResult;
System.String strConnection =
System.Configuration.ConfigurationSettings.AppSettings["primary"];
System.Data.OleDb.OleDbConnection objConnection = new
System.Data.OleDb.OleDbConnection(strConnection);
System.Data.OleDb.OleDbCommand objCommand = new
System.Data.OleDb.OleDbCommand(
"SELECT * FROM tblAbsence WHERE RecordEntered > ?",objConnection);
System.Data.OleDb.OleDbParameter objParam =
objCommand.CreateParameter();
objParam.DbType = System.Data.DbType.DateTime;
objParam.Direction = System.Data.ParameterDirection.Input;
objParam.OleDbType = System.Data.OleDb.OleDbType.Date;
objParam.ParameterName = "RecordEntered";
objParam.Value = StartDateTime;
objCommand.Parameters.Add(objParam);
System.Data.OleDb.OleDbDataAdapter objAdapter = new
System.Data.OleDb.OleDbDataAdapter(objCommand);
System.Data.DataSet objDataset = new DataSet("Absences");
objAdapter.Fill(objDataset, "Absences");
System.Xml.XmlDataDocument objSource = new
System.Xml.XmlDataDocument(objDataset);
System.Xml.Xsl.XslTransform objTransform = new
System.Xml.Xsl.XslTransform();
System.String strTransform =
System.Configuration.ConfigurationSettings.AppSettings["transform"];
objTransform.Load(strTransform);
System.IO.StringWriter sw = new System.IO.StringWriter();
objTransform.Transform(objSource, null, sw,
new System.Xml.XmlUrlResolver());
sw.Close();
strResult = sw.ToString();
strResult = System.Text.RegularExpressions.Regex.Replace(strResult,
">\\W*</z:row>", "/>");
objDataset.Dispose();
objAdapter.Dispose();
objCommand.Dispose();
objConnection.Dispose();
return strResult;
}
}
}
And here is the XSLT that transforms the XML. Unfortunately, this part is
very specific to the particluar data being transformed, this is where the
majority of the work will probably be required to adapt this to your needs
...
<xsl:stylesheet version='1.0'
xmlns:xsl='
http://www.w3.org/1999/XSL/Transform'>
<xsl
utput method='xml' omit-xml-declaration = 'yes' indent='yes'/>
<xsl:template match='/'>
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:AttributeType name='TeacherID' rs:number='1' rs:nullable='true'
rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rs
recision='10'
rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='StudentID' rs:number='2' rs:nullable='true'
rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='8'/>
</s:AttributeType>
<s:AttributeType name='AbsenceDate' rs:number='3' rs:nullable='true'
rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16'
rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='AbsenceTypeID' rs:number='4' rs:nullable='true'
rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rs
recision='10'
rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='AbsenceReason' rs:number='5' rs:nullable='true'
rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='AbsenceNotes' rs:number='6' rs:nullable='true'
rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='RecordEntered' rs:number='7' rs:nullable='true'
rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16'
rs:fixedlength='true'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<xsl:for-each select='Absences/Absences'>
<xsl:element name='z:row' namespace='#RowsetSchema'>
<xsl:attribute name='TeacherID'>
<xsl:value-of select='TeacherID'/>
</xsl:attribute>
<xsl:attribute name='StudentID'>
<xsl:value-of select='StudentID'/>
</xsl:attribute>
<xsl:attribute name='AbsenceDate'>
<xsl:value-of select='AbsenceDate'/>
</xsl:attribute>
<xsl:attribute name='AbsenceTypeID'>
<xsl:value-of select='AbsenceTypeID'/>
</xsl:attribute>
<xsl:attribute name='AbsenceReason'>
<xsl:value-of select='AbsenceReason'/>
</xsl:attribute>
<xsl:attribute name='AbsenceNotes'>
<xsl:value-of select='AbsenceNotes'/>
</xsl:attribute>
<xsl:attribute name='RecordEntered'>
<xsl:value-of select='RecordEntered'/>
</xsl:attribute>
</xsl:element>
</xsl:for-each>
</rs:data>
</xml>
</xsl:template>
</xsl:stylesheet>
And finally, the VBA code that calls the toolkit-created class
("clsws_primary"), uses the returned XML string as the source for an ADO
recordset, and uses that recordset to write the data to the local database
...
Private Sub cmdUpdate_Click()
Dim rstMaxDate As ADODB.Recordset
Dim dtmMaxDate As Date
Dim objPrimary As clsws_primary
Dim strXML As String
Dim objStream As ADODB.Stream
Dim rstSource As ADODB.Recordset
Dim rstDest As ADODB.Recordset
Dim fldSource As ADODB.Field
Dim fldDest As ADODB.Field
DoCmd.Hourglass True
Set rstMaxDate = New ADODB.Recordset
With rstMaxDate
.ActiveConnection = CurrentProject.Connection
.Open "SELECT Max(RecordEntered) as MaxDate FROM tblAbsences"
If Not IsNull(.Fields("MaxDate")) Then
dtmMaxDate = .Fields("MaxDate")
End If
.Close
End With
Set objPrimary = New clsws_primary
strXML = objPrimary.wsm_GetData(dtmMaxDate)
'Debug.Print strXML
Set objPrimary = Nothing
Set objStream = New ADODB.Stream
objStream.Open
objStream.WriteText strXML
objStream.Position = 0
Set rstSource = New ADODB.Recordset
rstSource.Open objStream
objStream.Close
Set objStream = Nothing
Set rstDest = New ADODB.Recordset
With rstDest
.ActiveConnection = CurrentProject.Connection
.Open "SELECT * FROM tblAbsences", , adOpenDynamic,
adLockOptimistic
End With
Do Until rstSource.EOF
rstDest.AddNew
For Each fldSource In rstSource.Fields
For Each fldDest In rstDest.Fields
If fldDest.name = fldSource.name Then
fldDest.Value = fldSource.Value
Exit For
End If
Next fldDest
Next fldSource
On Error Resume Next
rstDest.Update
If Err <> 0 Then
rstDest.CancelUpdate
End If
On Error GoTo 0
rstSource.MoveNext
Loop
rstDest.Close
rstSource.Close
Me.sfrAbsenceAll.Form.Requery
DoCmd.Hourglass False
End Sub
--
Brendan Reynolds
nycdon said:
Thanks much Brendan..and sure, if you could post the c# sharp that would
be
great!
Don
