RE: OleDbCommandBuilder insert query will not work? dataset to Ac by KerryMoorman
KerryMoorman
Thu Feb 28 12:00:01 CST 2008
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
"gibbonas" wrote:
> 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);
> }