Getting SQLite data in gridview (C#) is not working

Question

I have a SQLite database with data in it. I want to show this data in a GridView list on my asp.NET webpage. I found a way here but that did not work for me. What I want is to display the first three columns of my table in the gridview list.

So I want to show the data in the columns "woord", "vertaling" and "gebruiker".

This is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Scripts_Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnTest_Click(object sender, EventArgs e)
    {

        string connectionString =
         @"Data Source=C:/Users/elias/Documents/Visual Studio 2017/WebSites/WebSite7/App_Data/overhoren.db";

        using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString))
        {
            conn.Open();

            using (var command = new System.Data.SQLite.SQLiteCommand(conn))
            {
                command.Connection = conn;

                command.CommandText =
                   @"SELECT[vertaling], [woord] FROM[tbWoorden] WHERE[woord] = 'ans'";



                using (var reader = command.ExecuteReader())
                {
                    string test = "";

                    if (reader.Read())
                        dataGridView1.Rows.Add(new object[] {
                            read.GetValue(read.GetOrdinal("woord")),  // Or column name like this
                            read.GetValue(read.GetOrdinal("vertaling")),
                            read.GetValue(read.GetOrdinal("gebruiker"))
                        });

                }
            }

        }
    }



    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }

    protected void grdMijnLijsten_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
}

This is not working for me. I think I am doing something completely wrong, but I searched on the web a lot and could not find a fix to solve the problem.

What should I do?

Thanks,

Elias

and if you are looking for examples make sure they are asp.net. The link you posted goes to a winforms example. — Crowcoder
– Crowcoder, Commented May 14, 2017 at 11:29
@Crowcoder everything else I found does not work with SQLite — ielaajez
– ielaajez, Commented May 14, 2017 at 11:37
The examples may not be SQLite, but the database you use doesn't matter. All the .net providers implement the same interfaces. Just replace their SqlCommand (for instance) with SQLiteCommand , etc. — Crowcoder
– Crowcoder, Commented May 14, 2017 at 11:41

Nkosi · Accepted Answer · 2017-05-14 12:16:05Z

1

You forgot to specify the column gebruiker in the SELECT command.

To obtain the many data you need to use a while loop.

Ordinal values should be obtained once, before the loop.

command.CommandText =
    @"SELECT [vertaling], [woord], [gebruiker]
      FROM [tbWoorden]
      WHERE [woord] = 'ans'";


using (var reader = command.ExecuteReader())
{
    int woordIndex = reader.GetOrdinal("woord");
    int vertalingIndex = reader.GetOrdinal("vertaling");
    int gebruikerIndex = reader.GetOrdinal("gebruiker");

    while (reader.Read())
        dataGridView1.Rows.Add(new object[] {
            reader.GetValue(woordIndex),
            reader.GetValue(vertalingIndex),
            reader.GetValue(gebruikerIndex)
        });
}

edited May 14, 2017 at 12:16

Nkosi

249k38 gold badges472 silver badges506 bronze badges

answered May 14, 2017 at 11:47

Alexander Petrov

14.4k2 gold badges23 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ielaajez Over a year ago

Thanks for the quick reply. It almost worked but it gave this error: GridViewRowCollection' does not contain a definition for 'Add' and no extension method 'Add'

Crowcoder Over a year ago

@ielaajez, that is again because this is an asp.net grid, not a winforms grid. If you are going to do anything interesting with the grid you should use databinding anyway. Check this out

ielaajez Over a year ago

@Crowcoder thank you, that article was really helpfull!

Arun kumar · Accepted Answer · 2018-08-08 12:29:37Z

0

you can use this function.

  private static DataTable ReadSqliteTable(string DBfile, string tableName,string wherestatement = "")
    {
        try
        {
            using (var conn = new SQLiteConnection("Data Source=" + DBfile))
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                SQLiteDataAdapter sQLiteData = new SQLiteDataAdapter("SELECT * FROM " + tableName + wherestatement, conn);
                DataTable table = new DataTable(tableName);
                sQLiteData.Fill(table);
                return table;
            }
        }
        catch { return null; };           
    }

answered Aug 8, 2018 at 12:29

Arun kumar

11 bronze badge

Collectives™ on Stack Overflow

Getting SQLite data in gridview (C#) is not working

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related