A C# OleDb SQL Cunundrum

[Atlantistwo]

Can someone tell me why this SQL Command at the end is not working? I am a noob, but I have tried everything I can think of and I am getting nowhere. There is no literature on how to do something like this... It would have to be my first project LOL

I am trying to create an append program to merge identical Access databases populated at different client locations. I would not have used Access personally, but they didn't ask me...

OleDbConnection CNCTOld = new OleDbConnection(@"provider = microsoft.jet.oledb.4.0; data source = c:\MDR\MDR_BE\mdr_be.mdb;");
OleDbConnection CNCTNew = new OleDbConnection(@"provider = microsoft.jet.oledb.4.0;" + "data source = " + DBPath + ";");
CNCTNew.Open();
CNCTOld.Open();
string cmdStr1 = "Select * from tbl_dat_RecordsGen";
OleDbDataAdapter DA1 = new OleDbDataAdapter(cmdStr1, CNCTNew);
OleDbDataAdapter DA2 = new OleDbDataAdapter(cmdStr1, CNCTOld);
DataSet DS1 = new DataSet();
DataSet DS2 = new DataSet();
DA1.Fill(DS1, "tbl_dat_RecordsGen");
DA2.Fill(DS2, "tbl_dat_RecordsGen");
DataTable DTNew = DS1.Tables["tbl_dat_RecordsGen"];
DataTable DTOld = DS2.Tables["tbl_dat_RecordsGen"];
OleDbCommand StrSQL = CNCTOld.CreateCommand();
StrSQL.CommandText = "INSERT INTO " + DTOld + " SELECT * FROM " + DTNew + " WHERE (" + DTOld + ".AuditID <> " + DTNew + ".AuditID) OR (" + DTOld + ".ChartNo <> " + DTNew + ".ChartNo) OR (" + DTOld + ".ChartID <> " + DTNew + ".ChartID)";

[Ramy Mahrous]

give us the exception

[Atlantistwo]

It is not throwing an exception... it just does not do anything. The primary database is unchanged.

[Ramy Mahrous]

that's for sure, you don't execute your command

StrSQL.ExecuteNonQuery();

[Atlantistwo]

That is the next line down. It is executing and I exen have a msg box showing me the full statement... It IS being executed though.

My next step is going to be to try to make SQL recognize the full path to each database...

[Ramy Mahrous]

you don't execute your command!!!!!!!!!!!!!!!!!
execute it, if there is execption pass me it.

[Atlantistwo]

I told you... I AM executing it and there is NO exception being thrown. There is NO change in the database however. For some reason I don't think it is recognizing the differences in the databases and I don't know how to differentiate between the two.

[Ramy Mahrous]

drop your code here all of your code

[Atlantistwo]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using System.Data.SqlClient;
using System.Runtime.Remoting.Messaging;
using System.Data.OleDb;



namespace MDRAppend
{
public partial class Form1 : Form
{
String FN1;
public Form1()
{
InitializeComponent();
}


private void button1_Click(object sender, EventArgs e)
{
if (TxtOpenDB.Text == "")
{
MessageBox.Show ("Please select a Database to append.");
}
else
{
/*Open Appending DB and fill a DT with the data*/
string DBPath = TxtOpenDB.Text;
//string CNCTOld = @"provider = microsoft.jet.oledb.4.0; data source = c:\MDR\MDR_BE\mdr_be.mdb;";
//string CNCTNew = @"provider = microsoft.jet.oledb.4.0;" + "data source = " + DBPath + ";";
OleDbConnection CNCTOld = new OleDbConnection(@"provider = microsoft.jet.oledb.4.0; data source = c:\MDR\MDR_BE\mdr_be.mdb;");
OleDbConnection CNCTNew = new OleDbConnection(@"provider = microsoft.jet.oledb.4.0;" + "data source = " + DBPath + ";");
CNCTNew.Open();
CNCTOld.Open();
string cmdStr1 = "Select * from tbl_dat_RecordsGen";
OleDbDataAdapter DA1 = new OleDbDataAdapter(cmdStr1, CNCTNew);
OleDbDataAdapter DA2 = new OleDbDataAdapter(cmdStr1, CNCTOld);
DataSet DS1 = new DataSet();
DataSet DS2 = new DataSet();
DA1.Fill(DS1, "tbl_dat_RecordsGen");
DA2.Fill(DS2, "tbl_dat_RecordsGen");
DataTable DTNew = DS1.Tables["tbl_dat_RecordsGen"];
DataTable DTOld = DS2.Tables["tbl_dat_RecordsGen"];
OleDbCommand StrSQL = CNCTOld.CreateCommand();
// ORIGINAL = StrSQL.CommandText = "INSERT INTO " + DTOld + " SELECT * FROM " + DTNew + " INNER JOIN " + DTOld + " ON (tbl_dat_RecordsGen.AuditID <> tbl_dat_RecordsGen.AuditID) AND (tbl_dat_RecordsGen.ChartNo <> tbl_dat_RecordsGen.ChartNo) AND (tbl_dat_RecordsGen.ChartID <> tbl_dat_RecordsGen.ChartID)";



//*MUST use datatables objects
//StrSQL.CommandText = "INSERT INTO " + DTOld + " SELECT * FROM " + DTNew + " INNER JOIN " + DTOld + " ON (" + DTOld + ".ChartID <> " + DTNew + ".ChartID) WHERE (" + DTOld + ".AuditID <> " + DTNew + ".AuditID) OR (" + DTOld + ".ChartNo <> " + DTNew + ".ChartNo) OR (" + DTOld + ".ChartID <> " + DTNew + ".ChartID)";
StrSQL.CommandText = "INSERT INTO " + DTOld + " SELECT * FROM " + DTNew + " WHERE (" + DTOld + ".AuditID <> " + DTNew + ".AuditID) OR (" + DTOld + ".ChartNo <> " + DTNew + ".ChartNo) OR (" + DTOld + ".ChartID <> " + DTNew + ".ChartID)";
MessageBox.Show (StrSQL.CommandText);
StrSQL.ExecuteNonQuery();


foreach (DataRow DR in DTOld.Rows)
{
MessageBox.Show (DR[0].ToString());
}


}
}



private void button3_Click(object sender, EventArgs e)
{
//Stream myStream;
OpenFileDialog FD1 = new OpenFileDialog();


FD1.InitialDirectory = "c:\\" ;
FD1.Filter = "mdb files (*.mdb)|*.mdb|MS Access Database files (*.mdb)|*.mdb" ;
FD1.FilterIndex = 2 ;
FD1.RestoreDirectory = true ;
FD1.ShowDialog();
FN1 = FD1.FileName;
TxtOpenDB.Text = FN1;


}


private void button2_Click(object sender, EventArgs e)
{
Application.Exit();
}
}