How do I filter a DataGridView?

[agroom]

I'm developing my first app in VS 2005 (previous experience is in VB 6). I'm making a simple form that has a DataGridView with a BindingNavigator. All the bindings are setup in design. My question is, am I able to filter the DataGridView using a SQL statement?

I only have 1 table I'm reading from. In the BindingNavigator I made a quick search by putting a dropdown box with all the fields, then a textbox and search button. The user would chooses the field and enters what to search in the textbox. So basically I'm creating a SQL string from those two criteria, but need to know where to add that into my code.

I'm still pretty new to the DB Access controls for vb.net, so my knoledge with the DataSet, BidingSource, and TableAdaptors is very basic. Does anyone have a link to a tutorial that explains these well?
​

 

[agroom]

Okay, I figured out my problem. I had the correct code, I was just not referencing the correct field names in the table.

Me.TblLogFileBindingSource.Filter = SearchString

However, now my issue is refreshing the datagridview to view the full table again. This is what I have but is not working:

Me.TblLogFileTableAdapter.Fill(Me.LogBookDataSet.tblLogFile)
Me.TblLogFileDataGridView.Refresh()

Any help is appreciated!

 

[cjard]

Youre refilling the datatable needlessly - it still contains all the data it did before.. And the datagridview still contains the filter you set before.. So to remove the filter..


MyDataGridView.Filter = Nothing


It's a kickself one

 

[agroom]

Thanks, that worked perfect!
I found this works also:
Me.TblLogFileBindingSource.RemoveFilter()

I do have one last question (I hope!). I'm having issues saving to my database. I have the following code which I took from an online tutorial:

Me.TblLogFileBindingSource.EndEdit()
Me.TblLogFileTableAdapter.Update(Me.LogBookDataSet.tblLogFile)

It seemed like this worked when I first wrote it, but some how it does not anymore. From everything I've read, this should work. The only thing I can think of is that an Import was removed. I have the following imports, is there something I'm missing? I'm using MSSQL .

Imports System
Imports System.Data
Imports System.Windows.Forms

 

[jmcilhinney]

If you were missing an import then you'd get a compilation error indicating that a type wasn't defined. Do you? Please ever just say that something doesn't work because that could mean anything. DESCRIBE what you want to happen and what actually does happen. Also, Update is a function that returns the number rows affected by the operation. Test its return value and if it's greater than zero it IS working.

 

[agroom]

a description, duh *shakes head* I should have known better

Okay, my app is a single form with a datagridview and a bindingnavigator. I use the "+" on the binding navigator to add a record(s), then click the save icon. The pencil icon on the l left side of the DGV turns into an arrow and everything looks to have saved. I shut the program down and start it up again and none of the records I'd added were saved.

I tested the return value and I'm getting 0

 

[jmcilhinney]

If Update is returning zero then there are no rows to save that meet the criteria defined by your SQL statements. You need to put a breakpoint on the line that calls Update and then test the rows of your DataTable to see whether any have a RowState other than Unchanged. If they don't then that's your issue and you need to determine why there are no Deleted, Added or Modifed rows. If there are rows with those RowStates then it must be your SQL code that is at fault.

 

[cjard]

put a breakpoint on this line:
Me.TblLogFileTableAdapter.Update(Me.LogBookDataSet .tblLogFile)

Run the code.
When it breaks, type this into the immediate window:
?Me.LogBookDataSet.tblLogFile.HasChanges()

If it is false, there are no changes to save. If the result is true, we need to look at what kind of changes they are:
?Me.LogBookDataSet.tblLogFile.GetChanges(DataRowState.Added).Rows.Count
?Me.LogBookDataSet.tblLogFile.GetChanges(DataRowState.Modified).Rows.Count
?Me.LogBookDataSet.tblLogFile.GetChanges(DataRowState.Deleted).Rows.Count

Each of these will return a count of the number of rows added, modified or deleted respectively. If GetChanges() returns nothing (which i think it does in the case of no rows) then youyll get a null reference exception in your immediate window - ignore it and carry on with the testing

Remember which kinds of rows there are that have changed.. If there are Added ones, type this:
?Me.LogBookDataSet.tblLogFile.Adapter.InsertCommand

Modified:
?Me.LogBookDataSet.tblLogFile.Adapter.UpdateCommand

Deleted:
?Me.LogBookDataSet.tblLogFile.Adapter.DeleteCommand

Report your findings...

 

[agroom]

Okay, here we go. The only modification I made was to add a row.

When it breaks, type this into the immediate window:
?Me.LogBookDataSet.tblLogFile.HasChanges()

'HasChanges' is not a member of 'tblLogFileDataTable'.

Well, I kept going anyway.

If it is false, there are no changes to save. If the result is true, we need to look at what kind of changes they are:
?Me.LogBookDataSet.tblLogFile.GetChanges(DataRowState.Added).Rows.Count
?Me.LogBookDataSet.tblLogFile.GetChanges(DataRowState.Modified).Rows.Count
?Me.LogBookDataSet.tblLogFile.GetChanges(DataRowState.Deleted).Rows.Count

Added = 1
Modified = Nothing
Deleted = Nothing

As a test I continued through this break, then individually modified 1 record, saved, and then deleted one record and saved. Each time brought back a 1 count from the appropriate tests.

Remember which kinds of rows there are that have changed.. If there are Added ones, type this:
?Me.LogBookDataSet.tblLogFile.Adapter.InsertCommand

Modified:
?Me.LogBookDataSet.tblLogFile.Adapter.UpdateCommand

Deleted:
?Me.LogBookDataSet.tblLogFile.Adapter.DeleteCommand

'Adapter' is not a member of 'tblLogFileDataTable'. [obviously for all 3]

 

[cjard]

ah.. sorry.. for the last one i gave you the wrong code... I should have put the tableadapter name in, not the datatable name..and i should have used a publicly accessible variable. doh correcting this, can you run these statements in your immediate window instead:


?Me.TblLogFileTableAdapter.CommandCollection(0)
?Me.TblLogFileTableAdapter.CommandCollection(1)
?Me.TblLogFileTableAdapter.CommandCollection(2)
?Me.TblLogFileTableAdapter.CommandCollection(3)



and it seems i was mistaken about HasChanges being present on a datatable.. it can be called on the dataset to determine if the dataset has any tables that have changes.. oops, sorry about that

 

[agroom]

Thanks again for your help with all this, here's what I got:
Again, I just added 1 record and broke at the same point.

?Me.TblLogFileTableAdapter.CommandCollection(0)
{System.Data.SqlClient.SqlCommand}
CommandText: "SELECT fldDate, fldProjectNum, fldClientName, fldProjectName, fldProjectMgr, fldClentNum, fldBudget FROM dbo.tblLogFile"
CommandTimeout: 30
CommandType: Text {1}
Connection: {System.Data.SqlClient.SqlConnection}
Container: Nothing
DesignTimeVisible: True
Notification: Nothing
NotificationAutoEnlist: True
Parameters: {System.Data.SqlClient.SqlParameterCollection}
Site: Nothing
Transaction: Nothing
UpdatedRowSource: Both {3}

?Me.TblLogFileTableAdapter.CommandCollection(1)
Index '1' for dimension '0' is out of range.

?Me.TblLogFileTableAdapter.CommandCollection(2)
Index '2' for dimension '0' is out of range.

?Me.TblLogFileTableAdapter.CommandCollection(3)
Index '3' for dimension '0' is out of range.

 

[agroom]

I created a new project and imported my database over. Then from the Data Sources browser I drug the tblLogFile table onto the new form setting up a new DataGridView and BindingSourceNavigator. This is how I did it for my current app also. Under the BidingSourceNavigator save button it has the following code (exact same as I have in my app):

Me.Validate()
Me.TblLogFileBindingSource.EndEdit()
Me.TblLogFileTableAdapter.Update(Me.LogBookDataSet.tblLogFile)

I ran the test app and this also does not save any modifications to the table. Does this mean that the default code for the save button does not actually save? It saves it while the app is still running, but once you close re-open, it doesn't show any of the previous changes.

It seems like everything is saving back to the dataset correctly, but then not updating the database.

 

[cjard]

Access database by any chance?

Did you read the message that pops up when you import your database? It says something like:

"This database isnt in your project directory, do you want to copy it there? Blah blah. The database will be copied to the output folder each time you start your project. For info on controlling this behaviour press F1"


The bit in bold means that your original database will be copied over the top of any updated database your program code has done, every time you start your project. i.e. the changes are being saved but then the IDE is overwriting your new database with the old, unchanged one.

Click on the database file itself in Solution Explorer and change the Copy property to COPY IF NEWER

 

[agroom]

I changed it to "Copy if Newer" and it works fine. I guess if I'd published a test run and tried it would have worked fine *sigh*

Sorry for all the trouble for something so simple, but I greatly apprecaite all the help!

 

[cjard]

Catches a lot of people out, including me - had me going for a good 20 minutes! And youre right - in a production copy you never would have noticed this. I think MS need to make the message more clear when you add the DB to the project