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)";

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

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...

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.

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();
}
}

Edited 3 Years Ago by happygeek: fixed formatting

This article has been dead for over six months. Start a new discussion instead.