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 OLEDB:Database Locking Mode=1;Data
Source=""C:\dat a.mdb"";Jet OLEDB:Engine
Type=5;Provider =""Microsoft.Je t.OLEDB.4.0"";J et
OLEDB:System database=;Jet OLEDB:SFP=False ;persist
security info=False;Exte nded Properties=;Mod e=Share Deny
None;Jet OLEDB:Encrypt Database=False; Jet OLEDB:Create
System Database=False; Jet OLEDB:Don't Copy Locale on
Compact=False;J et OLEDB:Compact Without Replica
Repair=False;Us er 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.Dat aSet myDataSet;
private System.Data.Ole Db.OleDbConnect ion
myConnection;
private
System.Data.Ole Db.OleDbDataAda pter myDataAdapter;
private
System.Data.Ole Db.OleDbCommand Builder myCmdBuilder;

private System.Data.Ole Db.OleDbCommand
mySelectCmd;

public Schedule()
{
this.InitilizeD b();
this.PopulateDa taSet();
}

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

public void InitilizeDb()
{
myDataAdapter = new
OleDbDataAdapte r();
myCmdBuilder = new
OleDbCommandBui lder(myDataAdap ter);
myDataSet = new DataSet();
}

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

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

// Define that the Select Command
is an SQL statement
mySelectCmd.Com mandType =
CommandType.Tex t;

try
{
myConnection.Op en();
myDataAdapter.S electCommand = mySelectCmd;

// Populate the DataSet
from the "data" table
myDataAdapter.F ill
(myDataSet,"dat a");
}
finally
{
myConnection.Cl ose();
}
}

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

try
{
myConnection.Op en();

// create a new row to
insert the data into
DataRow newRow =
myDataSet.Table s["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.Table s
["data"].Rows.Add(newRo w);

myDataAdapter.U pdate
(myDataSet, "data");

return "true";
}
catch(Exception e)
{
return e.Message + "\n\n"
+ e.StackTrace + "\n\n" + e.InnerExceptio n;
}
finally
{
myConnection.Cl ose();
}
}
</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.Com mon.DbDataAdapt er.Update(DataR ow[]
dataRows, DataTableMappin g tableMapping)
at System.Data.Com mon.DbDataAdapt er.Update(DataS et
dataSet, String srcTable)
at ShutdownMgr.Sch edule.InsertSch edule(String date,
String day, String time, String process, String comments,
String timeout, String status) in c:\documents and
settings\azel\m y documents\visua l studio
projects\shutdo wnmgr\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.

"Azel" <[e-mail] > wrote in message
news:05******** *************** *****@phx.gbl.. .
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 OLEDB:Database Locking Mode=1;Data
Source=""C:\dat a.mdb"";Jet OLEDB:Engine
Type=5;Provider =""Microsoft.Je t.OLEDB.4.0"";J et
OLEDB:System database=;Jet OLEDB:SFP=False ;persist
security info=False;Exte nded Properties=;Mod e=Share Deny
None;Jet OLEDB:Encrypt Database=False; Jet OLEDB:Create
System Database=False; Jet OLEDB:Don't Copy Locale on
Compact=False;J et OLEDB:Compact Without Replica
Repair=False;Us er 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.Dat aSet myDataSet;
private System.Data.Ole Db.OleDbConnect ion
myConnection;
private
System.Data.Ole Db.OleDbDataAda pter myDataAdapter;
private
System.Data.Ole Db.OleDbCommand Builder myCmdBuilder;

private System.Data.Ole Db.OleDbCommand
mySelectCmd;

public Schedule()
{
this.InitilizeD b();
this.PopulateDa taSet();
}

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

public void InitilizeDb()
{
myDataAdapter = new
OleDbDataAdapte r();
myCmdBuilder = new
OleDbCommandBui lder(myDataAdap ter);
myDataSet = new DataSet();
}

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

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

// Define that the Select Command
is an SQL statement
mySelectCmd.Com mandType =
CommandType.Tex t;

try
{
myConnection.Op en();
myDataAdapter.S electCommand = mySelectCmd;

// Populate the DataSet
from the "data" table
myDataAdapter.F ill
(myDataSet,"dat a");
}
finally
{
myConnection.Cl ose();
}
}

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

try
{
myConnection.Op en();

// create a new row to
insert the data into
DataRow newRow =
myDataSet.Table s["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.Table s
["data"].Rows.Add(newRo w);

myDataAdapter.U pdate
(myDataSet, "data");

return "true";
}
catch(Exception e)
{
return e.Message + "\n\n"
+ e.StackTrace + "\n\n" + e.InnerExceptio n;
}
finally
{
myConnection.Cl ose();
}
}
</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.Com mon.DbDataAdapt er.Update(DataR ow[]
dataRows, DataTableMappin g tableMapping)
at System.Data.Com mon.DbDataAdapt er.Update(DataS et
dataSet, String srcTable)
at ShutdownMgr.Sch edule.InsertSch edule(String date,
String day, String time, String process, String comments,
String timeout, String status) in c:\documents and
settings\azel\m y documents\visua l studio
projects\shutdo wnmgr\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.