B
Bill Simakis
Hello,
I have posted the original problem to the newsgroup earlier this week and
thought I would try again with my latest problems.
I am developing a Data access component which is to access data from a
Foxpro database (schema below) using the OleDbProvider.
So far I have no problem getting information out of the database into a
dataset but storing changes back to the Database is causing me alot of
exceptions and I can't seem to understand why.
Any help would be greatly appreciated. Thanks
Bill
I am developing in Visual Studio .Net 2003 (C#) and using Visual Foxpro 7.0
SP1.
Database Schema
------------------
Table1
irow : integer [primary key] [default value calculated by stored procedure =
column max value + 1]
name : character (32)
value : character (254)
owner: integer [Foreign key to table 2]
host: character (32)
Table2:
id: integer [Primary key]
name: character(32)
Description: character (254)
Sql statements
---------------
(Each is encapsulated with an OleDbCommand object generated by the visual
studio designer)
1) SELECT table1.irow, table1.name, table1.value, table1.owner, host
FROM table1, table2
WHERE table1.owner = table2.id
AND table2.name = ?
2) INSERT INTO table1 (name, value, owner, host )
VALUES (?, ?, ?, ?)
3) UPDATE table1 SET name = ?, host = ?
WHERE irow = ?
4) DELETE FROM table1
WHERE irow = ?
Code
------
Originally I tried to use the OleDbDataAdapter that generated the DataSet,
to store the changes.
public virtual void SetParamter (DataSet dataset )
{
OleDbCommandBuilder cb = new OleDbCommandBuilder( dataAdapter );
dataAdapter.Update( dataset );
}
This generated the following exception:
System.Data.OleDb.OleDbException: No value given for one or more required
parameters.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at DataAccess.SetParameters(DataSet dataset) in
e:\MyProjects\dataaccess.cs:line 168
The Table mapping is configured in the DataAdapter.
I then tried creating my own commands and associating it with the
DataAdapter, instead of using the command builder, and this resulted in the
same exception. Next I tried to do the update myself by iterating through
the dataset and calling the appropriate Add/Modify/Delete method depending
on the DataRow's RowState. This generated exceptions as well, shown below
along with each method's code.
1) Insert method:
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 ( Exception e )
{
Console.WriteLine( e.ToString() );
}
finally
{
if ( DbConnection != null )
DbConnection.Close();
}
return retVal;
}
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) UpdateRecordCommand:
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 ( Exception e )
{
Console.WriteLine( e.ToString() );
}
finally
{
if ( DbConnection != null )
DbConnection.Close();
}
return retVal;
}
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
3) DeleteRecordCommand:
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 ( Exception e )
{
Console.WriteLine( e.ToString() );
}
finally
{
if ( DbConnection != null )
DbConnection.Close();
}
return retVal;
}
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
I have posted the original problem to the newsgroup earlier this week and
thought I would try again with my latest problems.
I am developing a Data access component which is to access data from a
Foxpro database (schema below) using the OleDbProvider.
So far I have no problem getting information out of the database into a
dataset but storing changes back to the Database is causing me alot of
exceptions and I can't seem to understand why.
Any help would be greatly appreciated. Thanks
Bill
I am developing in Visual Studio .Net 2003 (C#) and using Visual Foxpro 7.0
SP1.
Database Schema
------------------
Table1
irow : integer [primary key] [default value calculated by stored procedure =
column max value + 1]
name : character (32)
value : character (254)
owner: integer [Foreign key to table 2]
host: character (32)
Table2:
id: integer [Primary key]
name: character(32)
Description: character (254)
Sql statements
---------------
(Each is encapsulated with an OleDbCommand object generated by the visual
studio designer)
1) SELECT table1.irow, table1.name, table1.value, table1.owner, host
FROM table1, table2
WHERE table1.owner = table2.id
AND table2.name = ?
2) INSERT INTO table1 (name, value, owner, host )
VALUES (?, ?, ?, ?)
3) UPDATE table1 SET name = ?, host = ?
WHERE irow = ?
4) DELETE FROM table1
WHERE irow = ?
Code
------
Originally I tried to use the OleDbDataAdapter that generated the DataSet,
to store the changes.
public virtual void SetParamter (DataSet dataset )
{
OleDbCommandBuilder cb = new OleDbCommandBuilder( dataAdapter );
dataAdapter.Update( dataset );
}
This generated the following exception:
System.Data.OleDb.OleDbException: No value given for one or more required
parameters.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at DataAccess.SetParameters(DataSet dataset) in
e:\MyProjects\dataaccess.cs:line 168
The Table mapping is configured in the DataAdapter.
I then tried creating my own commands and associating it with the
DataAdapter, instead of using the command builder, and this resulted in the
same exception. Next I tried to do the update myself by iterating through
the dataset and calling the appropriate Add/Modify/Delete method depending
on the DataRow's RowState. This generated exceptions as well, shown below
along with each method's code.
1) Insert method:
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 ( Exception e )
{
Console.WriteLine( e.ToString() );
}
finally
{
if ( DbConnection != null )
DbConnection.Close();
}
return retVal;
}
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) UpdateRecordCommand:
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 ( Exception e )
{
Console.WriteLine( e.ToString() );
}
finally
{
if ( DbConnection != null )
DbConnection.Close();
}
return retVal;
}
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
3) DeleteRecordCommand:
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 ( Exception e )
{
Console.WriteLine( e.ToString() );
}
finally
{
if ( DbConnection != null )
DbConnection.Close();
}
return retVal;
}
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