The problem: create an MDB file and populate it with table that is create at runtime.
I am missing something, so pleas help:
Code:
string[] tickers = new string[] { "W1", "W2" };
string _path2file = "D:\\temp.mdb";
File.Delete("D:\\temp.mdb");
DoFiles.createMDB("D:\\temp.mdb"); // just creates an empty MDB file
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source=" + _path2file;
// open a connection to the database
OleDbConnection conn = new OleDbConnection(connString);
conn.Open();
string query = "CREATE TABLE TTT ([DateR] TEXT(10), [TimeIndex] NUMBER";
for (int i = 0; i < tickers.Length; i++)
{
query = query + ", [" + tickers[i] + "] " + "NUMBER";
}
query = query + ")";
OleDbCommand cmd = new OleDbCommand(query, conn);
cmd.ExecuteNonQuery();
query = "INSERT INTO [TTT] (TimeIndex) VALUES(10)";
OleDbCommand command = conn.CreateCommand();
command.CommandText = query;
command.ExecuteNonQuery();
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM TTT", conn);
OleDbCommandBuilder cm = new OleDbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable dt = ds.Tables[0];
dt.Rows.Add();
dt.Rows[0][1] = 1;
adapter.Update(ds);
// THE ERROR GETS HERE
// in principle, here I will update the dt data from another table.
conn.Close();
the error I am getting at adapter.Update(ds)
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."
Any suggestions are welcome. thanks.