Datagridview Error After Rowfilter -> Data Update/Add and Fill Process

Question

I have a datagridview which filled by SQL table. When I search a value with a textbox in the data and update/insert the some values and use fill function again I get this error "System.NullReferenceException HResult=0x80004003 Message=Object reference not set to an instance of an object."

Note: No problem with update, insert and fill function without using search textbox

This is my fill function;

DataTable dt1 = new DataTable();
void fill()
    {
        try
        {
            //txtSearch.Clear();
            dt1.Rows.Clear();
            dt1.Columns.Clear();
            SqlDataAdapter da = new SqlDataAdapter("Select * From Bilgisayar_Zimmet", bgl.baglanti());
            da.Fill(dt1);
            dataGridView1.DataSource = dt1;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

    }

Here is my search code;

private void txtSearch_TextChanged(object sender, EventArgs e)
    {
        (dataGridView1.DataSource as DataTable).DefaultView.RowFilter = String.Format("Name LIKE '%{0}%' OR Surname LIKE '%{1}%'", txtSearch.Text, txtSearch.Text);
    }

I think my problem is txtSearch_TextChanged methods. It blocks fill function because in the dgv there is filtered row.

Answer 1

So, if I was hanging my hat on the DataTable peg I would do the following in a net framework project - large parts of this process are switched off in netcore+ projects because of bugs/incomplete support in VS. It does work in core as a runtime thing, just not design (so you could do this design in framework, and import the code to core, or even have a core and netfw project that edit the same files..):

I would...

• Have my table in my DB:

• Make a new DataSet type of file:

• Right click the surface and add a tableadapter

• Add a connection string, and choose SELECT

• Write a query - personally I recommend putting like select * table where primarykeycolumn = @id here rather than selecting all, but you apparently want to have all 2000 rows in the client. Have a look in advanced and check that all 3 boxes are ticked

• Give the query a decent name - if youre selecting by@id then call it FillById etc

• You're done

---

• Flip to the forms designer for your form, and open the data sources panel (View menu, Other windows). Drag the node representing the table onto the form:

• A load of stuff appears at the bottom, a grid appears, the toolbar appears. Add a textbox too (the asterisk) - we'll use it for the filter

• Double click the textbox; you'll see the minimal code VS has written to load the data. The rest of the code is in the Form.Designer.cs file and the DataSet.Designer.cs file if you want to have a look. In our form we just need this code:

• I'd paste it as text, but genuinely, the only line of it I actually wrote was the one line in the TextChanged:

bilgisayarZimmetBindingSource.Filter = String.Format("FirstName LIKE '%{0}%' OR LastName LIKE '%{0}%'", txtSearch.Text);

• you can reuse placeholders in a string format by the way. Note that the filtering is done on the bindingsource - a device that VS has put between the table and the grid. BindingSources are very useful; they maintain position/current-row knowledge and can transmit updates up and down between grid and datatable. When related data is shown they automatically filter child datatables to show only children of a current parent

---

• We can now run the app and load data, save data and filter data, all off that one line of code. I add a bunch of people:

• See how their IDs are all negative? The datatable has an autoincrement that steps -1 then the db will calc the values when we hit save and they will be retrieved automatically, patched into the datatable and the grid will update, all automatically. If these rows were the parent of other rows, the child rows ParentId would be updated too via a DataRelation in the dataset:

• We haven't had to refill anything; VS just got the single IDs out the DB

• I can type a filter:

• No errors; these people are editable, saveable, I can clear the textbox and see everyone etc.

---

So what is this voodoo? How does it work? Well.. It's not much different to yours. If we go rummaging in DataSet.Designer.cs we can find the queries that pull and push data:

read and write queries right there; the tableadapter wraps a dataadapter and sets up the relevant queries, the datarow state drives whether an insert or update etc is fired.. All the code in your question and more is written by VS and tucked away.. The dataset has strongly typed tables in that inherit from DataTable, and rows that have proper properties rather than just an array you access via string column names

We would just load data:

var dt = tableAdapter.GetData();
var dt2 = new BlahBlahDataTable();
tableAdapter.Fill(dt2);

//if we put parameters in the dataset query like SELECT * FROM person WHERE name = @n
var dt3 = new PersonDataTable();
ta.FillByName(dt3, "John");

The strongly typed datatables are nicer to LINQ with

//weakly typed datatable
dt.Rows.Cast<DataRow>()
  .Where(r => r.Field<string>("FirstName").Contains("John") || r["LastName"].ToString().Contains("John"))

//strongly typed datatable
dt.Where(r => r.FirstName.Contains("John") || r.LastName.Contains("John"))

etc..

---

Final point: I personally wouldn't download 2000 rows into the client and hold them there. I'd download what I needed as and when. The longer you have something the more out of date it's likely to be, the more meory it consumes and the more your server disk/network gets battered serving up volumes of data noone uses. I understand the tradeoff between loading all the rows and then burning the client's CPU to search them rather than the server's, and that "string contains" are hard to optimally search/index, but still - I'd look at full text indexing and leaving them on the server

When you're making your query above, don't put SELECT * FROM BilgisayarZimmet - put SELECT * FROM BilgisayarZimmet WHERE ID = @id and call it FillById. Then when you're done with that one, add another query, SELECT * FROM BilgisayarZimmet WHERE FirstName LIKE @n OR LastName LIKE @n and call it FillbyNameLike

In code, call it like this:

tableAdapter.FillByNameLike(theDatasetX.BilgisayarZimmet, "%" + txtSearch.Text + "%")

Or even better let the user supply the wildcard, tohugh people are perhaps used to writing * so:

tableAdapter.FillByNameLike(theDatasetX.BilgisayarZimmet, txtSearch.Text.Replace("*", "%"))

When the TableAdapter fills it will clear the datatable first, so no need to clear it yourself. To turn this behaviour off, set tableAdapter.ClearBeforeFill = false. This way your grid shows only the data downlaoded from the DB, and that's a small, more recent dataset, than querying it all at app start

---

All in, VS write a lot of tedious code,and makes life a lot nicer. I use these nearly exclusively if operating in DataTable mode