Unable to add data to a MS Access table.

[Bujitsu]

Hi,

I am having trouble adding data to a MS Access 2003 table.
I have created a MS Access table by hand and added the column types as text.
The code makes the connection, but then it says that the Date column doesn't exsist?????

Any help would be great,
Thanks


string strDataSrc = ";Data Source=";
tempStr = "E:\\temp\\Log.mdb";
strResult = String.Concat(strDataSrc, tempStr);
OleDbConnection conn = null;
conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=" + strResult);
conn.Open();
OleDbDataAdapter fgADOAdapter = new OleDbDataAdapter();

OleDbCommandBuilder fgCommandBuilder = new OleDbCommandBuilder(fgADOAdapter);

DataTable fgDataTable = new DataTable("Server");
DataRow fgDataRow = fgDataTable.NewRow();
fgDataRow["Date"] = logWeb.LogDate;
fgDataRow["Time"] = logWeb.LogTime;

fgDataTable.Rows.Add(fgDataRow);
fgADOAdapter.Update(fgDataTable);

 

[psemianonymous]

Be very careful, "Date" is maybe a reserved word (it gets fuzzy when dealing with JET SQL, but I assume it's a synonym for the DATETYPE data type). Try "[Date]" or rename your date field to something other than plain "Date".

 

[Bujitsu]

Foolio12,

Thank you for your suggestions, but I tried both of your suggestions, no luck.

Any other ideas??

Cheers.

 

[obislavu]

You should call Fill() or FillSchema() function on the DataAdapter object after the creation on the fgDataTable table. Example:

// Retreive Schema to get columns properties
	..	
SqlDataAdapter DataAdapter	= new SqlDataAdapter();
DataTable fgDataTable	= new DataTable("MyTable");
sCommand.CommandText = "SELECT TOP 0 * FROM " + SQLTable;
DataAdapter.SelectCommand = sCommand;
DataAdapter.FillSchema(fgDataTable,SchemaType.Source);


// Check if a given column exists in the loaded table 
.
if (fgDataTable.Columns.IndexOf("Date") == -1 )
{
// The column "Date" is not part of the table
// The following lines add this column as a DateTiime type at run time
fgDataTable.Columns.Add("Date",System.Type.GetType("System.DateTime"));
fgDataTable.Columns["Date"].DefaultValue = DateTime.Now();
}
// Add a row
DataRow dr = fgDataTable.NewRow();
fgDataRow["Date"] = logWeb.LogDate; 
fgDataRow["Time"] = logWeb.LogTime;
fgDataTable.Rows.Add(fgDataRow);
int iCount = DataAdapter.Update(fgDataTable);
//

You should use try-catch block for the above statements to control the errors.
-obislavu-

 

[obislavu]

In my previous post I used Sql object but the same code is working using OleDb objects.
-obislavu-

 

[Bujitsu]

Obislavu,

Thanks for the help so far.

Now I receive the following:
Additional information: Update requires a valid InsertCommand when passed DataRow collection with new rows.

Also the SQL TOP command didn't like 0 (zero), so I put 1 in its place.

Cheers

OleDbCommand fgSelectCommand = conn.CreateCommand();
fgSelectCommand.CommandText = "SELECT TOP 1 * FROM Antivirus";
OleDbDataAdapter fgADOAdapter = new OleDbDataAdapter();
fgADOAdapter.SelectCommand = fgSelectCommand;
DataTable fgDataTable = new DataTable("Server");
fgADOAdapter.FillSchema(fgDataTable, SchemaType.Source);
DataRow fgDataRow = fgDataTable.NewRow();

fgDataRow["Date"] = logWeb.LogDate;
fgDataRow["Time"] = logTime.LogTime;
fgDataTable.Rows.Add(fgDataRow);

fgADOAdapter.Update(fgDataTable);