VB.net Dataview Rowfilter behaviour problems

[AlexW]

Hi

I am in the process of developing an inventory application in visual basic. I keep coming up against a problem with using the dataview.rowfil ter property.
Basically what happens is this:

-a global dataview is created
-The user enters new part information into a form.
-A function is called to check to see if the part is a new part (so that two identical part numbers are not added to the database.
-The check is done by setting the rowfilter value to the string representation of the partnumber and checking if the dataview's .count method returns 0 (ie the rowfilter that was applied hides everything)
-if the function returns true then the .addnew() method of the dataview is invoked and the part details are added to the dataview. (the updating of the actual database will be done later through a user dialog)

The above works perfectly for the first part number but not for any parts added after this. The reason is that for some reason the rowfilter that is applied does not filter out the newly added part, even though the row filter should have filtered this part out because the part Numbers are completely different.
I have checked that the values are being correctly set by stepping through the function line by line and have also made sure that no other functions are being called during its execution.

Any Ideas?

Here is the code of the function below:

Private Function checkPartIsNew(ByVal partNumber As String) As Boolean
        Dim filterString As String

        'variables used for debugging purposes only
        Dim test As DataRowView 'for testing
        Dim testpartNum As String 'for testing
        Dim messageString As String 'for testing

        'checking to see if a row filter already exists
        addPartsDataView.RowStateFilter = DataViewRowState.OriginalRows
        If IsNothing(addPartsDataView.RowFilter) = False Then

            messageString = "rowFilterApplied, "

            'there must already be a rowfilter applied, take steps to preserve it
            filterString = addPartsDataView.RowFilter
            addPartsDataView.RowFilter = Nothing 'put in during debugging to ensure row filter is removed

            'set row filter to partNumber
            addPartsDataView.RowFilter = "partNum = '" + partNumber + "'"

            'test if part is new/unique
            If addPartsDataView.Count = 0 Then
                'part must be unique (filter returns no rows)
                'reapply filter
                addPartsDataView.RowFilter = filterString
                Return True
            End If

            messageString = messageString + "Addpartsdataview.count equals " + addPartsDataView.Count.ToString + ", "

        Else
            'no row filter is currently applied
            messageString = "No row filter applied, "
            addPartsDataView.RowFilter = Nothing

            'set row filter to partNumber
            addPartsDataView.RowFilter = "partNum = '" + partNumber + "'"
            
            'test if part is new/unique
            If addPartsDataView.Count = 0 Then
            'part must be unique (filter returns no rows)
                'remove filter
                addPartsDataView.RowFilter = Nothing
                Return True
            End If

            messageString = messageString + "Addpartsdataview.count equals " + addPartsDataView.Count.ToString + ", "

        End If

        'for debugging only
        test = addPartsDataView(0)
        testpartNum = test.Item("PartNum")
        MsgBox(messageString)

        'if we are here then part already exists
        Return False
    End Function

Any Help Would be most appreciated.

Thanks in advance

[kenobewan]

My guess is that your dataview is losing the subsequent rows by not storing them, that is why addnewrow is usually followed by a data update. HTH.

[AlexW]

My guess is that your dataview is losing the subsequent rows by not storing them, that is why addnewrow is usually followed by a data update. HTH.

Sorry I don't follow your reasoning, why would I need to update the database I got the original data from with each row I add? Why can't I make my changes to a local dataset and then update all the changes at once?

what is happening here is that the rowfilter is getting is not filtering out newly added rows. ie the test below should be returning 0 when I give it a partnumber which is unique, but instead returns the number of the rows that I have already added (note that they all have completely different part numbers too)

The excerpt of code I'm referring to is shown below

'set row filter to partNumber
addPartsDataView.RowFilter = "partNum = '" + partNumber + "'"
'test if part is new/unique
If addPartsDataView.Count = 0 Then
'part must be unique (filter returns no rows)

[AlexW]

I just noticed that the .haschanges property of the dataset that my dataview refers to remains false after adding a row. Could this have something with it?

The code for adding a new datarow is shown below... am I missing a step?

        
        Dim addedRow As System.Data.DataRowView
        'creating new row in dataview, making addedRow point to it
        addedRow = addPartsDataView.AddNew()

[AlexW]

Problem solved

I had to either create a binding source and call the .endedit() method on it or change the selected row.

I chose the former. My revised working code to add a new datarow to the dataset is shown below:

Private Function fillDataRowfromForm() As System.Data.DataRowView

        Dim myBindingSource As BindingSource = New BindingSource
        myBindingSource.DataSource = addPartsDataView
        'myDataGridView.DataSource = myBindingSource;

        Dim addedrow As DataRowView = DirectCast(myBindingSource.AddNew(), DataRowView)
      
        addedRow.Item("Major") = ....
        addedRow.Item("Minor") = ....
        
        addedrow.EndEdit()

    End Function

Hope this helps anyone else with the same problem.

[baysoftdan]

Hi there.

Can I suggest that you abandon this method of checking for duplicates.
I would personally create a function called

Protected Sub ExecuteScalar(byVal SQL as String) as Object

Basically, you need to create a new system.data.sql client.sqlconne ction object,
then a new system.data.sql client.sqlcomma nd object and then fire the command using ExecuteScalar as the method, returning the result of the query as the result of the function.

Basically, you can then run some code similar to this:

If ExecuteScalar("select count(*) from [cust] where [email] = 'email@address.com' ") > 0 THEN
  ' Give some bad feedback
else
  ' Proceed with the insertion of the new record
End If

Right tool for the right job.

I hope this helps.