Inserting Recs into Access DB

  • Thread starter Thread starter JJ
  • Start date Start date
J

JJ

Hi all,

I am trying to add records to a Access DB. I use the following code to do
this. The record gets recorded in the db's but when I read the record back
from the database. All fields report nothing. Now here's the clincher. When
I go to Access and do a query for that record in Access it still reports
nothing even though the record is in table. Now when I cut and paste the
record back into same table. wahlah its fine. Am I maybe missing some kind
of commit in this code? Any ideas? How does the code look below to you?

try
{

DataSet dsHours = new DataSet();
System.Data.DataRow drTS;
//Initialize Data Adapter and Dataset for TS Hours Table
daHours.TableMappings.Add("Table", "Hours" );
daHours.Fill(dsHours ,"Hours");

drTS = dsHours.Tables["Hours"].NewRow();
drTS["TSDate"] = dtEndDate.Value;
drTS["EmployeeID"] = tbEmployeeNum.Text.ToString();
drTS["TSMonReg"] = tbMon.Text.ToString();
drTS["TSTuesReg"] = tbTues.Text.ToString();
drTS["TSWedsReg"] = tbWeds.Text.ToString();
drTS["TSThursReg"] = tbThurs.Text.ToString();
drTS["TSFriReg"] = tbFri.Text.ToString();
drTS["TSSatReg"] = tbSat.Text.ToString();
drTS["TSSunReg"] = tbSun.Text.ToString();
drTS["TSMonOT"] = tbMonOT.Text.ToString();
dsHours.Tables[0].Rows.Add(drTS);

daHours.Update(dsHours);
MessageBox.Show("Record Added Successfully !");
bUpdateTS.Visible = true;
bAddTS.Visible = false;
}
catch (OleDbException err)
{
string errorMessage = "Message: " + err.Message + "\n" +
"Source: " + err.Source + "\n" ;
MessageBox.Show(errorMessage,"Error in DataCon");
}
catch(Exception err)
{
MessageBox.Show(err.Message,"General Error");
}


Thanks,
JJ
 
JJ:

Hopefully this doesn't get executed over and over b/c you don't want to
recreate datasets all over the place. With that said, assuming that all of
the tb.Text's have actual values, what does your update statement look like?
That's the most likely cause of the problem.

However ,if your saying that you query the Access db and the record looks
empty, but you cut it out and paste it right back in and values appear, you
may need to compact and repair the database.

As an aside, you may want to consider using the Databindings of each of the
textboxes and controls

ie tb.EmployeeNum.DataBindings.Add("Text", dsHours.Tables("Hours"),
"FieldNameThatHoursIsMappedTo")

Then use a BindingManagerBase/BindingContext. When you want to add a new
row, you can just call BindingContext.AddNew...it's probably a lot cleaner
approach.

All in all, if the database doesn't need repaired then it's probably the
update statement, if you post it I'll be glad to take a look at it and try
to figure it out.

Cheers,

Bill
 
Hi Bill,

This is for an insert into the Access DB. I use commandbuilder to build
the insert into the Access DB. It automatically generates the insert
statement for me. Is it possible to assign my own SQL Insert string to the
DataAdapters InsertCommand instead? Or is the one generated by the
COmmandbuilder more efficient even though it doesn't work properly? Also how
can I check to see what the version is for Jet 4 oledb is? Should I use
maybe ODBC connection string to the Access DB instead, maybe more reliable?
What do you think? How can I tell if I have the latest data drivers for
..net?

Thanks,
JJ





William Ryan eMVP said:
JJ:

Hopefully this doesn't get executed over and over b/c you don't want to
recreate datasets all over the place. With that said, assuming that all of
the tb.Text's have actual values, what does your update statement look like?
That's the most likely cause of the problem.

However ,if your saying that you query the Access db and the record looks
empty, but you cut it out and paste it right back in and values appear, you
may need to compact and repair the database.

As an aside, you may want to consider using the Databindings of each of the
textboxes and controls

ie tb.EmployeeNum.DataBindings.Add("Text", dsHours.Tables("Hours"),
"FieldNameThatHoursIsMappedTo")

Then use a BindingManagerBase/BindingContext. When you want to add a new
row, you can just call BindingContext.AddNew...it's probably a lot cleaner
approach.

All in all, if the database doesn't need repaired then it's probably the
update statement, if you post it I'll be glad to take a look at it and try
to figure it out.

Cheers,

Bill
JJ said:
Hi all,

I am trying to add records to a Access DB. I use the following code to do
this. The record gets recorded in the db's but when I read the record back
from the database. All fields report nothing. Now here's the clincher. When
I go to Access and do a query for that record in Access it still reports
nothing even though the record is in table. Now when I cut and paste the
record back into same table. wahlah its fine. Am I maybe missing some kind
of commit in this code? Any ideas? How does the code look below to you?

try
{

DataSet dsHours = new DataSet();
System.Data.DataRow drTS;
//Initialize Data Adapter and Dataset for TS Hours Table
daHours.TableMappings.Add("Table", "Hours" );
daHours.Fill(dsHours ,"Hours");

drTS = dsHours.Tables["Hours"].NewRow();
drTS["TSDate"] = dtEndDate.Value;
drTS["EmployeeID"] = tbEmployeeNum.Text.ToString();
drTS["TSMonReg"] = tbMon.Text.ToString();
drTS["TSTuesReg"] = tbTues.Text.ToString();
drTS["TSWedsReg"] = tbWeds.Text.ToString();
drTS["TSThursReg"] = tbThurs.Text.ToString();
drTS["TSFriReg"] = tbFri.Text.ToString();
drTS["TSSatReg"] = tbSat.Text.ToString();
drTS["TSSunReg"] = tbSun.Text.ToString();
drTS["TSMonOT"] = tbMonOT.Text.ToString();
dsHours.Tables[0].Rows.Add(drTS);

daHours.Update(dsHours);
MessageBox.Show("Record Added Successfully !");
bUpdateTS.Visible = true;
bAddTS.Visible = false;
}
catch (OleDbException err)
{
string errorMessage = "Message: " + err.Message + "\n" +
"Source: " + err.Source + "\n" ;
MessageBox.Show(errorMessage,"Error in DataCon");
}
catch(Exception err)
{
MessageBox.Show(err.Message,"General Error");
}


Thanks,
JJ
Click to expand...
Click to expand...
 
Back
Top