Storing changes to a dataset.

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
 
S

Scot Rose [MSFT]

Hi Bill, Please check the version of your OLEDB Provider and see if the following KB Applies... (See the section about using it with ADO.Net)

321631 BUG: Visual FoxPro OLE DB Provider Only Works with CHAR ADODB.Command
http://support.microsoft.com/?id=321631

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided “AS IS”, with no warranties, and confers no rights.




--------------------
From: "Bill Simakis" <[email protected]>
Subject: Storing changes to a dataset.
Date: Fri, 25 Jul 2003 12:48:12 -0400
Keywords: OleDB, FoxPro
Lines: 213
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <#[email protected]>
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: 207.35.240.157
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:56730
X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

not able to fill dataset with OleDbDataAdapter 1
OleDbException: Could not update; currently locked 4
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll 2
Help: OleDbException: Operation must use an updateable query 1
OleDbException: Syntax error in UPDATE statement 2
System.Data.Common.UnsafeNativeMethods.SetErrorInfo 1
Help: SQL syntax error??? 2
OleDB Stored Procedure returns Data Rows 2

Top