OleDbDataAdapter problems

[Bill Simakis]

Hello,

I am working on a data access component which access a foxpro db using the
OleDb provider for foxpro. In my component I am using a OleDbDataAdapter to
fill a dataset which is return to user of my component. This works fine,
however when I try and use the same OleDbDataAdapter to update the DB with
the information contained in a DataSet I get an NullObjectRefenence
exception.

I have used the DataAdpater configuration wizard but it encounters an error
with my select statement due to the parameter, hence does not generate an
insert, update or delete command.

Thanks in advance for the help.

Bill



Dev environment: VS .NET 2003
OleDbProvider : included with Visual FoxPro 7.0

My Database schema is as follows:

Table1
--------------
irow -> Primary key (Default value = Max value + 1)
name -> string
value -> string
owner -> integer (Foreign key to Table 2)
host -> string

Table2
------------
id -> integer Primary Key
name -> string
description -> string

My Select statement:
SELECT name, value, owner, host FROM table1,table2 WHERE table1.owner =
table2.id AND table2.name = ?

My insert statement is below:
INSERT INTO table1 (name, value, owner, host) VALUES (?,?,?,?)

My Update Statement:
UPDATE table1 SET name = ?, value = ?, host = ? WHERE irow = ?

My delete statement:
DELETE FROM table1 WHERE irow =?

The following is the code I use to get the DataSet:

dataAdapter.SelectCommand.Parameters["OwnerName"].Value = OwnerName;
if ( dataAdapter.Fill( dataset ) == 0 )
{
dataset = null;
}

And to update the DB,

ParameterSetAdapter.Update( dataset );

 

[Bill Simakis]

In an effort to continue my development, I decided to implement the database
update myself by iterating through the dataset and calling an appropriate
method to Add, modify or delete records depending on the DataRows RowState.
This has lead me to yet more excpetions that I do not understand. I have
included my code and the exception messages I get for each command. Does
anyone have any ideas?

Thanks.

Bill

I am using Visual Studio .Net 2003, C# and Visual FoxPro 7.0.

Database Schema:
-------------------
Table1
irow : integer [Primary Key] [Default value from stored procedure = Max
column value + 1]
name: varchar (32)
value: varchar (254)
owner: integer [Foreign key to Table2]
host: varchar (32)

Table2

id: integer
name: varchar (32)
Description: varchar (254)

OleDbCommands (added using Visual Studio designer).
--------------------------------------------------------
-CommandText shown for each command.
-All share DbConnection which connect to the FoxPro DB.

1) InsertRecordCommand
INSERT INTO table1 (name, value, owner, host) VALUES ( ?,?,?,?)
2) UpdateRecordCommand
UPDATE table1 SET value = ?, host = ? WHERE irow = ?
3) DeleteRecordCommand
DELETE FROM table1 WHERE irow = ?

Methods:
----------------
protected override bool AddRecord ( DataRow record )
{
bool retVal = false;
InsertRecordCommand.Parameters["ParameterName"].Value = record["name"];
InsertRecordCommand.Parameters["ParameterValue"].Value =
record["value"];
InsertRecordCommand.Parameters["OwnerId"].Value = this.OwnerId;
InsertRecordCommand.Parameters["Host"].Value = record["host"];
try
{
DbConnection.Open();
if ( InsertRecordCommand.ExecuteNonQuery() == 1 )
retVal = true;
}
catch ( OleDbException e )
{
Console.WriteLine( e.Message );
}
catch ( Exception e )
{
Console.WriteLine( e.ToString() );
}
finally
{
if ( DbConnection != null )
DbConnection.Close();
}
return retVal;
}

protected override bool ModifyRecord( DataRow record )
{
bool retVal = false;
UpdateRecordCommand.Parameters["ParameterValue"].Value =
record["value"];
UpdateRecordCommand.Parameters["Host"].Value = record["host"];
UpdateRecordCommand.Parameters["Irow"].Value = record["irow"];
try
{
DbConnection.Open();
if ( UpdateRecordCommand.ExecuteNonQuery() == 1 )
retVal = true;
}
catch ( OleDbException e )
{
Console.WriteLine( e.ToString() );
}
finally
{
if ( DbConnection != null )
DbConnection.Close();
}
return retVal;
}

protected override bool DeleteRecord( DataRow record )
{
bool retVal = false;
record.RejectChanges(); //To be able to get irow value from record
DeleteRecordCommand.Parameters["Irow"].Value = record["irow"];
record.Delete(); //Mark as deleted again.
try
{
DbConnection.Open();
if ( DeleteRecordCommand.ExecuteNonQuery() == 1 )
retVal = true;
}
catch ( OleDbException e )
{
Console.WriteLine( e.Message );
}
catch ( Exception e )
{
Console.WriteLine( e.ToString() );
}
finally
{
if ( DbConnection != null )
DbConnection.Close();
}
return retVal;
}

Exceptions Generated:
------------------------

1) InsertRecordCommand:
System.NullReferenceException: Object reference not set to an instance of an
object.
at System.Data.Common.ICommandText.Execute(IntPtr pUnkOuter, Guid riid,
tagDBPARAMS pDBParams, Int32& pcRowsAffected, Object& ppRowset)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at DataAccess.AddRecord(DataRow record) in
e:\MyProjects\dataaccess.cs:line 227

2) DeleteRecordCommand:
System.NullReferenceException: Object reference not set to an instance of an
object.
at System.Data.Common.ICommandText.Execute(IntPtr pUnkOuter, Guid riid,
tagDBPARAMS pDBParams, Int32& pcRowsAffected, Object& ppRowset)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at DataAccess.DeleteRecord(DataRow record) in
e:\MyProjects\dataaccess.cs:line 303

3) UpdateRecordCommand:
System.Data.OleDb.OleDbException: Syntax error.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at DataAccess.ModifyRecord(DataRow record) in
e:\MyProjects\dataaccess.cs:line 267