[RESOLVED] Very weird "data gone missing and then it's back" problem.

[lkallas]

Hi!

I have a code that uses SQL query to filter data in gridview. Using MS Access 2003 database. The query is running fine and filters data well. But sometimes when I execute the code one row goes missing. And after a while when I execute the same code it is back there! It is always the same row. Can't figure out why.

Any idea what might cause this?

[jmcilhinney]

Firstly, is it really a GridView? That's a WebForms control. Is this a WebForms app or is it actually a DataGridView? Secondly, your description is far too vague. There's nothing inherent with querying a database and displaying the data that will make records disappear so it's something specific to what you're doing, so we need to know specifically what you're doing. The first order of business would be to determine whether the record "goes missing" between the database and the app or between the app data source and the UI.

[lkallas]

Yes, I know it isn't very good explanation. Been coding for....20 hours in a row - can't understand even my own thoughts.

I am starting to think that there is something wrong with my sql statement.

I Have a table where there is checkboxes (True/false) in columns OEM and Audi. Then the part of the code:

Dim Audi As Boolean
Dim OEM As Boolean

OEM = True
Audi = True


SQLString = "SELECT * FROM SpareParts WHERE OEM = " & OEM & " and Audi = " & Audi & ""

Is this the right way to say I want to see only the data where OEM is true and Audi is true? In my program this code brings back also data where audi= false but oem=true and vice versa. But I want ONLY data where Audi=TRUE and OEM=TRUE to be displayed in my gridview. Somethings wrong here.....

[Shaggy Hiker]

That isn't quite the right way. After all, there is no need for the variables. When using the variables, what will be returned is whatever the variables are set to, so the way you have it would make more sense if you were going to return is sometimes when both were true, sometimes when both were false, and sometimes when they were in other combinations. If you want to return just those cases where they are both true, then scrap the variables and get rid of the string concatenation (which you wouldn't need), and just write it out. I think that for Access it would be something like this:

WHERE OEM = -1 AND AUDI = -1

but I may have the wrong representation for True.

[lkallas]

I think that for Access it would be something like this:

WHERE OEM = -1 AND AUDI = -1

but I may have the wrong representation for True.

Thank you, it did it. Something new to learn.

Another quick question. How can I modify this code to close database connection and does it refresh the data(in the gridview) when the code has launched again?

Private Sub lstBedrooms_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstBedrooms.SelectedIndexChanged
Dim Bedrooms, SQLString As String
Dim dtProperties As New DataTable() 
Dim dbDataAdapter As OleDbDataAdapter
Dim ConnectString As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source = Holidays.mdb"
Bedrooms = lstBedrooms.Text
SQLString = "SELECT * FROM Properties WHERE Bedrooms = " & Bedrooms & ""
dbDataAdapter = New OleDbDataAdapter(SQLString, ConnectString)
dbDataAdapter.Fill(dtProperties)
grdProperties.DataSource = dtProperties

End Sub

[Shaggy Hiker]

I think that if you let the dataadapter manage the connection, it will do just that. In other words, it will open it as needed, and close it when done. On the other hand, I haven't really tried it that way, so I don't know for sure.

The code, as you have it, should completely create a new datatable and set that as the source for the grid, so it should totally refresh it each time that code runs. If the performance is acceptable, then that is fine. However, the way you have it is rather inefficient, so if the performance is not acceptable, you would need a more complex solution. If that is the case, I would suggest that you fill the datatable with all the records (unless there are too many to make that an option), and filter the datatable using a Dataview rather than creating a whole new datatable each time.

[lkallas]

I found a solution to my first question. Missing data was caused by the order of my IF statements inside the code. I changed the order as I was coding and that messed around with the data that was displayed in the grid. Put the statements to a logic order and everything works just the way I wanted. Hours of trial and error and recoding....and all because of this little mistake.

Thank you all for your help. Thread solved!