DaniWeb Forum Index > Software Development > C#

Get the column values from the SQLite database

Please support our C# advertiser: Programming Forums - DaniWeb Sister Site

Thread Solved

•

Join Date: May 2009

Posts: 30

Reputation: Krstevski is an unknown quantity at this point

Solved Threads: 0

Krstevski

Offline

Light Poster

Get the column values from the SQLite database

Oct 31st, 2009

Hello guys, can someone tell me how can I get the values from the columns ?

My sql table is:

Table name: settbl
Table columns: id, first (int), second (int)

...and now, I want to get the values from "first" and "second", but I got the error message "Object reference no set to an instance of an object".

This is my code:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
sqlIteCon = new SQLiteConnection("Data Source=set.db;Version=3;New=False;Compress=True;");
                sqlIteCon.Open();
string SelectQuery = "SELECT * FROM settbl";
SQLiteCommand sqlCommand = new SQLiteCommand(SelectQuery, sqlIteCon);
 
                SQLiteDataReader sqlRead = sqlCommand.ExecuteReader();
 
                int a = sqlRead.GetInt32(sqlRead.GetOrdinal("first"));
 
                int b = sqlRead.GetInt32(sqlRead.GetOrdinal("second"));
sqlIteCon = new SQLiteConnection("Data Source=set.db;Version=3;New=False;Compress=True;");
                sqlIteCon.Open();
string SelectQuery = "SELECT * FROM settbl";
SQLiteCommand sqlCommand = new SQLiteCommand(SelectQuery, sqlIteCon);

                SQLiteDataReader sqlRead = sqlCommand.ExecuteReader();

                int a = sqlRead.GetInt32(sqlRead.GetOrdinal("first"));

                int b = sqlRead.GetInt32(sqlRead.GetOrdinal("second"));

Thanks...

•

Join Date: Feb 2009

Posts: 3,464

Reputation: sknake has much to be proud of

Solved Threads: 629

sknake

Offline

.NET Enthusiast

Oct 31st, 2009

Why not load it in to a DataTable ?

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
    private void button9999_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      const string query = "Select * From Invoice Where InvNumber = @InvNumber";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(new SqlParameter("@InvNumber", 1100));
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            using (DataTable dt = new DataTable())
            {
              dt.Load(dr);
              for (int i1 = 0; i1 < dt.Rows.Count; i1++)
              {
                DataRow row = dt.Rows[i1];
                string custName = Convert.ToString(row["CustName"]);
                string email = Convert.ToString(row["Email"]);
              }
            }
          }
        }
        conn.Close();
      }
    }
    private void button9999_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      const string query = "Select * From Invoice Where InvNumber = @InvNumber";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(new SqlParameter("@InvNumber", 1100));
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            using (DataTable dt = new DataTable())
            {
              dt.Load(dr);
              for (int i1 = 0; i1 < dt.Rows.Count; i1++)
              {
                DataRow row = dt.Rows[i1];
                string custName = Convert.ToString(row["CustName"]);
                string email = Convert.ToString(row["Email"]);
              }
            }
          }
        }
        conn.Close();
      }
    }

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: May 2009

Posts: 30

Reputation: Krstevski is an unknown quantity at this point

Solved Threads: 0

Krstevski

Offline

Light Poster

Nov 1st, 2009

Sknake, thanks for your help/example, but I solved mu problem with the SQLiteDataReader Class

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
public void ReadMyData(string myConnString)
{
 string mySelectQuery = "SELECT DeptNo, DName FROM Dept";
 SQLiteConnection sqConnection = new SQLiteConnection(myConnString);
 SQLiteCommand sqCommand = new SQLiteCommand(mySelectQuery,sqConnection);
 sqConnection.Open();
 try
 {
   SQLiteDataReader sqReader = sqCommand.ExecuteReader();
   while (sqReader.Read())
   {
     Console.WriteLine(sqReader.GetInt32(0) + ", " + sqReader.GetString(sqReader.GetOrdinal("DName")));
   }
   sqReader.Close();
 }
 finally
 {
   sqConnection.Close();
 }
}
public void ReadMyData(string myConnString)
{
 string mySelectQuery = "SELECT DeptNo, DName FROM Dept";
 SQLiteConnection sqConnection = new SQLiteConnection(myConnString);
 SQLiteCommand sqCommand = new SQLiteCommand(mySelectQuery,sqConnection);
 sqConnection.Open();
 try
 {
   SQLiteDataReader sqReader = sqCommand.ExecuteReader();
   while (sqReader.Read())
   {
     Console.WriteLine(sqReader.GetInt32(0) + ", " + sqReader.GetString(sqReader.GetOrdinal("DName")));
   }
   sqReader.Close();
 }
 finally
 {
   sqConnection.Close();
 }
}