I have rewritten my code to clear things up and it has exposed that I
am not actually merging the data successfully. If anyone can spot the
reason why from the below code I would seriously appreciate the help.
namespace PortalBANK.DesktopModules
{
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
/// <summary>
/// Summary description for Importer.
/// </summary>
public abstract class Importer : PortalBANK.PortalModuleControl
{
protected System.Web.UI.WebControls.DataGrid Datagrid1;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.DataGrid Datagrid2;
protected System.Web.UI.WebControls.DataGrid Datagrid3;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void Button1_Click(object sender, System.EventArgs e)
{
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='c:\\biztest2.xls'" + ";" +
"Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection
string.
OleDbConnection excelConn = new OleDbConnection(sConnectionString);
// Open connection with the database.
excelConn.Open();
// The code to follow uses a SQL SELECT command to display the data
from the worksheet.
// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect =new OleDbCommand("Select * from
[Sheet1$]", excelConn);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter daExcel = new OleDbDataAdapter();
// Pass the Select command to the adapter.
daExcel.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet dsExcel = new DataSet();
// Fill the DataSet with the information from the worksheet.
daExcel.Fill(dsExcel, "XLData");
// Bind data to DataGrid control.
Datagrid1.DataSource = dsExcel.Tables[0].DefaultView;
Datagrid1.DataBind();
excelConn.Close();
//ATTEMPT THE SAME FOR A SQL CONNECTION
string myConnectionString = "Provider=SQLOLEDB;Data
Source=localhost;Initial Catalog=BANK;" +
"User ID=xxx;Password=xxx";
// Create connection object by using the preceding connection
string.
OleDbConnection sqlConn = new OleDbConnection(myConnectionString);
// Open connection with the database.
sqlConn.Open();
// The code to follow uses a SQL SELECT command to display the data
from the worksheet.
// Create new OleDbCommand to return data from worksheet.
OleDbCommand objSQLSelect2 =new OleDbCommand("Select
AccountNo,TransType,TransDate,TransValue,CandRec,Comments,BusinessID,BatchID
from businesstransaction", sqlConn);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter daSQL = new OleDbDataAdapter();
//ATTEMPT TO CREATE TABLE MAPPING
DataTableMapping sqlMap = daSQL.TableMappings.Add("Table",
"BusinessTransaction");
// Pass the Select command to the adapter.
daSQL.SelectCommand = objSQLSelect2;
// Create new DataSet to hold information from the worksheet.
DataSet dsSQL = new DataSet();
// Fill the DataSet with the information from the worksheet.
daSQL.Fill(dsSQL, "SQLData");
//ATTEMPT TO FORCE ACCEPTANCE OF CHANGES n.b. this prevents
changing of rowstates if only updating
dsSQL.AcceptChanges();
// Bind data to DataGrid control.
Datagrid2.DataSource = dsSQL.Tables[0].DefaultView;
Datagrid2.DataBind();
sqlConn.Close();
//ATTEMPT TO MERGE THEN OUTPUT TO A DATAGRID
dsExcel.Merge(dsSQL, false, MissingSchemaAction.AddWithKey);
if (dsSQL.HasChanges())
{
Trace.Warn("boolean","yes");
}
else
{
Trace.Warn("boolean","no");
}
try
{
daSQL.Update(dsSQL);
}
catch (Exception exception)
{
Trace.Warn("exception", (exception.ToString()));
}
Datagrid3.DataSource = dsSQL.Tables[0].DefaultView;
Datagrid3.DataBind();
}
}
}
