I thought this might be the case, so I reduced the number of parameters to
two so I could easily verify that the parameters are correct.
This is the class:
using System;
using System.Data ;
using System.Data.OleDb ;
using System.Data.Common ;
using System.Text ;
namespace Avasoft
{
/// <summary>
/// Summary description for Leads.
/// </summary>
public class Leads
{
#region Initialize
private static Leads theOnlyOne = new Leads();
public string TableName = "Leads" ;
public DataAccess oDataAccess = DataAccess.GetInstance();
[System.Diagnostics.DebuggerStepThrough()]
public static Leads GetInstance()
{
return theOnlyOne ;
}
public Leads()
{
//
// TODO: Add constructor logic here
//
}
#endregion
#region GetLeadListing
//The updatecommand is used to get the parameter collection, which
//tells us which fields to get.
public string GetLeadsListingSelectSQL(CDataSet oDataSet)
{
StringBuilder SQL = new StringBuilder() ;
SQL.Append("SELECT ");
foreach (OleDbParameter oParameter in
oDataSet.DataAdapter.UpdateCommand.Parameters)
{
SQL.Append(" " + oParameter.SourceColumn+ ",");
}
SQL.Remove(SQL.Length - 1,1);
SQL.Append(" FROM " + this.TableName);
return SQL.ToString() ;
}
public string GetLeadsListingUpdateSQL(OleDbCommand oCommand)
{
StringBuilder SQL = new StringBuilder() ;
SQL.Append("UPDATE " + this.TableName + " SET " );
foreach (OleDbParameter oParameter in oCommand.Parameters)
{
SQL.Append(" " + oParameter.SourceColumn + " = ? ,");
}
SQL.Remove(SQL.Length - 1,1);
SQL.Append(" where LeadID = ?") ;
return SQL.ToString() ;
}
//Add The where clause, and the where clause parameter that it uses.
public void AddWhereClauseParameter(OleDbCommand oUpdateCommand)
{
//For the Where Clause.
//LeadTable.LeadID
oUpdateCommand.Parameters.Add( LeadsTable.LeadId.FieldName,
LeadsTable.LeadId.OleDbType,
LeadsTable.LeadId.Size,
LeadsTable.LeadId.Source);
}
public OleDbCommand GetLeadListingUpdateCommand()
{
OleDbCommand oUpdateCommand = new OleDbCommand();
oUpdateCommand.Connection = oDataAccess.oOleDbConnection ;
this.AddUpdateParameters(oUpdateCommand);
oUpdateCommand.CommandText = this.GetLeadsListingUpdateSQL(oUpdateCommand);
this.AddWhereClauseParameter(oUpdateCommand);
return oUpdateCommand ;
}
public OleDbCommand GetLeadListingSelectCommand(CDataSet oDataSet)
{
string SQLSelect = this.GetLeadsListingSelectSQL(oDataSet) ;
OleDbCommand oSelectCommand = new
OleDbCommand(SQLSelect,this.oDataAccess.oOleDbConnection);
return oSelectCommand ;
}
public OleDbCommand GetLeadListingInsertCommand()
{
string SQLInsert = "Insert into Leads (LeadID) Values (99999)" ;
OleDbCommand oInsertCommand = new
OleDbCommand(SQLInsert,this.oDataAccess.oOleDbConnection);
return oInsertCommand ;
}
public OleDbCommand GetLeadListingDeleteCommand()
{
string SQLDelete = "Delete * from Leads" ;
OleDbCommand oDeleteCommand = new
OleDbCommand(SQLDelete,this.oDataAccess.oOleDbConnection);
return oDeleteCommand ;
}
public CDataSet GetLeadListingDataSet()
{
CDataSet ds = new CDataSet(); //The DataSet Object which will be returned.
ds.DataAdapter = new OleDbDataAdapter(); // The data Adapter through
which we will update data.
ds.DataAdapter.UpdateCommand = this.GetLeadListingUpdateCommand(); //The
Update Command.
ds.DataAdapter.SelectCommand = this.GetLeadListingSelectCommand(ds);
ds.DataAdapter.InsertCommand = this.GetLeadListingInsertCommand(); //The
Insert Command.
ds.DataAdapter.DeleteCommand = this.GetLeadListingDeleteCommand(); //The
Insert Command.
try
{
int myInt = ds.DataAdapter.Fill(ds,"Leads");
return ds;
}
catch (Exception oException)
{
throw new ApplicationException(oException.Message);
}
finally
{
//this.oOleDbConnection.Close();
}
//
}
public int LeadsListingSave(CDataSet oDataSet)
{
try
{
string cString = "Leads";
//The next line is where the null reference error.
return oDataSet.DataAdapter.Update(oDataSet, cString); //
LeadsTable.TableName);
}
catch (DBConcurrencyException ex)
{
string customErrorMessage;
customErrorMessage = "Concurrency violation\n";
customErrorMessage += ex.Row[0].ToString();
// Replace the above code with appropriate business logic
// to resolve the concurrency violation.
MessageBox.Show(customErrorMessage);
throw ex ;
}
}
#endregion
#region AddUpdateParameters
public void AddUpdateParameters(OleDbCommand oUpdateCommand)
{
// //LeadsTable.AltContactEmail
// oUpdateCommand.Parameters.Add( LeadsTable.AltContactEmail.FieldName,
// LeadsTable.AltContactEmail.OleDbType,
// LeadsTable.AltContactEmail.Size,
// LeadsTable.AltContactEmail.Source);
//
//
// oUpdateCommand.Parameters.Add( LeadsTable.AltContactExt.FieldName,
// LeadsTable.AltContactExt.OleDbType,
// LeadsTable.AltContactExt.Size,
// LeadsTable.AltContactExt.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.AltContactLast.FieldName,
// LeadsTable.AltContactLast.OleDbType,
// LeadsTable.AltContactLast.Size,
// LeadsTable.AltContactLast.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.AltContactName.FieldName,
// LeadsTable.AltContactName.OleDbType,
// LeadsTable.AltContactName.Size,
// LeadsTable.AltContactName.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.AltContactPhone.FieldName,
// LeadsTable.AltContactPhone.OleDbType,
// LeadsTable.AltContactPhone.Size,
// LeadsTable.AltContactPhone.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.AltContactTitle.FieldName,
// LeadsTable.AltContactTitle.OleDbType,
// LeadsTable.AltContactTitle.Size,
// LeadsTable.AltContactTitle.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.Budget.FieldName,
// LeadsTable.Budget.OleDbType,
// LeadsTable.Budget.Size,
// LeadsTable.Budget.Source);
oUpdateCommand.Parameters.Add( LeadsTable.CompanyName.FieldName,
LeadsTable.CompanyName.OleDbType,
LeadsTable.CompanyName.Size,
LeadsTable.CompanyName.Source);
// oUpdateCommand.Parameters.Add( LeadsTable.CompetitorId.FieldName,
// LeadsTable.CompetitorId.OleDbType,
// LeadsTable.CompetitorId.Size,
// LeadsTable.CompetitorId.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.ContactAltExt.FieldName,
// LeadsTable.ContactAltExt.OleDbType,
// LeadsTable.ContactAltExt.Size,
// LeadsTable.ContactAltExt.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.ContactAltPhone.FieldName,
// LeadsTable.ContactAltPhone.OleDbType,
// LeadsTable.ContactAltPhone.Size,
// LeadsTable.ContactAltPhone.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.ContactEmail.FieldName,
// LeadsTable.ContactEmail.OleDbType,
// LeadsTable.ContactEmail.Size,
// LeadsTable.ContactEmail.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.ContactExt.FieldName,
// LeadsTable.ContactExt.OleDbType,
// LeadsTable.ContactExt.Size,
// LeadsTable.ContactExt.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.ContactFax.FieldName,
// LeadsTable.ContactFax.OleDbType,
// LeadsTable.ContactFax.Size,
// LeadsTable.ContactFax.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.ContactFirstName.FieldName,
// LeadsTable.ContactFirstName.OleDbType,
// LeadsTable.ContactFirstName.Size,
// LeadsTable.ContactFirstName.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.ContactLastName.FieldName,
// LeadsTable.ContactLastName.OleDbType,
// LeadsTable.ContactLastName.Size,
// LeadsTable.ContactLastName.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.ContactPhone.FieldName,
// LeadsTable.ContactPhone.OleDbType,
// LeadsTable.ContactPhone.Size,
// LeadsTable.ContactPhone.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.ContactTitle.FieldName,
// LeadsTable.ContactTitle.OleDbType,
// LeadsTable.ContactTitle.Size,
// LeadsTable.ContactTitle.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.CreateDate.FieldName,
// LeadsTable.CreateDate.OleDbType,
// LeadsTable.CreateDate.Size,
// LeadsTable.CreateDate.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.CreatorUser.FieldName,
// LeadsTable.CreatorUser.OleDbType,
// LeadsTable.CreatorUser.Size,
// LeadsTable.CreatorUser.Source);
//
// oUpdateCommand.Parameters.Add(
LeadsTable.CurrentContractLengthNumber.FieldName,
// LeadsTable.CurrentContractLengthNumber.OleDbType,
// LeadsTable.CurrentContractLengthNumber.Size,
// LeadsTable.CurrentContractLengthNumber.Source);
//
// oUpdateCommand.Parameters.Add(
LeadsTable.CurrentContractLengthTime.FieldName,
// LeadsTable.CurrentContractLengthTime.OleDbType,
// LeadsTable.CurrentContractLengthTime.Size,
// LeadsTable.CurrentContractLengthTime.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.DayClean.FieldName,
// LeadsTable.DayClean.OleDbType,
// LeadsTable.DayClean.Size,
// LeadsTable.DayClean.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.DayPorter.FieldName,
// LeadsTable.DayPorter.OleDbType,
// LeadsTable.DayPorter.Size,
// LeadsTable.DayPorter.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.EstBidDate.FieldName,
// LeadsTable.EstBidDate.OleDbType,
// LeadsTable.EstBidDate.Size,
// LeadsTable.EstBidDate.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.FrequencyOfService.FieldName,
// LeadsTable.FrequencyOfService.OleDbType,
// LeadsTable.FrequencyOfService.Size,
// LeadsTable.FrequencyOfService.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.GroupId.FieldName,
// LeadsTable.GroupId.OleDbType,
// LeadsTable.GroupId.Size,
// LeadsTable.GroupId.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.HasDayPorter.FieldName,
// LeadsTable.HasDayPorter.OleDbType,
// LeadsTable.HasDayPorter.Size,
// LeadsTable.HasDayPorter.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.HowLongNumber.FieldName,
// LeadsTable.HowLongNumber.OleDbType,
// LeadsTable.HowLongNumber.Size,
// LeadsTable.HowLongNumber.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.HowLongTime.FieldName,
// LeadsTable.HowLongTime.OleDbType,
// LeadsTable.HowLongTime.Size,
// LeadsTable.HowLongTime.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.IndustryId.FieldName,
// LeadsTable.IndustryId.OleDbType,
// LeadsTable.IndustryId.Size,
// LeadsTable.IndustryId.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.LastEventDate.FieldName,
// LeadsTable.LastEventDate.OleDbType,
// LeadsTable.LastEventDate.Size,
// LeadsTable.LastEventDate.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.LastEventDetails.FieldName,
// LeadsTable.LastEventDetails.OleDbType,
// LeadsTable.LastEventDetails.Size,
// LeadsTable.LastEventDetails.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.LeadId.FieldName,
// LeadsTable.LeadId.OleDbType,
// LeadsTable.LeadId.Size,
// LeadsTable.LeadId.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.LeadSource.FieldName,
// LeadsTable.LeadSource.OleDbType,
// LeadsTable.LeadSource.Size,
// LeadsTable.LeadSource.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.LocalOtherCity.FieldName,
// LeadsTable.LocalOtherCity.OleDbType,
// LeadsTable.LocalOtherCity.Size,
// LeadsTable.LocalOtherCity.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.MailingAddress1.FieldName,
// LeadsTable.MailingAddress1.OleDbType,
// LeadsTable.MailingAddress1.Size,
// LeadsTable.MailingAddress1.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.MailingAddress2.FieldName,
// LeadsTable.MailingAddress2.OleDbType,
// LeadsTable.MailingAddress2.Size,
// LeadsTable.MailingAddress2.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.MailingAddress3.FieldName,
// LeadsTable.MailingAddress3.OleDbType,
// LeadsTable.MailingAddress3.Size,
// LeadsTable.MailingAddress3.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.MailingCity.FieldName,
// LeadsTable.MailingCity.OleDbType,
// LeadsTable.MailingCity.Size,
// LeadsTable.MailingCity.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.MailingStateCode.FieldName,
// LeadsTable.MailingStateCode.OleDbType,
// LeadsTable.MailingStateCode.Size,
// LeadsTable.MailingStateCode.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.MailingZip.FieldName,
// LeadsTable.MailingZip.OleDbType,
// LeadsTable.MailingZip.Size,
// LeadsTable.MailingZip.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.MainAddress1.FieldName,
// LeadsTable.MainAddress1.OleDbType,
// LeadsTable.MainAddress1.Size,
// LeadsTable.MainAddress1.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.MainAddress2.FieldName,
// LeadsTable.MainAddress2.OleDbType,
// LeadsTable.MainAddress2.Size,
// LeadsTable.MainAddress2.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.MainAddress3.FieldName,
// LeadsTable.MainAddress3.OleDbType,
// LeadsTable.MainAddress3.Size,
// LeadsTable.MainAddress3.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.MainCity.FieldName,
// LeadsTable.MainCity.OleDbType,
// LeadsTable.MainCity.Size,
// LeadsTable.MainCity.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.MainStateCode.FieldName,
// LeadsTable.MainStateCode.OleDbType,
// LeadsTable.MainStateCode.Size,
// LeadsTable.MainStateCode.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.MainZip.FieldName,
// LeadsTable.MainZip.OleDbType,
// LeadsTable.MainZip.Size,
// LeadsTable.MainZip.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.Notes.FieldName,
// LeadsTable.Notes.OleDbType,
// LeadsTable.Notes.Size,
// LeadsTable.Notes.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.NumEmployees.FieldName,
// LeadsTable.NumEmployees.OleDbType,
// LeadsTable.NumEmployees.Size,
// LeadsTable.NumEmployees.Source);
//
// oUpdateCommand.Parameters.Add(
LeadsTable.NumLocationsOtherCities.FieldName,
// LeadsTable.NumLocationsOtherCities.OleDbType,
// LeadsTable.NumLocationsOtherCities.Size,
// LeadsTable.NumLocationsOtherCities.Source);
//
//
// oUpdateCommand.Parameters.Add( LeadsTable.Prefix.FieldName,
// LeadsTable.Prefix.OleDbType,
// LeadsTable.Prefix.Size,
// LeadsTable.Prefix.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.PropAmount.FieldName,
// LeadsTable.PropAmount.OleDbType,
// LeadsTable.PropAmount.Size,
// LeadsTable.PropAmount.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.ProposalDate.FieldName,
// LeadsTable.ProposalDate.OleDbType,
// LeadsTable.ProposalDate.Size,
// LeadsTable.ProposalDate.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.ReferedBy.FieldName,
// LeadsTable.ReferedBy.OleDbType,
// LeadsTable.ReferedBy.Size,
// LeadsTable.ReferedBy.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.SICCode.FieldName,
// LeadsTable.SICCode.OleDbType,
// LeadsTable.SICCode.Size,
// LeadsTable.SICCode.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.SqFootage.FieldName,
// LeadsTable.SqFootage.OleDbType,
// LeadsTable.SqFootage.Size,
// LeadsTable.SqFootage.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.Status.FieldName,
// LeadsTable.Status.OleDbType,
// LeadsTable.Status.Size,
// LeadsTable.Status.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.TimeStamp.FieldName,
// LeadsTable.TimeStamp.OleDbType,
// LeadsTable.TimeStamp.Size,
// LeadsTable.TimeStamp.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.UserDefined1Label.FieldName,
// LeadsTable.UserDefined1Label.OleDbType,
// LeadsTable.UserDefined1Label.Size,
// LeadsTable.UserDefined1Label.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.UserDefined1Value.FieldName,
// LeadsTable.UserDefined1Value.OleDbType,
// LeadsTable.UserDefined1Value.Size,
// LeadsTable.UserDefined1Value.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.UserDefined2Label.FieldName,
// LeadsTable.UserDefined2Label.OleDbType,
// LeadsTable.UserDefined2Label.Size,
// LeadsTable.UserDefined2Label.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.UserDefined3Label.FieldName,
// LeadsTable.UserDefined3Label.OleDbType,
// LeadsTable.UserDefined3Label.Size,
// LeadsTable.UserDefined3Label.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.UserDefined3Value.FieldName,
// LeadsTable.UserDefined3Value.OleDbType,
// LeadsTable.UserDefined3Value.Size,
// LeadsTable.UserDefined3Value.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.UserDefined4Label.FieldName,
// LeadsTable.UserDefined4Label.OleDbType,
// LeadsTable.UserDefined4Label.Size,
// LeadsTable.UserDefined4Label.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.UserDefined4Value.FieldName,
// LeadsTable.UserDefined4Value.OleDbType,
// LeadsTable.UserDefined4Value.Size,
// LeadsTable.UserDefined4Value.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.UserId.FieldName,
// LeadsTable.UserId.OleDbType,
// LeadsTable.UserId.Size,
// LeadsTable.UserId.Source);
//
// oUpdateCommand.Parameters.Add( LeadsTable.Website.FieldName,
// LeadsTable.Website.OleDbType,
// LeadsTable.Website.Size,
// LeadsTable.Website.Source);
}
#endregion
}
}
--
Greg Gum
Bob Powell said:
It looks like you might have a SQL statement that has a variable in it such
as [something] and no corresponding parameter in the OleDbCommmand. Make
sure you've added enough parameters.
If you don't find it immediately post your code.
--
Bob Powell [MVP]
Visual C#, System.Drawing
Ramuseco Limited .NET consulting
http://www.ramuseco.com
Find great Windows Forms articles in Windows Forms Tips and Tricks
http://www.bobpowell.net/tipstricks.htm
Answer those GDI+ questions with the GDI+ FAQ
http://www.bobpowell.net/faqmain.htm
All new articles provide code in C# and VB.NET.
Subscribe to the RSS feeds provided and never miss a new article.
Greg said:
Hi,
I have been unable to resolve this error message: Object Ref not set to an
instance of an object.
The issue is that I cannot determine which object reference is causing the
problem.
The line of user code that gives the error message is:
return oDataSet.DataAdapter.Update(oDataSet, "Leads");
I have ensured that oDataSet is not null.
The stack at the time of the error , including non-user code as it doesn't
appear that it is user code that is the issue, is:
system.data.dll!System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(System.Data.Common.UnsafeNativeMethods.tagDBPARAMS
dbParams = {System.Data.Common.UnsafeNativeMethods.tagDBPARAMS},
System.Object executeResult = <undefined value>)
Is the "undefined value" above the issue?
Is there another way to trace down what the issue is? It obviously has to
do with the data update, but I have carefully gone through the DataAdapter
and all appears to be well there. There must be a way of finding the
offending object reference.
Thanks, and Happy 4th to all!
Greg
