Visual Studio 2013 fill DataGrid

Question

I'm trying to read data from a SQLite database. Then I'd like to fill it into my DataGrid, but the following code doesn't work (dataUrl is the DataGrid-Object):

    string sql = "SELECT rowid, url FROM urls WHERE url LIKE '%@search%'";
    SQLiteConnection myConnection = new SQLiteConnection(@"Data Source=C:\URL Store\URL Store\bin\Release\urlStore.sqlite;Version=3;Password=*censored*;");
    SQLiteCommand command = new SQLiteCommand(sql, myConnection);
    command.Parameters.AddWithValue("@search", txtSearch.Text);
    myConnection.Open();
    command.ExecuteNonQuery();
    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
    DataSet set = new DataSet();
    try
    {
        //            
        //ESPECIALLY THIS PART IS IMPORTANT TO ME
        //
        adapter.Fill(set);
        DataTable table = set.Tables[0];
        dataUrl.DataContext = table;
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error loading data!");
    }

It does't even throw an exception.

dkozl · Accepted Answer · 2014-01-20T16:12:33.520

3

You should set ItemsSource instead of DataContext and for that you need to get DataView as it only accepts IEnumerable:

dataUrl.ItemsSource = table.DefaultView;

or

dataUrl.ItemsSource = new DataView(table);

also remove command.ExecuteNonQuery();

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

also, because you use parameters and AddWithValue(..), your query should look like this:

string sql = "SELECT rowid, url FROM urls WHERE url LIKE @search";

and you add parameter like this instead:

command.Parameters.AddWithValue("@search", "%" + txtSearch.Text + "%");

which is the whole point of using parameters

edited Jan 20 '14 at 16:12

answered Jan 20 '14 at 15:40

dkozl

32,814
8
87
89

This worked better. The DataGrid is now showing the headlines of the table, but no entries. – SoBiT Jan 20 '14 at 15:44
Have you checked your SQL in a commandline? Or at least debug the result of the query. – Pellared Jan 20 '14 at 15:46
@SoBiT, remove `command.ExecuteNonQuery();` and see if it works then – dkozl Jan 20 '14 at 15:51
@dkozl It should not have any impact on the result :) – Pellared Jan 20 '14 at 16:04
Fantastic, changing the way to set the parameters was the solution! Thanks! – SoBiT Jan 21 '14 at 07:47

Visual Studio 2013 fill DataGrid

1 Answers1