Fill method adding new row

  • Thread starter Thread starter Paul Delcogliano
  • Start date Start date
P

Paul Delcogliano

Hi all,

I am having a strange problem with the Fill method. Under certain
circumstances, the Fill method is adding an incomplete datarow to my
dataset. This new incomplete row is causing all sorts of errors (as you can
probably imagine). I have to admit that I don't fully understand all of the
circumstances that are causing this problem, but I will attempt to give you
as much information as possible.

Here's the basics. We are using a strongly typed dataset which contains 3
datatables. The datatables are related to each other via data relation
objects. The datatable hierarchy is parent - child - grandchild. The dataset
is databound at runtime to a user control on a windows form. When the form
is first opened, the fill method is called which populates the dataset just
fine.

We start to see problems after updating or inserting new rows in the 2nd
datatable (mentioned as "child" above) via the controls on the user control
bound to the dataset. After successfully saving the changes to the database
via the data adapter's update method, we are clearing the dataset by calling
the dataset's clear method, and repopulating it by calling the fill method
once again. This time, the fill method is returning all of the correct rows
for the child datatable, but is adding a new row to the datatable. This new
row only has the first two columns in it (as opposed to the 20 or so that a
correct row would have). In addition, the new row's rowstate is set to
"Added" and the value for one of the columns (which has it's Autoincrement
property set to true) is -1 (we are seeding with -1 and incrementing -1).

Here's where things start to get really weird. In some cases, if we remove
the data relation from between the child and grandchild datatables, we don't
get this "phantom" row. If we recreate the data relation between the child
and grandchild datatables, the phantom row returns. In other cases, if we
don't bind one control on the user control at runtime, and the data relation
exists between the child and grandchild tables, we get the phantom row.
There are other scenarios where removing the binding in combination with
removing or adding the data relation but nothing seems consistent.

We believe that the problem lies somewhere in the child datatable since this
is the one common element to all of the various scenarios that cause the
phantom row to appear.

I know that there isn't much to go on here, but I'm hoping that someone has
run into a similar situation where calling the fill method mysteriously adds
a row to a datatable. I should mention that we are populating the dataset
from data stored in SQL Server 2000 and are using VS.Net 2003. The
application we are debugging is a WinForms app written in VB.Net. We are
using the .Net Provider for SQL Server to communicate with the db.

Any help would be greatly appreciated.

Paul
 
Hi Paul,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you are getting "phantom" rows randomly
when trying to fill data into the DataSet. If there is any
misunderstanding, please feel free to let me know.

It's hard to tell why this happens without debugging the program. It seems
to be very strange. Generally, these kind of symptons can be caused by a
wrong setting of table's primary key. Please check if the primary key in
DataSet schema has been set properly according to database table.

If the problem still persists, I would like to see the Xml schema of
DataSet and the code you update and re-fill the DataTable. Could you paste
them here. Thanks.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Kevin,

Thanks for your response.

Our problem isn't exactly random. It can be reproduced depending on the
binding / unbinding of specific controls or removing a data relation.

I checked the primary keys in the dataset against those in the database.
They all match up correctly. Below is the schema from our typed dataset and
the code we use to make the update and re-fill the dataset.

Please let me know if you need anymore information.

typed dataset schema:
-----------------------

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="ReleaseOrderDS"
targetNamespace="http://tempuri.org/ReleaseOrderDS.xsd"
elementFormDefault="qualified"
attributeFormDefault="qualified"
xmlns="http://tempuri.org/ReleaseOrderDS.xsd"
xmlns:mstns="http://tempuri.org/ReleaseOrderDS.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="ReleaseOrderDS" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Release">
<xs:complexType>
<xs:sequence>
<xs:element name="iReleaseID" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:int"
msdata:AutoIncrementSeed="-1" msdata:AutoIncrementStep="-1" />
<xs:element name="iContractID" type="xs:int" minOccurs="0" />
<xs:element name="iContractQuotaMonthID" type="xs:int" minOccurs="0"
/>
<xs:element name="iReleaseTypeID" type="xs:short" minOccurs="0" />
<xs:element name="iCounterpartSmelterContactID" type="xs:int"
minOccurs="0" />
<xs:element name="iMonth" msdata:ReadOnly="false" type="xs:short"
minOccurs="0" />
<xs:element name="iYear" msdata:ReadOnly="false" type="xs:short"
minOccurs="0" />
<xs:element name="vcReleaseNumber" msdata:ReadOnly="false"
type="xs:string" minOccurs="0" />
<xs:element name="vcReferenceString" msdata:ReadOnly="false"
type="xs:string" minOccurs="0" />
<xs:element name="iShipToID" type="xs:int" minOccurs="0" />
<xs:element name="vcCustomerOrderNumber" type="xs:string"
minOccurs="0" />
<xs:element name="vcReleaseNotes" msdata:ReadOnly="false"
type="xs:string" minOccurs="0" />
<xs:element name="vcConsignment" type="xs:string" minOccurs="0" />
<xs:element name="vcCompanyName" msdata:ReadOnly="false"
type="xs:string" minOccurs="0" />
<xs:element name="vcFamilyName" msdata:ReadOnly="false"
type="xs:string" minOccurs="0" />
<xs:element name="dtReleaseDate" type="xs:dateTime" />
<xs:element name="vcContractNumber" msdata:ReadOnly="false"
type="xs:string" minOccurs="0" />
<xs:element name="TREEVIEWLABEL" msdata:ReadOnly="true"
type="xs:string" minOccurs="0" />
<xs:element name="iDocumentStatusID" type="xs:short" />
<xs:element name="vcCustomerReleaseNumber" msdata:ReadOnly="false"
type="xs:string" minOccurs="0" />
<xs:element name="iCompanyTypeID" type="xs:short" minOccurs="0" />
<xs:element name="iCounterpartSmelterID" type="xs:int" minOccurs="0"
/>
<xs:element name="vcQuotaMonth" type="xs:string" minOccurs="0" />
<xs:element name="vcReleaseTypeDesc" msdata:ReadOnly="false"
type="xs:string" minOccurs="0" />
<xs:element name="vcReleaseStatus" msdata:ReadOnly="false"
type="xs:string" minOccurs="0" />
<xs:element name="bToBeProduced" msdata:ReadOnly="false"
type="xs:boolean" minOccurs="0" />
<xs:element name="bActive" msdata:ReadOnly="false" type="xs:boolean"
minOccurs="0" />
<xs:element name="dtLastModified" msdata:ReadOnly="false"
type="xs:base64Binary" minOccurs="0" />
<xs:element name="bOverWrite" msdata:ReadOnly="false"
type="xs:boolean" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Reflections">
<xs:complexType>
<xs:sequence>
<xs:element name="iReleaseReflectionID" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:int"
msdata:AutoIncrementSeed="-1" msdata:AutoIncrementStep="-1" />
<xs:element name="iSalesProductInventoryID" type="xs:int" />
<xs:element name="iPurchaseProductInventoryID" type="xs:int" />
<xs:element name="fQuantityReflected" type="xs:double" />
<xs:element name="iQuantityReflectedMeasureID" type="xs:short" />
<xs:element name="fBundleQuantity" type="xs:double" minOccurs="0" />
<xs:element name="fPieceQuantity" type="xs:double" minOccurs="0" />
<xs:element name="bToBeProduced" type="xs:boolean" />
<xs:element name="bActive" type="xs:boolean" />
<xs:element name="vcQuotaMonth" type="xs:string" minOccurs="0" />
<xs:element name="vcContractNumber" msdata:ReadOnly="true"
type="xs:string" minOccurs="0" />
<xs:element name="vcQuantity" msdata:ReadOnly="true" type="xs:string"
minOccurs="0" />
<xs:element name="vcProductOrigin" msdata:ReadOnly="true"
type="xs:string" minOccurs="0" />
<xs:element name="vcPartialDescriptionWithOrigin"
msdata:ReadOnly="true" type="xs:string" minOccurs="0" />
<xs:element name="iContractID" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Products">
<xs:complexType>
<xs:sequence>
<xs:element name="iReleaseProductID" msdata:AutoIncrement="true"
type="xs:int" msdata:AutoIncrementSeed="-1"
msdata:AutoIncrementStep="-1" />
<xs:element name="iReleaseID" type="xs:int" />
<xs:element name="iProductInventoryID" type="xs:int"
msdata:AutoIncrement="true" msdata:AutoIncrementSeed="-1"
msdata:AutoIncrementStep="-1" />
<xs:element name="iProductID" type="xs:int" />
<xs:element name="iProductInventoryParentID" type="xs:int" />
<xs:element name="iDocumentStatusID" type="xs:short" />
<xs:element name="bReleaseComplete" type="xs:boolean" minOccurs="0"
/>
<xs:element name="fPieceQuantity" msdata:ReadOnly="true"
type="xs:double" minOccurs="0" />
<xs:element name="fBundleQuantity" msdata:ReadOnly="true"
type="xs:double" minOccurs="0" />
<xs:element name="vcNotes" type="xs:string" minOccurs="0" />
<xs:element name="iContractID" type="xs:int" minOccurs="0" />
<xs:element name="iContractQuotaMonthID" type="xs:int" minOccurs="0"
/>
<xs:element name="fQuantity" type="xs:double" />
<xs:element name="iQuantityMeasureID" type="xs:short" />
<xs:element name="fReflectedQuantityRemaining" type="xs:double"
minOccurs="0" />
<xs:element name="iProductOriginID" type="xs:short" minOccurs="0" />
<xs:element name="iProductFamilyID" type="xs:short" minOccurs="0" />
<xs:element name="iProductShapeID" type="xs:short" minOccurs="0" />
<xs:element name="fWeight" type="xs:double" minOccurs="0" />
<xs:element name="iWeightMeasureID" type="xs:short" minOccurs="0" />
<xs:element name="bWeightMax" type="xs:boolean" minOccurs="0" />
<xs:element name="fDiameter" type="xs:double" minOccurs="0" />
<xs:element name="iDiameterMeasureID" type="xs:short" minOccurs="0"
/>
<xs:element name="fLength" type="xs:double" minOccurs="0" />
<xs:element name="iLengthMeasureID" type="xs:short" minOccurs="0" />
<xs:element name="fWidth" type="xs:double" minOccurs="0" />
<xs:element name="iWidthMeasureID" type="xs:short" minOccurs="0" />
<xs:element name="fHeight" type="xs:double" minOccurs="0" />
<xs:element name="iHeightMeasureID" type="xs:short" minOccurs="0" />
<xs:element name="iProductAlloyID" type="xs:short" minOccurs="0" />
<xs:element name="iProductSpecID" type="xs:short" minOccurs="0" />
<xs:element name="dtDeliveryDate" type="xs:dateTime" minOccurs="0" />
<xs:element name="iDeliveryTypeID" type="xs:short" />
<xs:element name="iDeliveryPeriodID" type="xs:short" />
<xs:element name="bActive" type="xs:boolean" minOccurs="0" />
<xs:element name="TREEVIEWLABEL" msdata:ReadOnly="true"
type="xs:string" minOccurs="0" />
<xs:element name="dtLastModified" msdata:ReadOnly="true"
type="xs:base64Binary" minOccurs="0" />
<xs:element name="bOverWrite" msdata:ReadOnly="true"
type="xs:boolean" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:key name="Release_ReleaseID">
<xs:selector xpath=".//mstns:Release" />
<xs:field xpath="mstns:iReleaseID" />
</xs:key>
<xs:key name="Product_ReleaseProducts">
<xs:selector xpath=".//mstns:Products" />
<xs:field xpath="mstns:iReleaseProductID" />
</xs:key>
<xs:key name="Product_ProductInventory">
<xs:selector xpath=".//mstns:Products" />
<xs:field xpath="mstns:iProductInventoryID" />
</xs:key>
<xs:keyref name="Release_Products" refer="Release_ReleaseID">
<xs:selector xpath=".//mstns:Products" />
<xs:field xpath="mstns:iReleaseID" />
</xs:keyref>
<xs:key name="ReleaseOrderDSKey8">
<xs:selector xpath=".//mstns:Reflections" />
<xs:field xpath="mstns:iReleaseReflectionID" />
</xs:key>
<xs:keyref name="Products_Reflections" refer="Product_ProductInventory">
<xs:selector xpath=".//mstns:Reflections" />
<xs:field xpath="mstns:iSalesProductInventoryID" />
</xs:keyref>
</xs:element>
</xs:schema>

' code that calls the update
---------------------------

Protected Sub CallUpdateCommandForAdapter(ByVal daAdapter As
SqlDataAdapter, _
ByVal dataTable As DataTable, ByVal sUserName As String)

' Helper function: used to call the update method of each
adapter.
' This method is called from the Public Save method.

Dim drRow As DataRow

Try

With daAdapter

' This for loop is a workaround. The UI binds controls
to
' each datatable in the dataset. When that happens, the
EndEdit
' method is not being called properly for the bound
data. This loop
' forces each new row to end edit. EndEdit is needed to
propagate the
' "proposed" row data to the "current" row data
For Each drRow In dataTable.Rows

If drRow.RowState = DataRowState.Added Then
drRow.EndEdit()
End If

Next drRow

If Not .UpdateCommand Is Nothing Then
.UpdateCommand.Parameters("@vcUserName").Value =
sUserName
End If

If Not .InsertCommand Is Nothing Then
.InsertCommand.Parameters("@vcUserName").Value =
sUserName
End If

If Not .DeleteCommand Is Nothing Then
.DeleteCommand.Parameters("@vcUserName").Value =
sUserName
End If

.UpdateCommand.Connection.Open()
.Update(dataTable)

' This code is called for any adapter whose
ContinueOnError property is
' set to true. Use the table's GetErrors method to
return an array of
' datarow objects that have errors

End With

Finally

' Close any connections that were opened as a result of
executing Update
If daAdapter.UpdateCommand.Connection.State =
ConnectionState.Open Then
daAdapter.UpdateCommand.Connection.Close()
End If

If daAdapter.InsertCommand.Connection.State =
ConnectionState.Open Then
daAdapter.InsertCommand.Connection.Close()
End If

' Not all delete command objects are instantiated, check
before attempting to
' close connection
If Not daAdapter.DeleteCommand Is Nothing Then

If daAdapter.DeleteCommand.Connection.State =
ConnectionState.Open Then
daAdapter.DeleteCommand.Connection.Close()
End If

End If

End Try

End Sub

' Code that fills the typed dataset

---------------------------------

Public Sub Populate(ByVal iReleaseId As Int32, ByVal localDS As
ReleaseOrderDS)


Try
' The release adapter is used to fill the dataset with data
for all of the tables.
' All other adapters are used for saving data to their
respective tables
_daRelease.SelectCommand.Parameters("@iReleaseID").Value =
iReleaseId

localDS.Clear()

With _daRelease

' Map the tables returned from the stored procedure to
the tables in
' the typed dataset's schema

If Not .TableMappings.Contains("Table") Then

.TableMappings.Add("Table",
localDS.Release.TableName)

End If

If Not .TableMappings.Contains("Table1") Then

.TableMappings.Add("Table1",
localDS.Products.TableName)

End If

If Not .TableMappings.Contains("Table2") Then

.TableMappings.Add("Table2",
localDS.Reflections.TableName)

End If


End With

_daRelease.Fill(localDS)

Finally

If _daRelease.SelectCommand.Connection.State =
ConnectionState.Open Then
_daRelease.SelectCommand.Connection.Close()
End If

End Try

End Sub
 
Hi Paul,

Based on the code and schema, we cannot find evidence which causes the
problem. In your code, we can see that you filled the original DataSet by
clearing it first. Would you please try to fill the data into a newly
created DataSet object and rebind controls to that DataSet to see if the
returned data is right. Use the following instead of localDS.Clear().

localDS = new ReleaseOrderDS();

Please also check the values in localDS using the watch window instead of
binding it to a control both before and after the code changes to see if it
is a data binding problem. HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Kevin,

I took your advice. Instead of clearing the dataset, I filled the data into
a new dataset. This seems to have resolved the "phantom row" problem.
However, it introduces a new problem. The form I am working with has some
controls bound at design time (in addition to those controls on the user
control which are bound at runtime). With the new dataset, the controls
bound at design time don't show any data. It seems like the form level
dataset I use to bind the controls at design time is somehow different than
the new dataset returned from the fill method. How can I do design time
binding with a new dataset?

Paul
 
Hi Paul,

Just as I mentioned in my last post, the new DataSet, which was filled, is
another object. However, the data binding source of the controls was still
pointing to the old object which has been garbage collected. So the
controls show nothing. So I think we have to re-bind all the controls after
re-filling the DataSet at runtime.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Kevin,

If that is the only solution, then I guess that is what we will have to do.
But it begs the question; what is the benefit to design time binding to a
typed dataset if I have to rebind all of the controls at runtime everytime I
fill the dataset? Is there another way? Can you tell me of some additional
situations (other than those already addressed) that cause the fill method
to add a "phantom row" to a datatable? Maybe we can troubleshoot the problem
more if we know what causes the fill method to behave this way.

Thanks for all of your help,

Paul
 
Hi Paul,

We have to rebind all the controls not because the DataSet is re-filled,
but because we have recreated a new DataSet object. This is not the best
solution, if we can make further debugging on it.

It's hard to tell what causes this problem. Generally, these kind of
"phantom rows" can be caused by incorrect schemas and primary keys. Please
try to check the schemas after clearing the DataSet.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Paul,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Kevin,

I just wanted to let you know that we are considering the problem resolved.
We decided to go with your suggestion of rebinding the controls at runtime
to a new instance of the dataset.

Thanks for all of your help,

Paul
 
Hi Paul,

It was nice to hear that you have had the problem resolved. Thanks for
sharing your experience with all the people here. If you have any
questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top