Problem in inserting data into AccessDB

[Elan.Hira]

Hi,

I am trying to insert a record to a table in my local AccessDB through
CSharp. I dont get any error when i execute the program. But after i
insert, the table is not getting updated. Below is the code which i
use to insert

private void button2_Click(object sender, EventArgs e)
{
DataTable dt = LoadDT();
string SQL = "Select * from Ticket_Charging_Setting where
charge = 0 ";
OleDbConnection oConnection = new
OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\
\AFC_ATM.MDB");
try
{
oConnection.Open();
OleDbDataAdapter oAdapter = new
OleDbDataAdapter(SQL,oConnection);
oAdapter.InsertCommand = new OleDbCommand("insert into
Ticket_Charging_Setting(ticket_type_code,charge) " +
"values(@str1,@str2)");
oAdapter.InsertCommand.Parameters.Add("@str1",
OleDbType.VarChar, 1, "F");
oAdapter.InsertCommand.Parameters.Add("@str2",
OleDbType.Integer, 5, "50");
oAdapter.Update(dt);
MessageBox.Show("Success");
}
catch (OleDbException oe)
{
MessageBox.Show("Exception thrown : ",
oe.Errors[0].Message);
}
oConnection.Close();
}
public static DataTable LoadDT()
{
DataTable DT = new DataTable();
string ConnString = "Provider = Microsoft.Jet.OLEDB.4.0;
Data Source = C:\\AFC_ATM.MDB";
string SQL = "Select * from Ticket_Charging_Setting where
charge = 0 ";

OleDbConnection OleConn = new OleDbConnection(ConnString);
OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL,
OleConn);
OleConn.Open();
OleAdp.Fill(DT);
OleConn.Close();
return DT;
}

Can anyone please guide me what is the problem with the above code?
Thanks in advance!!

Cheers
Hira

 

[Earl]

Looks like you are pulling in a datatable, making no changes to it, then
doing an update. Since the datatable would have no changes to insert, looks
like it's working right to me. Also, line 2 of your button click event
(string SQL = "Select ... ) is redundant.

 

[Norman Yuan]

Take a closer look at the OldDbParameterCollection.Add() method's signature:

OleDbParameterCollection.Add(parameterName, OleDbType, size,
SOURCECOLUMN)

Here is how you call it:

oAdapter.InsertCommand.Parameters.Add("@str1", OleDbType.VarChar, 1,
"F");

and

oAdapter.InsertCommand.Parameters.Add("@str2", OleDbType.Integer, 5,
"50");

Does the data table being upated by the DataAdapter have column named as "F"
and "50"? of course not. So, the insert command get two null parameters.

Does the table in database have a primary key (or one of the column is a
primary key column)?, I'd bet not. That is why you did not get an error.

Try this:

OldDbParameter pmt;

pmt=oAdapter.InsertCommand.Parameters.Add("@str1", OleDbType.VarChar, 1);
pmt.Value="F"

pmt=oAdapter.InsertCommand.Parameters.Add("@str2", OleDbType.Integer, 5);
pmt.Value=50;

 

[Elan.Hira]

Thanks for your comments. But even after trying out your code
mentioned below, the insertion is not done successfully.
Can you guide me how to insert a record into an access db through
csharp code?

Cheers
Hira

Take a closer look at the OldDbParameterCollection.Add() method's signature:

OleDbParameterCollection.Add(parameterName, OleDbType, size,
SOURCECOLUMN)

Here is how you call it:

oAdapter.InsertCommand.Parameters.Add("@str1", OleDbType.VarChar, 1,
"F");

and

oAdapter.InsertCommand.Parameters.Add("@str2", OleDbType.Integer, 5,
"50");

Does the data table being upated by the DataAdapter have column named as "F"
and "50"? of course not. So, the insert command get two null parameters.

Does the table in database have a primary key (or one of the column is a
primary key column)?, I'd bet not. That is why you did not get an error.

Try this:

OldDbParameter pmt;

pmt=oAdapter.InsertCommand.Parameters.Add("@str1", OleDbType.VarChar, 1);
pmt.Value="F"

pmt=oAdapter.InsertCommand.Parameters.Add("@str2", OleDbType.Integer, 5);
pmt.Value=50;

Hi,

I am trying to insert a record to a table in my local AccessDB through
CSharp. I dont get any error when i execute the program. But after i
insert, the table is not getting updated. Below is the code which i
use to insert

private void button2_Click(object sender, EventArgs e)
{
DataTable dt = LoadDT();
string SQL = "Select * from Ticket_Charging_Setting where
charge = 0 ";
OleDbConnection oConnection = new
OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\
\AFC_ATM.MDB");
try
{
oConnection.Open();
OleDbDataAdapter oAdapter = new
OleDbDataAdapter(SQL,oConnection);
oAdapter.InsertCommand = new OleDbCommand("insert into
Ticket_Charging_Setting(ticket_type_code,charge) " +
"values(@str1,@str2)");
oAdapter.InsertCommand.Parameters.Add("@str1",
OleDbType.VarChar, 1, "F");
oAdapter.InsertCommand.Parameters.Add("@str2",
OleDbType.Integer, 5, "50");
oAdapter.Update(dt);
MessageBox.Show("Success");
}
catch (OleDbException oe)
{
MessageBox.Show("Exception thrown : ",
oe.Errors[0].Message);
}
oConnection.Close();
}
public static DataTable LoadDT()
{
DataTable DT = new DataTable();
string ConnString = "Provider = Microsoft.Jet.OLEDB.4.0;
Data Source = C:\\AFC_ATM.MDB";
string SQL = "Select * from Ticket_Charging_Setting where
charge = 0 ";

OleDbConnection OleConn = new OleDbConnection(ConnString);
OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL,
OleConn);
OleConn.Open();
OleAdp.Fill(DT);
OleConn.Close();
return DT;
}

Can anyone please guide me what is the problem with the above code?
Thanks in advance!!

Cheers
Hira- Hide quoted text -

- Show quoted text -

 

[Norman Yuan]

There are two way to update data back to database from client application by
using XXXXCommand object.

1. One way is to use XXXXCommand to execute SQL statement directly. In your
case, if you only need to insert a record into the table
"Ticket_Changing_Setting", you can simply use a OleDbCommand, rather than a
Adapter.

Psudo code looks like:

OleDbCommand cmd=new OleDbCommand("Insert.....(Ticket_Type_Code,Charge)
Values (@Para1,@Para2)",TheConnection);
OleDbParameter pmt;
pmt=oAdapter.InsertCommand.Parameters.Add("@Para1", OleDbType.VarChar, 1);
pmt.Value="F"
pmt=oAdapter.InsertCommand.Parameters.Add("@Para2", OleDbType.Interger);
pmt.Value=50;
cmd.ExecuteNonQuery();

Important note: with OleDbCommand, parameter name (@Para1, @Para2...) is not
important, the order of parameter being added into OleDbParameterCollection
is important and must be match the paramter place holder in SQL statement in
order.

2. Use DataAdapter to update data in datatabel of client side back to
database. This is what you were trying to do without success. In this case,
the DataAdapter must have InsertCommand and/or UpdateCommand and/or
DeleteCommand ready. You could use CommandBuilder to build these commands
for the DataAdapter, or build them in code as you did (but did it wrong).
When you call dataAdapter.Update(theDataTable), the dataAdapter loop through
all rows in the table for added/modified/deleted row and execute
corresponding commands. If now row was changed in the datatable, no command
would be executed.

So, you have two mistakes:

(1) the InsertCommand was wrong: as I pointed out in previous reply, the
last argument of OleDbParameterCollection.Add() method should be source
column name, not the parameter's value. So the code should be:

OleDbDataAdapter oAdapter = new OleDbDataAdapter(SQL,oConnection);
oAdapter.InsertCommand = new OleDbCommand("insert into
Ticket_Charging_Setting(ticket_type_code,charge values(@str1,@str2)");
oAdapter.InsertCommand.Parameters.Add("@str1",OleDbType.VarChar, 1,
"ticket_type_code");
oAdapter.InsertCommand.Parameters.Add("@str2",OleDbType.Integer, 5,
"charge");

(2) you do not make any change to the datatable, thus nothing would be
updated back to database by the DataAdapter. In your case you need to add a
new row to the datatable, so when the DataAdapter loops through the
datatable, it would find an added row and execute the InsertCommand on the
row. So, you do this before call DataAdapter.Update(thedataTable):

DataRow r=dt.NewRow();
r["ticket_type_code"]="F";
r["charge"]=50;
dt.Rows.Add(r);

Now, an added row exists in the datatable. When you update this datatable
with DataAdapter, the added row will be updated back to database by the
InsertCommand in DataAdapter.

HTH.

Thanks for your comments. But even after trying out your code
mentioned below, the insertion is not done successfully.
Can you guide me how to insert a record into an access db through
csharp code?

Cheers
Hira

Take a closer look at the OldDbParameterCollection.Add() method's
signature:

OleDbParameterCollection.Add(parameterName, OleDbType, size,
SOURCECOLUMN)

Here is how you call it:

oAdapter.InsertCommand.Parameters.Add("@str1", OleDbType.VarChar, 1,
"F");

and

oAdapter.InsertCommand.Parameters.Add("@str2", OleDbType.Integer, 5,
"50");

Does the data table being upated by the DataAdapter have column named as
"F"
and "50"? of course not. So, the insert command get two null parameters.

Does the table in database have a primary key (or one of the column is a
primary key column)?, I'd bet not. That is why you did not get an error.

Try this:

OldDbParameter pmt;

pmt=oAdapter.InsertCommand.Parameters.Add("@str1", OleDbType.VarChar, 1);
pmt.Value="F"

pmt=oAdapter.InsertCommand.Parameters.Add("@str2", OleDbType.Integer, 5);
pmt.Value=50;

Hi,

I am trying to insert a record to a table in my local AccessDB through
CSharp. I dont get any error when i execute the program. But after i
insert, the table is not getting updated. Below is the code which i
use to insert

private void button2_Click(object sender, EventArgs e)
{
DataTable dt = LoadDT();
string SQL = "Select * from Ticket_Charging_Setting where
charge = 0 ";
OleDbConnection oConnection = new
OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\
\AFC_ATM.MDB");
try
{
oConnection.Open();
OleDbDataAdapter oAdapter = new
OleDbDataAdapter(SQL,oConnection);
oAdapter.InsertCommand = new OleDbCommand("insert into
Ticket_Charging_Setting(ticket_type_code,charge) " +
"values(@str1,@str2)");
oAdapter.InsertCommand.Parameters.Add("@str1",
OleDbType.VarChar, 1, "F");
oAdapter.InsertCommand.Parameters.Add("@str2",
OleDbType.Integer, 5, "50");
oAdapter.Update(dt);
MessageBox.Show("Success");
}
catch (OleDbException oe)
{
MessageBox.Show("Exception thrown : ",
oe.Errors[0].Message);
}
oConnection.Close();
}
public static DataTable LoadDT()
{
DataTable DT = new DataTable();
string ConnString = "Provider = Microsoft.Jet.OLEDB.4.0;
Data Source = C:\\AFC_ATM.MDB";
string SQL = "Select * from Ticket_Charging_Setting where
charge = 0 ";

OleDbConnection OleConn = new OleDbConnection(ConnString);
OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL,
OleConn);
OleConn.Open();
OleAdp.Fill(DT);
OleConn.Close();
return DT;
}

Can anyone please guide me what is the problem with the above code?
Thanks in advance!!

Cheers
Hira- Hide quoted text -

- Show quoted text -

 

[Scott Starker]

Norman,

I have a similar problem. Although it executes correctly there is no new
record added to the table. Could it be that the table has an "AutoNumber"
field (RecordID)? I have tried (among other things):
SQLString = "SELECT * FROM TypeNames"
DBAdapter2 = New OleDbDataAdapter(SQLString, DBConnection)
DBDataSet2 = New DataSet()
DBAdapter2.Fill(DBDataSet2, "TypeNames")
DBTable3 = DBDataSet2.Tables("TypeNames")
foundRow2 = DBTable3.NewRow()
foundRow2("TypeCode") = cmbTypeLookup.Text
foundRow2("TypeDomain") = txtDomain.Text
foundRow2("TypeNameSpan") = txtTypeName.Text
' New row.
DBTable3.Rows.Add(FoundRow2)

I assume that the "AutoNumber" would get inserted automatically but I may be
wrong. Could you check out my code? Thanks.

Scott

There are two way to update data back to database from client application
by using XXXXCommand object.

1. One way is to use XXXXCommand to execute SQL statement directly. In
your case, if you only need to insert a record into the table
"Ticket_Changing_Setting", you can simply use a OleDbCommand, rather than
a Adapter.

Psudo code looks like:

OleDbCommand cmd=new OleDbCommand("Insert.....(Ticket_Type_Code,Charge)
Values (@Para1,@Para2)",TheConnection);
OleDbParameter pmt;
pmt=oAdapter.InsertCommand.Parameters.Add("@Para1", OleDbType.VarChar, 1);
pmt.Value="F"
pmt=oAdapter.InsertCommand.Parameters.Add("@Para2", OleDbType.Interger);
pmt.Value=50;
cmd.ExecuteNonQuery();

Important note: with OleDbCommand, parameter name (@Para1, @Para2...) is
not important, the order of parameter being added into
OleDbParameterCollection is important and must be match the paramter place
holder in SQL statement in order.

2. Use DataAdapter to update data in datatabel of client side back to
database. This is what you were trying to do without success. In this
case, the DataAdapter must have InsertCommand and/or UpdateCommand and/or
DeleteCommand ready. You could use CommandBuilder to build these commands
for the DataAdapter, or build them in code as you did (but did it wrong).
When you call dataAdapter.Update(theDataTable), the dataAdapter loop
through all rows in the table for added/modified/deleted row and execute
corresponding commands. If now row was changed in the datatable, no
command would be executed.

So, you have two mistakes:

(1) the InsertCommand was wrong: as I pointed out in previous reply, the
last argument of OleDbParameterCollection.Add() method should be source
column name, not the parameter's value. So the code should be:

OleDbDataAdapter oAdapter = new OleDbDataAdapter(SQL,oConnection);
oAdapter.InsertCommand = new OleDbCommand("insert into
Ticket_Charging_Setting(ticket_type_code,charge values(@str1,@str2)");
oAdapter.InsertCommand.Parameters.Add("@str1",OleDbType.VarChar, 1,
"ticket_type_code");
oAdapter.InsertCommand.Parameters.Add("@str2",OleDbType.Integer, 5,
"charge");

(2) you do not make any change to the datatable, thus nothing would be
updated back to database by the DataAdapter. In your case you need to add
a new row to the datatable, so when the DataAdapter loops through the
datatable, it would find an added row and execute the InsertCommand on the
row. So, you do this before call DataAdapter.Update(thedataTable):

DataRow r=dt.NewRow();
r["ticket_type_code"]="F";
r["charge"]=50;
dt.Rows.Add(r);

Now, an added row exists in the datatable. When you update this datatable
with DataAdapter, the added row will be updated back to database by the
InsertCommand in DataAdapter.

HTH.

Thanks for your comments. But even after trying out your code
mentioned below, the insertion is not done successfully.
Can you guide me how to insert a record into an access db through
csharp code?

Cheers
Hira

Take a closer look at the OldDbParameterCollection.Add() method's
signature:

OleDbParameterCollection.Add(parameterName, OleDbType, size,
SOURCECOLUMN)

Here is how you call it:

oAdapter.InsertCommand.Parameters.Add("@str1", OleDbType.VarChar, 1,
"F");

and

oAdapter.InsertCommand.Parameters.Add("@str2", OleDbType.Integer, 5,
"50");

Does the data table being upated by the DataAdapter have column named as
"F"
and "50"? of course not. So, the insert command get two null parameters.

Does the table in database have a primary key (or one of the column is a
primary key column)?, I'd bet not. That is why you did not get an error.

Try this:

OldDbParameter pmt;

pmt=oAdapter.InsertCommand.Parameters.Add("@str1", OleDbType.VarChar,
1);
pmt.Value="F"

pmt=oAdapter.InsertCommand.Parameters.Add("@str2", OleDbType.Integer,
5);
pmt.Value=50;





Hi,

I am trying to insert a record to a table in my local AccessDB through
CSharp. I dont get any error when i execute the program. But after i
insert, the table is not getting updated. Below is the code which i
use to insert

private void button2_Click(object sender, EventArgs e)
{
DataTable dt = LoadDT();
string SQL = "Select * from Ticket_Charging_Setting where
charge = 0 ";
OleDbConnection oConnection = new
OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\
\AFC_ATM.MDB");
try
{
oConnection.Open();
OleDbDataAdapter oAdapter = new
OleDbDataAdapter(SQL,oConnection);
oAdapter.InsertCommand = new OleDbCommand("insert into
Ticket_Charging_Setting(ticket_type_code,charge) " +
"values(@str1,@str2)");
oAdapter.InsertCommand.Parameters.Add("@str1",
OleDbType.VarChar, 1, "F");
oAdapter.InsertCommand.Parameters.Add("@str2",
OleDbType.Integer, 5, "50");
oAdapter.Update(dt);
MessageBox.Show("Success");
}
catch (OleDbException oe)
{
MessageBox.Show("Exception thrown : ",
oe.Errors[0].Message);
}
oConnection.Close();
}
public static DataTable LoadDT()
{
DataTable DT = new DataTable();
string ConnString = "Provider = Microsoft.Jet.OLEDB.4.0;
Data Source = C:\\AFC_ATM.MDB";
string SQL = "Select * from Ticket_Charging_Setting where
charge = 0 ";

OleDbConnection OleConn = new OleDbConnection(ConnString);
OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL,
OleConn);
OleConn.Open();
OleAdp.Fill(DT);
OleConn.Close();
return DT;
}

Can anyone please guide me what is the problem with the above code?
Thanks in advance!!

Cheers
Hira- Hide quoted text -

- Show quoted text -