How can I get more information from a System.Data.SqlClient.SqlException?

  • Thread starter Thread starter Eric Harmon
  • Start date Start date
E

Eric Harmon

Hi,

I have an application where, upon inserting data into a table, I'm receiving
a System.Data.SqlClient.SqlException exception. The only information
displayed is "System Error". I've gotten exceptions like this in the past
(with other data adapters), when I've finally determined that what's really
going on is the code is trying to add a record with a bogus foreign key or
something along those lines.

For this particular exception, I haven't been able to figure out what's
going wrong. There has to be some more descriptive text somewhere other
than "System Error". Is there a way to find out what the exact error coming
back from SQL Server is? Something a little more meaningful that "System
Error"?

In case it matters, my code looks like this:

sqlConnection1.Open();
try
{
System.Data.SqlClient.SqlTransaction txn =
sqlConnection1.BeginTransaction();

// SetTransaction just sets the transaction for the insert, update, and
delete commands.
DataHelper.SetTransaction(daEntity, txn);
DataHelper.SetTransaction(daEmployee, txn);
DataHelper.SetTransaction(daOrgPerson, txn);
DataHelper.SetTransaction(daWorkerPositionHistoryByOrgPersonKey, txn);
DataHelper.SetTransaction(daWorkerLocationHistoryByOrgPersonKey, txn);
DataHelper.SetTransaction(daWorkerSupervisorHistoryByOrgPersonKey, txn);
DataHelper.SetTransaction(daWorkerSalaryHistoryByOrgPersonKey, txn);
DataHelper.SetTransaction(daWorkerEmploymentTypeHistoryByOrgPersonKey,
txn);
DataHelper.SetTransaction(daWorkerOrganizationHistoryByPersonKey, txn);

try
{
// Submit the new entities, person, orgperson, and history
daEntity.Update(DataSet.Entity.Select("", "",
DataViewRowState.Added));
daEmployee.Update(DataSet.Person.Select("", "",
DataViewRowState.Added));
daOrgPerson.Update(DataSet.OrgPerson.Select("", "",
DataViewRowState.Added));

daWorkerPositionHistoryByOrgPersonKey.Update(DataSet.WorkerPositionHistory.S
elect("", "", DataViewRowState.Added));

daWorkerLocationHistoryByOrgPersonKey.Update(DataSet.WorkerLocationHistory.S
elect("", "", DataViewRowState.Added));

daWorkerSupervisorHistoryByOrgPersonKey.Update(DataSet.WorkerSupervisorHisto
ry.Select("", "", DataViewRowState.Added));

daWorkerSalaryHistoryByOrgPersonKey.Update(DataSet.WorkerSalaryHistory.Selec
t("", "", DataViewRowState.Added));

daWorkerEmploymentTypeHistoryByOrgPersonKey.Update(DataSet.WorkerEmploymentT
ypeHistory.Select("", "", DataViewRowState.Added));

daWorkerOrganizationHistoryByPersonKey.Update(DataSet.WorkerOrganizationHist
ory.Select("", "", DataViewRowState.Added));

// Submit modified history, orgperson, person, entities

daWorkerOrganizationHistoryByPersonKey.Update(DataSet.WorkerOrganizationHist
ory.Select("", "", DataViewRowState.ModifiedCurrent));

daWorkerEmploymentTypeHistoryByOrgPersonKey.Update(DataSet.WorkerEmploymentT
ypeHistory.Select("", "", DataViewRowState.ModifiedCurrent));

daWorkerSalaryHistoryByOrgPersonKey.Update(DataSet.WorkerSalaryHistory.Selec
t("", "", DataViewRowState.ModifiedCurrent));

daWorkerSupervisorHistoryByOrgPersonKey.Update(DataSet.WorkerSupervisorHisto
ry.Select("", "", DataViewRowState.ModifiedCurrent));

daWorkerLocationHistoryByOrgPersonKey.Update(DataSet.WorkerLocationHistory.S
elect("", "", DataViewRowState.ModifiedCurrent));

daWorkerPositionHistoryByOrgPersonKey.Update(DataSet.WorkerPositionHistory.S
elect("", "", DataViewRowState.ModifiedCurrent));
daOrgPerson.Update(DataSet.OrgPerson.Select("", "",
DataViewRowState.ModifiedCurrent));
daEmployee.Update(DataSet.Person.Select("", "",
DataViewRowState.ModifiedCurrent));
daEntity.Update(DataSet.Entity.Select("", "",
DataViewRowState.ModifiedCurrent));

// Submit deleted history, orgperson, person, entities

daWorkerOrganizationHistoryByPersonKey.Update(DataSet.WorkerOrganizationHist
ory.Select("", "", DataViewRowState.Deleted));

daWorkerEmploymentTypeHistoryByOrgPersonKey.Update(DataSet.WorkerEmploymentT
ypeHistory.Select("", "", DataViewRowState.Deleted));

daWorkerSalaryHistoryByOrgPersonKey.Update(DataSet.WorkerSalaryHistory.Selec
t("", "", DataViewRowState.Deleted));

daWorkerSupervisorHistoryByOrgPersonKey.Update(DataSet.WorkerSupervisorHisto
ry.Select("", "", DataViewRowState.Deleted));

daWorkerLocationHistoryByOrgPersonKey.Update(DataSet.WorkerLocationHistory.S
elect("", "", DataViewRowState.Deleted));

daWorkerPositionHistoryByOrgPersonKey.Update(DataSet.WorkerPositionHistory.S
elect("", "", DataViewRowState.Deleted));
daOrgPerson.Update(DataSet.OrgPerson.Select("", "",
DataViewRowState.Deleted));
daEmployee.Update(DataSet.Person.Select("", "",
DataViewRowState.Deleted));
daEntity.Update(DataSet.Entity.Select("", "",
DataViewRowState.Deleted));

txn.Commit();
}
catch(System.Data.SqlClient.SqlException exc)
{
txn.Rollback();
DataSet.WorkerOrganizationHistory.RejectChanges();
DataSet.WorkerEmploymentTypeHistory.RejectChanges();
DataSet.WorkerSalaryHistory.RejectChanges();
DataSet.WorkerSupervisorHistory.RejectChanges();
DataSet.WorkerLocationHistory.RejectChanges();
DataSet.WorkerPositionHistory.RejectChanges();
DataSet.OrgPerson.RejectChanges();
DataSet.Person.RejectChanges();
DataSet.Entity.RejectChanges();
throw;
}
}
finally
{
sqlConnection1.Close();
}

-Eric Harmon
 
I think that is just the message the debugger gives. You need to examine the
error in your catch (the exc variable) and see its message to find out what
the real error is.
 
Marina,

You're absolutely right. Wow, most exceptions display something meaningful,
so I wasn't expecting this exception to be different. I looped through the
errors in the exception and displayed them manually, and I got something I
can definitely use. Thanks so much!

-Eric
 
Back
Top