Link to home
Create AccountLog in
Avatar of David_Graves

asked on

Filterexpression and NULL

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?
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Add a messagebox to see what the filter expression is evaluating to.
Avatar of David_Graves


The filter expression is not stored with drPromptOverride, so I am unsure how to see what it is.
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 ")
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.
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.
No it requires that you call the Update method to propagate changes back to DB.
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.
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)

            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), _

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)
Avatar of David_Graves

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account