P
Peter
I am trying to Add and Update data to Access database, the update part works
just fine, but when I try to add data I get the following error, what am I
doing wrong?
---------------------------
---------------------------
System.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC Microsoft
Access Driver] Syntax error in INSERT INTO statement.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at tsrdt.Import.M(DataSet dsNew, String tableName) in c:\program
files\c#code\tsrdt\import.cs:line 131
---------------------------
OK
---------------------------
---------------------------------------- here's my
code ------------------------------------------------
private void MergeData(DataSet dsNew)
{
OdbcDataAdapter adapter;
OdbcConnection tsrConnection;
string connectionString = @"MaxBufferSize=2048;FIL=MS
Access;DSN=Scans;PageTimeout=5;UID=admin;DBQ=C:\My
Documents\Test.MDB;DriverId=25";
DataRow dataRow;
string keyField = string.Empty;
object[] findKey = new object[1];
DataSet dsCurrent = new DataSet();
try
{
tsrConnection = new OdbcConnection(connectionString);
tsrConnection.Open();
adapter = new OdbcDataAdapter("Select * from MyTable",
tsrConnection);
OdbcCommandBuilder cmdBuilder = new
OdbcCommandBuilder(adapter);
adapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
adapter.Fill(dsCurrent, "MyTable");
DataTable MyTable = dsCurrent.Tables[0];
DataColumn[] keys = new DataColumn[1];
keys[0] = MyTable.Columns["MyNumber"];
MyTable.PrimaryKey = keys;
foreach(DataRow dr in dsNew.Tables[0].Rows)
{
findKey[0] = Convert.ToInt32(dr["MyNumber"]);
dataRow = dsCurrent.Tables[0].Rows.Find(findKey);
if(dataRow != null)
{
foreach(DataColumn cl in dsNew.Tables[0].Columns)
{
dataRow[cl.ColumnName] = dr[cl.ColumnName];
}
}
else
{
//
// add new record section
//
dataRow = MyTable.NewRow();
foreach(DataColumn cl in dsNew.Tables[0].Columns)
{
dataRow[cl.ColumnName] = dr[cl.ColumnName];
}
MyTable.Rows.Add(dataRow);
}
}
adapter.Update(dsCurrent, "MyTable"); // this is where I
get an error when adding new records
adapter.Dispose();
}
catch(Exception e)
{
MessageBox.Show(e.ToString());
}
}
just fine, but when I try to add data I get the following error, what am I
doing wrong?
---------------------------
---------------------------
System.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC Microsoft
Access Driver] Syntax error in INSERT INTO statement.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at tsrdt.Import.M(DataSet dsNew, String tableName) in c:\program
files\c#code\tsrdt\import.cs:line 131
---------------------------
OK
---------------------------
---------------------------------------- here's my
code ------------------------------------------------
private void MergeData(DataSet dsNew)
{
OdbcDataAdapter adapter;
OdbcConnection tsrConnection;
string connectionString = @"MaxBufferSize=2048;FIL=MS
Access;DSN=Scans;PageTimeout=5;UID=admin;DBQ=C:\My
Documents\Test.MDB;DriverId=25";
DataRow dataRow;
string keyField = string.Empty;
object[] findKey = new object[1];
DataSet dsCurrent = new DataSet();
try
{
tsrConnection = new OdbcConnection(connectionString);
tsrConnection.Open();
adapter = new OdbcDataAdapter("Select * from MyTable",
tsrConnection);
OdbcCommandBuilder cmdBuilder = new
OdbcCommandBuilder(adapter);
adapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
adapter.Fill(dsCurrent, "MyTable");
DataTable MyTable = dsCurrent.Tables[0];
DataColumn[] keys = new DataColumn[1];
keys[0] = MyTable.Columns["MyNumber"];
MyTable.PrimaryKey = keys;
foreach(DataRow dr in dsNew.Tables[0].Rows)
{
findKey[0] = Convert.ToInt32(dr["MyNumber"]);
dataRow = dsCurrent.Tables[0].Rows.Find(findKey);
if(dataRow != null)
{
foreach(DataColumn cl in dsNew.Tables[0].Columns)
{
dataRow[cl.ColumnName] = dr[cl.ColumnName];
}
}
else
{
//
// add new record section
//
dataRow = MyTable.NewRow();
foreach(DataColumn cl in dsNew.Tables[0].Columns)
{
dataRow[cl.ColumnName] = dr[cl.ColumnName];
}
MyTable.Rows.Add(dataRow);
}
}
adapter.Update(dsCurrent, "MyTable"); // this is where I
get an error when adding new records
adapter.Dispose();
}
catch(Exception e)
{
MessageBox.Show(e.ToString());
}
}