Filterexpression and NULL

David_Graves
David_Graves used Ask the Experts™
on
Dim drPromptOverride() As DataRow = Me.PDICompany_780_02DataSet.SAPPBROS_780_Card_Prompt_Overrides.Select(String.Concat("Cust_Key = ", Convert.ToInt32(dgvCustSelect.CurrentRow.Cells("Cust_Key").Value), " AND Card_Key IS NULL "))

Open in new window


The SQL database this is built for does indeed have a row that matches the criteria (where Card_Key is a null value), but the .select is returning 0 rows.

I am guessing I have the NULL portion of the expression string coded incorrectly. Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
Add a messagebox to see what the filter expression is evaluating to.

Author

Commented:
The filter expression is not stored with drPromptOverride, so I am unsure how to see what it is.
Most Valuable Expert 2012
Top Expert 2014

Commented:
You can add the following line just above the line you have

Msgbox String.Concat("Cust_Key = ", Convert.ToInt32(dgvCustSelect.CurrentRow.Cells("Cust_Key").Value), " AND Card_Key IS NULL ")
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Author

Commented:
Hmmm... it seems to be working now.

I think what happened is I added the row I was looking for, but didn't update the dataset adapter so it wasn't finding it.

Author

Commented:
Although I had used the TableAdapter.Insert method to create the row in the first place, I thought that the data adapter would have been automatically updated.
Most Valuable Expert 2012
Top Expert 2014

Commented:
No it requires that you call the Update method to propagate changes back to DB.

Author

Commented:
The TableAdapter.Insert method is inserting the row into the database. Why would I need to use the Update method?

It seems to me that after I to the Insert method, I need to re-select the data, but that seems counter-intuitive to using a DataAdapter in the first place.

Now I am confused.

Author

Commented:
In other words:

        Dim fEx As String = String.Concat("Cust_Key = ", Convert.ToInt32(dgvCustSelect.CurrentRow.Cells("Cust_Key").Value), " AND Card_Key IS NULL")
        Dim drPromptOverride() As DataRow = Me.PDICompany_780_02DataSet.SAPPBROS_780_Card_Prompt_Overrides.Select(fEx)

        Try
            If drPromptOverride.Count = 0 Then
                Me.SappbroS_780_Card_Prompt_OverridesTableAdapter1.Insert(Convert.ToInt32(dgvCustSelect.CurrentRow.Cells("Cust_Key").Value), _
                                                                          nullDecimal, _
                                                                          Convert.ToInt16(Me.CP_Plate.CheckState), _
                                                                          Convert.ToInt16(Me.CP_Truck.CheckState), _
                                                                          Convert.ToInt16(Me.CP_Mileage.CheckState), _
                                                                          Convert.ToInt16(Me.CP_DriverID.CheckState), _
                                                                          Convert.ToInt16(Me.CP_Trip.CheckState), _
                                                                          Convert.ToInt16(Me.CP_PO.CheckState), _
                                                                          Convert.ToInt16(Me.CP_Trailer.CheckState))
            Else

Open in new window


works and adds the row to the table when it did not find it the first time. When I attempt again, it does not find the row in drPromptOverride and attempts the insert a second time (and of course, if I restart the project, it works as intended)
Ended up just doing a dataadapter.fill each time I did an insert or an update.

It's working, just not how I thought it should.

Author

Commented:
.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial