OleDbCommandBuilder insert query will not work? dataset to Access

[gibbonas]

I am trying to take a very strangely delimited text file and put it into
access. So far I was able to get the text file into a dataset and figured
from there I could get it into access. I am trying to use the code below but
the insert query(oledbcommandbuilder) just will not work. I am working with
a blank access database and I am able to get the table and the field names in
the
database(with a primary key just set ot the first row). I just cannot get
the rest of the data. I get an error that there is incorrect syntax in the
insert query. Can someone please help! I've been all over the site reading a
buch of different things
and tried to just write inserts for each row but cannot get that to work
either.
Thank you!

DataSet result = new DataSet();
....get delimited file into dataset...

OleDbConnection DB = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\\Conversions\\" + agencyid + "\\" + agencyid + ".mdb");

OleDbDataAdapter DataAdapter = new OleDbDataAdapter();
DataAdapter.SelectCommand = new OleDbCommand("SELECT * FROM " + TableName,
DB);
OleDbCommandBuilder CmdBuilder = new OleDbCommandBuilder(DataAdapter);
DB.Open();
DataAdapter.FillSchema(result, SchemaType.Source, TableName);
DataAdapter.InsertCommand = CmdBuilder.GetInsertCommand();
//I added the line above because the insert query did not show in the data
adapter.
//It now has: insert into tablename(fieldname1,fieldname2...) Values
(?,?,?...)

DataAdapter.Fill(result, TableName);
DataAdapter.Update(result, TableName);//Fails here on the insert query syntax
DB.Close();

 

[Kerry Moorman]

gibbonas,

What is the name of the table and what are the names of the columns within
the table?

Kerry Moorman

 

[gibbonas]

Table names and field names will vary- I have a directory of txt files to add
to the Access Database. The first one happens to be: CandidateActivities
-FIELDS:
ID|*|CandidateID|*|ActivityDate|*|CreateDate|*|CreateUserID|*|UpdateDate|*|UpdateUserID|*|CategoryID|*|Note|*|Result|*|ResultDate|*|ActivityUserID|*|HistoryResultID|*|ConvID|*|WorkGroupID|*|emailout|*|orderid
The rest of my code:

//The DataSet the file gets put into
DataSet result = new DataSet();

//Open the file in a stream reader.
StreamReader s = new StreamReader(File);
string[] LineBreak = new string[1];
LineBreak[0] = "~*~";

string[] row =
s.ReadToEnd().Split(LineBreak,StringSplitOptions.None);

//Split the first line into the columns
string[] delimit = new string[1];
delimit[0] = delimiter;
string[] columns = row[0].Split(delimit,StringSplitOptions.None);

//Add the new DataTable to the RecordSet
result.Tables.Add(TableName);

//Sql query to insert table
string TableQRY = "CREATE TABLE " + TableName + " ( " ;
//string InsertQRY = "Insert Into " + TableName + " (";

//Cycle the colums, adding those that don't exist yet
//and sequencing the one that do.
bool prim = true;
foreach(string col in columns)
{
bool added = false;
string next = "";
int i = 0;
while(!added)
{
//Build the column name and remove any unwanted
characters.
string columnname = col + next;
columnname = columnname.Replace("#","");
columnname = columnname.Replace("'","");
columnname = columnname.Replace("&","");

//See if the column already exists
if(!result.Tables[TableName].Columns.Contains(columnname))
{
//if it doesn't then we add it here and mark it as added
result.Tables[TableName].Columns.Add(columnname);
if (prim == true)
{
result.Tables[TableName].PrimaryKey = new
DataColumn[] { result.Tables[TableName].Columns[columnname] };
TableQRY = TableQRY + "[" + columnname + "] text
PRIMARY KEY, ";

}
else
TableQRY = TableQRY + "[" + columnname + "]
text, ";

prim = false;
//InsertQRY = InsertQRY + columnname + ", ";
added = true;
}
else
{
//if it did exist then we increment the sequencer and
try again.
i++;
next = "_" + i.ToString();
}
}
}
//create access table with column names
OleDbConnection DB = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\\Conversions\\" + agencyid + "\\" + agencyid + ".mdb");

DB.Open();
TableQRY = TableQRY.Substring(0, TableQRY.Length - 2) + ")";
OleDbCommand cmd = new OleDbCommand(TableQRY, DB);
cmd.ExecuteNonQuery();
DB.Close();
cmd = null;


//Now add each row to the DataSet
InsertQRY = InsertQRY.Substring(0,InsertQRY.Length-3) + ")
Values (";
for(int c = 1;c<row.Length;c++)//(string r in row)
{
string r = row[c];
//Split the row at the delimiter.
string[] items = r.Split(delimit, StringSplitOptions.None);
//for (int x = 0; x < items.Length; x++)
//{

// InsertQRY = InsertQRY + "'" + items[x] + "', ";
// //vurvDataAdapter.InsertCommand.Parameters(
//}
//InsertQRY = InsertQRY.Substring(0, InsertQRY.Length - 2)
+ ")";

//DB.Open();
//OleDbCommand Insertcmd = new OleDbCommand(InsertQRY, DB);
//Insertcmd.ExecuteNonQuery();
//DB.Close();

//Add the item
result.Tables[TableName].Rows.Add(items);
}

 

[Kerry Moorman]

gibbonas,

"Note" is a reserved word in some versions of Access. You need to enclose it
in square brackets.

It looks like your code is doing that for some situations, but not for
building the Insert.

Kerry Moorman

Table names and field names will vary- I have a directory of txt files to add
to the Access Database. The first one happens to be: CandidateActivities
-FIELDS:
ID|*|CandidateID|*|ActivityDate|*|CreateDate|*|CreateUserID|*|UpdateDate|*|UpdateUserID|*|CategoryID|*|Note|*|Result|*|ResultDate|*|ActivityUserID|*|HistoryResultID|*|ConvID|*|WorkGroupID|*|emailout|*|orderid
The rest of my code:

//The DataSet the file gets put into
DataSet result = new DataSet();

//Open the file in a stream reader.
StreamReader s = new StreamReader(File);
string[] LineBreak = new string[1];
LineBreak[0] = "~*~";

string[] row =
s.ReadToEnd().Split(LineBreak,StringSplitOptions.None);

//Split the first line into the columns
string[] delimit = new string[1];
delimit[0] = delimiter;
string[] columns = row[0].Split(delimit,StringSplitOptions.None);

//Add the new DataTable to the RecordSet
result.Tables.Add(TableName);

//Sql query to insert table
string TableQRY = "CREATE TABLE " + TableName + " ( " ;
//string InsertQRY = "Insert Into " + TableName + " (";

//Cycle the colums, adding those that don't exist yet
//and sequencing the one that do.
bool prim = true;
foreach(string col in columns)
{
bool added = false;
string next = "";
int i = 0;
while(!added)
{
//Build the column name and remove any unwanted
characters.
string columnname = col + next;
columnname = columnname.Replace("#","");
columnname = columnname.Replace("'","");
columnname = columnname.Replace("&","");

//See if the column already exists
if(!result.Tables[TableName].Columns.Contains(columnname))
{
//if it doesn't then we add it here and mark it as added
result.Tables[TableName].Columns.Add(columnname);
if (prim == true)
{
result.Tables[TableName].PrimaryKey = new
DataColumn[] { result.Tables[TableName].Columns[columnname] };
TableQRY = TableQRY + "[" + columnname + "] text
PRIMARY KEY, ";

}
else
TableQRY = TableQRY + "[" + columnname + "]
text, ";

prim = false;
//InsertQRY = InsertQRY + columnname + ", ";
added = true;
}
else
{
//if it did exist then we increment the sequencer and
try again.
i++;
next = "_" + i.ToString();
}
}
}
//create access table with column names
OleDbConnection DB = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\\Conversions\\" + agencyid + "\\" + agencyid + ".mdb");

DB.Open();
TableQRY = TableQRY.Substring(0, TableQRY.Length - 2) + ")";
OleDbCommand cmd = new OleDbCommand(TableQRY, DB);
cmd.ExecuteNonQuery();
DB.Close();
cmd = null;


//Now add each row to the DataSet
InsertQRY = InsertQRY.Substring(0,InsertQRY.Length-3) + ")
Values (";
for(int c = 1;c<row.Length;c++)//(string r in row)
{
string r = row[c];
//Split the row at the delimiter.
string[] items = r.Split(delimit, StringSplitOptions.None);
//for (int x = 0; x < items.Length; x++)
//{

// InsertQRY = InsertQRY + "'" + items[x] + "', ";
// //vurvDataAdapter.InsertCommand.Parameters(
//}
//InsertQRY = InsertQRY.Substring(0, InsertQRY.Length - 2)
+ ")";

//DB.Open();
//OleDbCommand Insertcmd = new OleDbCommand(InsertQRY, DB);
//Insertcmd.ExecuteNonQuery();
//DB.Close();

//Add the item
result.Tables[TableName].Rows.Add(items);
}

 

[gibbonas]

That explains why I couldn't do it by manually building the query but
wouldn't the commandbuilder's generated query that I'm currently using try to
do that? I'm curently not building the query commandbuilder is. I'll give
this a try though.

HaH! just tried this and changed the field names to 1_Fieldname so there
would not be any reserved words. NOW it is working GREAT!

Thank you very much!
Aaron

gibbonas,

"Note" is a reserved word in some versions of Access. You need to enclose it
in square brackets.

It looks like your code is doing that for some situations, but not for
building the Insert.

Kerry Moorman

Table names and field names will vary- I have a directory of txt files to add
to the Access Database. The first one happens to be: CandidateActivities
-FIELDS:
ID|*|CandidateID|*|ActivityDate|*|CreateDate|*|CreateUserID|*|UpdateDate|*|UpdateUserID|*|CategoryID|*|Note|*|Result|*|ResultDate|*|ActivityUserID|*|HistoryResultID|*|ConvID|*|WorkGroupID|*|emailout|*|orderid
The rest of my code:

//The DataSet the file gets put into
DataSet result = new DataSet();

//Open the file in a stream reader.
StreamReader s = new StreamReader(File);
string[] LineBreak = new string[1];
LineBreak[0] = "~*~";

string[] row =
s.ReadToEnd().Split(LineBreak,StringSplitOptions.None);

//Split the first line into the columns
string[] delimit = new string[1];
delimit[0] = delimiter;
string[] columns = row[0].Split(delimit,StringSplitOptions.None);

//Add the new DataTable to the RecordSet
result.Tables.Add(TableName);

//Sql query to insert table
string TableQRY = "CREATE TABLE " + TableName + " ( " ;
//string InsertQRY = "Insert Into " + TableName + " (";

//Cycle the colums, adding those that don't exist yet
//and sequencing the one that do.
bool prim = true;
foreach(string col in columns)
{
bool added = false;
string next = "";
int i = 0;
while(!added)
{
//Build the column name and remove any unwanted
characters.
string columnname = col + next;
columnname = columnname.Replace("#","");
columnname = columnname.Replace("'","");
columnname = columnname.Replace("&","");

//See if the column already exists
if(!result.Tables[TableName].Columns.Contains(columnname))
{
//if it doesn't then we add it here and mark it as added
result.Tables[TableName].Columns.Add(columnname);
if (prim == true)
{
result.Tables[TableName].PrimaryKey = new
DataColumn[] { result.Tables[TableName].Columns[columnname] };
TableQRY = TableQRY + "[" + columnname + "] text
PRIMARY KEY, ";

}
else
TableQRY = TableQRY + "[" + columnname + "]
text, ";

prim = false;
//InsertQRY = InsertQRY + columnname + ", ";
added = true;
}
else
{
//if it did exist then we increment the sequencer and
try again.
i++;
next = "_" + i.ToString();
}
}
}
//create access table with column names
OleDbConnection DB = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\\Conversions\\" + agencyid + "\\" + agencyid + ".mdb");

DB.Open();
TableQRY = TableQRY.Substring(0, TableQRY.Length - 2) + ")";
OleDbCommand cmd = new OleDbCommand(TableQRY, DB);
cmd.ExecuteNonQuery();
DB.Close();
cmd = null;


//Now add each row to the DataSet
InsertQRY = InsertQRY.Substring(0,InsertQRY.Length-3) + ")
Values (";
for(int c = 1;c<row.Length;c++)//(string r in row)
{
string r = row[c];
//Split the row at the delimiter.
string[] items = r.Split(delimit, StringSplitOptions.None);
//for (int x = 0; x < items.Length; x++)
//{

// InsertQRY = InsertQRY + "'" + items[x] + "', ";
// //vurvDataAdapter.InsertCommand.Parameters(
//}
//InsertQRY = InsertQRY.Substring(0, InsertQRY.Length - 2)
+ ")";

//DB.Open();
//OleDbCommand Insertcmd = new OleDbCommand(InsertQRY, DB);
//Insertcmd.ExecuteNonQuery();
//DB.Close();

//Add the item
result.Tables[TableName].Rows.Add(items);
}