Inserting into Access Database using C#....?

[Azel]

Hi,

I am trying to learn ADO.net and I keep running into
problems trying to insert data into my Access Database:
data.mdb.

here is my code:

<code>
// Database Variables
private string connectionStr = @"Jet
OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry
Path=;Jet OLEDBatabase Locking Mode=1;Data
Source=""C:\data.mdb"";Jet OLEDB:Engine
Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet
OLEDB:System database=;Jet OLEDB:SFP=False;persist
security info=False;Extended Properties=;Mode=Share Deny
None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create
System Database=False;Jet OLEDBon't Copy Locale on
Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;User ID=Admin;Jet OLEDB:Global Bulk
Transactions=1";
private string selectStr = "Select id,
date, day, time, comments, timeout, status, process from
data";

private System.Data.DataSet myDataSet;
private System.Data.OleDb.OleDbConnection
myConnection;
private
System.Data.OleDb.OleDbDataAdapter myDataAdapter;
private
System.Data.OleDb.OleDbCommandBuilder myCmdBuilder;

private System.Data.OleDb.OleDbCommand
mySelectCmd;

public Schedule()
{
this.InitilizeDb();
this.PopulateDataSet();
}

private OleDbConnection ConnectDb()
{
return new OleDbConnection
(connectionStr);
}

public void InitilizeDb()
{
myDataAdapter = new
OleDbDataAdapter();
myCmdBuilder = new
OleDbCommandBuilder(myDataAdapter);
myDataSet = new DataSet();
}

private void PopulateDataSet()
{
// get the connection object
myConnection = (OleDbConnection)
this.ConnectDb();

// Initilize the Select Command
mySelectCmd = new OleDbCommand
(selectStr, myConnection);

// Define that the Select Command
is an SQL statement
mySelectCmd.CommandType =
CommandType.Text;

try
{
myConnection.Open();


myDataAdapter.SelectCommand = mySelectCmd;

// Populate the DataSet
from the "data" table
myDataAdapter.Fill
(myDataSet,"data");
}
finally
{
myConnection.Close();
}
}

public string InsertSchedule(string date,
string day, string time, string process,
string comments, string timeout,
string status)
{
// get the connection object
myConnection = (OleDbConnection)
this.ConnectDb();

try
{
myConnection.Open();

// create a new row to
insert the data into
DataRow newRow =
myDataSet.Tables["data"].NewRow();

// initilize the new row
newRow["id"] = 3;
newRow["date"] = date;
newRow["day"] = day;
newRow["time"] = time;
newRow["process"] =
process;
newRow["comments"] =
comments;
newRow["timeout"] =
timeout;
newRow["status"] = status;

// add the new row into
the dataset
myDataSet.Tables
["data"].Rows.Add(newRow);

myDataAdapter.Update
(myDataSet, "data");

return "true";
}
catch(Exception e)
{
return e.Message + "\n\n"
+ e.StackTrace + "\n\n" + e.InnerException;
}
finally
{
myConnection.Close();
}
}
</code>



the problem is when I try to insert into the database, I
keep getting this Exception Message:




<code>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 ShutdownMgr.Schedule.InsertSchedule(String date,
String day, String time, String process, String comments,
String timeout, String status) in c:\documents and
settings\azel\my documents\visual studio
projects\shutdownmgr\schedule.cs:line 99
</code>




I have tried different ways but none were successful. Is
anyone able to spot where I am going wrong? X|

thanks.

 

[Jeremy Ames]

Yeah, I never actually see a Insert statement either.

Hi,

I am trying to learn ADO.net and I keep running into
problems trying to insert data into my Access Database:
data.mdb.

here is my code:

<code>
// Database Variables
private string connectionStr = @"Jet
OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry
Path=;Jet OLEDBatabase Locking Mode=1;Data
Source=""C:\data.mdb"";Jet OLEDB:Engine
Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet
OLEDB:System database=;Jet OLEDB:SFP=False;persist
security info=False;Extended Properties=;Mode=Share Deny
None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create
System Database=False;Jet OLEDBon't Copy Locale on
Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;User ID=Admin;Jet OLEDB:Global Bulk
Transactions=1";
private string selectStr = "Select id,
date, day, time, comments, timeout, status, process from
data";

private System.Data.DataSet myDataSet;
private System.Data.OleDb.OleDbConnection
myConnection;
private
System.Data.OleDb.OleDbDataAdapter myDataAdapter;
private
System.Data.OleDb.OleDbCommandBuilder myCmdBuilder;

private System.Data.OleDb.OleDbCommand
mySelectCmd;

public Schedule()
{
this.InitilizeDb();
this.PopulateDataSet();
}

private OleDbConnection ConnectDb()
{
return new OleDbConnection
(connectionStr);
}

public void InitilizeDb()
{
myDataAdapter = new
OleDbDataAdapter();
myCmdBuilder = new
OleDbCommandBuilder(myDataAdapter);
myDataSet = new DataSet();
}

private void PopulateDataSet()
{
// get the connection object
myConnection = (OleDbConnection)
this.ConnectDb();

// Initilize the Select Command
mySelectCmd = new OleDbCommand
(selectStr, myConnection);

// Define that the Select Command
is an SQL statement
mySelectCmd.CommandType =
CommandType.Text;

try
{
myConnection.Open();


myDataAdapter.SelectCommand = mySelectCmd;

// Populate the DataSet
from the "data" table
myDataAdapter.Fill
(myDataSet,"data");
}
finally
{
myConnection.Close();
}
}

public string InsertSchedule(string date,
string day, string time, string process,
string comments, string timeout,
string status)
{
// get the connection object
myConnection = (OleDbConnection)
this.ConnectDb();

try
{
myConnection.Open();

// create a new row to
insert the data into
DataRow newRow =
myDataSet.Tables["data"].NewRow();

// initilize the new row
newRow["id"] = 3;
newRow["date"] = date;
newRow["day"] = day;
newRow["time"] = time;
newRow["process"] =
process;
newRow["comments"] =
comments;
newRow["timeout"] =
timeout;
newRow["status"] = status;

// add the new row into
the dataset
myDataSet.Tables
["data"].Rows.Add(newRow);

myDataAdapter.Update
(myDataSet, "data");

return "true";
}
catch(Exception e)
{
return e.Message + "\n\n"
+ e.StackTrace + "\n\n" + e.InnerException;
}
finally
{
myConnection.Close();
}
}
</code>



the problem is when I try to insert into the database, I
keep getting this Exception Message:




<code>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 ShutdownMgr.Schedule.InsertSchedule(String date,
String day, String time, String process, String comments,
String timeout, String status) in c:\documents and
settings\azel\my documents\visual studio
projects\shutdownmgr\schedule.cs:line 99
</code>




I have tried different ways but none were successful. Is
anyone able to spot where I am going wrong? X|

thanks.