CODEWITHSUNDEEP
c#asp.netsqlitegridviewdatagridview
ielaajez
ielaajez

Reputation: 81

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

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.
my table
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

Upvotes: 2

Views: 1517

Answers (2)

Arun kumar
Arun kumar

Reputation: 1

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; };           
    }

Upvotes: 0

Alexander Petrov
Alexander Petrov

Reputation: 14231

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)
        });
}

Upvotes: 1

Related Questions