Adding SDF data into a Listview - C#

Hello everyone, I finally got the SDF file on my pocket pc, and now am having trouble loading the data into the listview. I am not sure where I am going wrong, but the code is listed below. Any ideas? Thanks so much, everyone.

private void LoadList(string szSQL)

{

// Clear out anything that might already be in the list

CollectionList.Items.Clear();

// save off the SQL Statement

m_szCurrFilter = szSQL;

// Throw Data into the list

SqlCeCommand sqlLoadEvents = m_cConn.CreateCommand();

sqlLoadEvents.CommandText = szSQL;

SqlCeDataReader rReader = sqlLoadEvents.ExecuteReader();

// roll through the reader and put items into the list

while ( rReader.Read() )

{

ListViewItem lvItem = new ListViewItem(rReader.GetValue(rReader.GetOrdinal("ca_id")).ToString()); //Nmbr

// Note: Since a "tag" is not supported by the compact framework, we'll use the "ImageIndex"

// property to hold the movie ID, this way we can find it later when the user clicks

// on an item

lvItem.ImageIndex = (int)rReader.GetValue(rReader.GetOrdinal("ca_id"));

lvItem.SubItems.Add(rReader.IsDBNull(rReader.GetOrdinal("ca_titel")) ? "" : rReader.GetValue(rReader.GetOrdinal("ca_titel")).ToString()); // Title

lvItem.SubItems.Add(rReader.IsDBNull(rReader.GetOrdinal("ca_alter")) ? "" : rReader.GetValue(rReader.GetOrdinal("ca_alter")).ToString()); // Rating

lvItem.SubItems.Add(rReader.IsDBNull(rReader.GetOrdinal("ca_sex")) ? "" : rReader.GetValue(rReader.GetOrdinal("ca_sex")).ToString());// Gender

CollectionList.Items.Add( lvItem );

}

}

I think I have narrowed it (somewhat) down:

The following item is giving me problems, I think.

LoadList("SELECT * FROM fragenkatalog");

-

fragenkatalog is the table in the database.

Thanks again.

|||

those calls to getOrdinal("columnName") combined with a SELECT * command are going to make your code as slow as possible.

the goal you have of storing the ID in the ImageIndex is not going to work, but what you are trying to do is what I call "poor-man's databinding" and I understand why you are doing it. In controls like datagrids and listviews, if you want ultimate performance (meaning, avoiding binding), you can create an extra column in your ListView, set it's width to zero, and in this column, store the ID you need on the row.

Here is a sample of filling a listview with the results of a complex database query. Note that we explicitly list the columns needed in the SQL, we refer to the ordinals by offset, close/disposee the data reader, etc. In this example, there are 2 columns with a width of zero that store GUIDs that the application needs for each row selected - poor mans databinding.

private void _populateTodaysTasks()
{
string sql = "SELECT a.ScheduledStartDate, b.Name, a.Name, c.Name, a.ID, c.ID, a.ActualStartDate, a.ActualEndDate, a.Synchronized " +
"FROM Job a, Customer b, Form c, Site d " +
"WHERE a.TechnicianID = '" + Globals.GetInstance().TechnicianID + "' " +
"AND a.SiteID = d.ID AND d.CustomerID = b.ID " +
"AND a.FormID = c.ID " +
"AND a.Synchronized = 0 " +
"ORDER BY a.ScheduledStartDate";

SqlCeDataReader dtr = DatabaseManager.GetInstance().ExecuteQuery(sql);

if ( null != dtr )
{
while ( dtr.Read() )
{
ListViewItem lvi = new ListViewItem(dtr.GetString(0));
lvi.SubItems.Add(dtr.GetString(1).Trim());
lvi.SubItems.Add(dtr.GetString(2).Trim());
lvi.SubItems.Add(dtr.GetString(3).Trim());
lvi.SubItems.Add(dtr.GetGuid(4).ToString()); // jobID
lvi.SubItems.Add(dtr.GetGuid(5).ToString()); // formID
string start = dtr.GetString(6).Trim();
string end = dtr.GetString(7).Trim();
bool sent = dtr.GetBoolean(8);

// removed some code that uses start and end to determine if a task is past due

lvwTasks.Items.Add(lvi);

}
dtr.Close();

dtr.Dispose();

}

}

Darren

|||

Thank you Darren, I appreciate your help.

I will try this out later on and let you know how I did. :)

I really appreciate it!

Martina

|||

Well, I'm confused.

I have re-thought everything again, gone through the code, and have simplified it somewhat (I think). But I am still not getting the list of items in the listview box. In my sdf file, I have 1 Table (fragenkatalog) and within it, I have 5 columns (id (int), title (nvarchar (150)), picture (nvarchar (100)), age (int), and gender (nvarchar (1)) ) -- but I am only listing the first 3 in the listbox.

The way the code is now, I am not getting any error messages when I debug.

I am including the code for the application.... I'm at such a loss, and I've been using the ".NET Compact Framework KickStart" book (Rubin, Yates) and have not been having much luck with finding my answer there.

If someone could give me a helping hand, I would be very appreciative.

Thanks again,

Martina

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.IO;

using System.Text;

using System.Data;

using System.Data.SqlServerCe;

using System.Data.Common;

namespace PedBoneView

{

/// <summary>

/// This is the default form.

/// <summary>

public class Form1 : System.Windows.Forms.Form

{

private System.Windows.Forms.ColumnHeader columnHeader1;

private System.Windows.Forms.ColumnHeader columnHeader2;

private System.Windows.Forms.ColumnHeader columnHeader3;

private System.Windows.Forms.MenuItem New;

private System.Windows.Forms.ListView lVListe;

private System.Windows.Forms.ColumnHeader columnHeader4;

private System.Windows.Forms.ColumnHeader columnHeader5;

private System.Windows.Forms.ColumnHeader columnHeader6;

private System.Windows.Forms.MainMenu mainMenu1;

SqlCeConnection conn = null;

private System.Windows.Forms.ContextMenu contextMenu1;

private System.Windows.Forms.MenuItem menuItem2;

private System.Windows.Forms.MenuItem menuItem3;

SqlCeCommand cmd = null;

public Form1()

{

//

// Required for Windows Form Designer support

//

InitializeComponent();

//

// TODO: Add any constructor code after InitializeComponent call

//

}

/// <summary>

/// Clean up any resources being used.

/// </summary>

protected override void Dispose( bool disposing )

{

base.Dispose( disposing );

}

static void Main()

{

Application.Run(new Form1());

}

private void Form1_Load(object sender, System.EventArgs e)

{

try

{

if (!File.Exists ("//My Documents//pedbone.sdf")) // If the database doesn't exists, create it

{

SqlCeEngine engine = new SqlCeEngine ("Data Source = //My Documents//pedbone.sdf");

engine.CreateDatabase ();

conn = new SqlCeConnection ("Data Source = //My Documents//pedbone.sdf");

conn.Open();

cmd = conn.CreateCommand();

cmd.CommandText = "CREATE TABLE fragenkatalog(ca_id int PRIMARY KEY IDENTITY(1,1), ca_titel nvarchar(150), ca_bildurl1 nvarchar(100), ca_alter int, ca_sex nvarchar(1))";

cmd.ExecuteNonQuery();

conn.Close();

}

else // else initialize the connection

{

conn = new SqlCeConnection ("Data Source = //My Documents//pedbone.sdf");

cmd = conn.CreateCommand();

UpdateLView();

}

}

catch (SqlCeException ex)

{

}

}

//New entry

private void New_Click(object sender, System.EventArgs e)

{

New newpbdata = new New(this);

newpbdata.Show();

}

public void UpdateLView() //This function updates the main listview

{

// lVListe.Items.Clear();

conn.Open();

cmd.CommandText = "SELECT ca_id int, ca_titel nvarchar(150), ca_bildurl1 nvarchar(100) FROM fragenkatalog";

SqlCeDataReader rdr = cmd.ExecuteReader();

if ( null != rdr )

while(rdr.Read())

{

ListViewItem lvi = new ListViewItem(rdr.GetString(0));

lvi.SubItems.Add(rdr.GetString(1));

lvi.SubItems.Add(rdr.GetString(2));

lvi.SubItems.Add(rdr.GetString(3));

lVListe.Items.Add(lvi);

}

rdr.Close();

rdr.Dispose();

}

}

}

|||

While you do create a database and a table, you have not inserted any actual data into the table before you attempt to read from the table and populate the listview.

Try inserting some data into the database table.

-ds

|||

I already have data in the database table (about 750 rows).

Is that not possible to do?

Technically, I just want to show the data that's in the table, not edit, add, or remove any of the data.

Thanks again.... :)

|||

How is the ListView itself configured? Did you use the VS2005 designer to add columns and set the view to Details with/without checkboxes or are you doing this in code?

Be sure you have created ListViewColumns that match what you are trying to display in the ListView and that you have set the Default View to Details.

Post the portion of the InitializeComponent method that initializes the listview if you want me to review that.

This should not be so difficult - there is something small and easy you have wrong and we just need to find it.

Darren

|||

Darren,

Thank you so much for your help. I'm really feeling like an idiot at the moment, simply because I cannot find the problem myself. I was getting worried that it might be my sdf file....

I'm using code to do this. I am using MS Development Enviroment 2003...

I hope I've provided the code you need.

Thank you very much again...

Martina

--

private void InitializeComponent()

{

this.mainMenu1 = new System.Windows.Forms.MainMenu();

this.New = new System.Windows.Forms.MenuItem();

this.lVListe = new System.Windows.Forms.ListView();

this.columnHeader1 = new System.Windows.Forms.ColumnHeader();

this.columnHeader2 = new System.Windows.Forms.ColumnHeader();

this.columnHeader3 = new System.Windows.Forms.ColumnHeader();

this.columnHeader4 = new System.Windows.Forms.ColumnHeader();

this.columnHeader5 = new System.Windows.Forms.ColumnHeader();

//

//

// lVListe

//

this.lVListe.Columns.Add(this.columnHeader1);

this.lVListe.Columns.Add(this.columnHeader2);

this.lVListe.Columns.Add(this.columnHeader3);

this.lVListe.Columns.Add(this.columnHeader4);

this.lVListe.Columns.Add(this.columnHeader5);

this.lVListe.ContextMenu = this.contextMenu1;

this.lVListe.FullRowSelect = true;

this.lVListe.Size = new System.Drawing.Size(240, 216);

this.lVListe.View = System.Windows.Forms.View.Details;

// columnHeader1

//

this.columnHeader1.Text = "ID";

this.columnHeader1.Width = 30;

//

// columnHeader2

//

this.columnHeader2.Text = "Title";

this.columnHeader2.Width = 80;

//

// columnHeader3

//

this.columnHeader3.Text = "Image";

this.columnHeader3.Width = 60;

//

// columnHeader4

//

this.columnHeader4.Text = "Age";

this.columnHeader4.Width = 35;

//

// columnHeader5

//

this.columnHeader5.Text = "Sex";

this.columnHeader5.Width = 35;

//

// Form1

//

this.Controls.Add(this.lVListe);

this.Menu = this.mainMenu1;

this.Text = "PedBone Pocket";

this.Load += new System.EventHandler(this.Form1_Load);

|||

I think I see the problem.

The ListView object you define has 5 columns:

ID Title Image Age Sex

and the ListView items you are trying to add to the ListView only have 3 columns resulting from this select statement

SELECT ca_id int, ca_titel nvarchar(150), ca_bildurl1 nvarchar(100) FROM fragenkatalog

the number of SubItems in a ListViewItem must be one less than the number of columns defined in the ListView itself. So, in other words, you create a new ListViewItem and give it the name 'ID'. If there are 5 total columns defined in the ListView, you need to add 4 more subitems to this ListViewItem so that 'ID' plus the other 4 subitems constitute a total of 5 items which match to 5 columns.

Darren

|||

Hmmmmmmm.....

That didn't seem to work... Or have I missed something again? I have triple-checked upper- and lower case, so that's not it. I checked the nvarchar values, and those seem to be okay too. I did upper case ID and lower case id, ca_id too.

I'm still not getting any errors, but also nothing in the ListView.

Here's the updated code....

Thank you again, Darren.

public void UpdateLView() //This function updates the main listview

{

lVListe.Items.Clear();

conn.Open();

cmd.CommandText = "SELECT ca_id int, ca_titel nvarchar(150), ca_bildurl1 nvarchar(100), ca_alter int, ca_sex nvarchar(1) FROM fragenkatalog";

SqlCeDataReader rdr = cmd.ExecuteReader();

if ( null != rdr )

while(rdr.Read())

{

ListViewItem id = new ListViewItem(rdr.GetString(0)); //this should be the id #

id.SubItems.Add(rdr.GetString(1)); //title

id.SubItems.Add(rdr.GetString(2));//picture

id.SubItems.Add(rdr.GetString(3));//age

id.SubItems.Add(rdr.GetString(4));//gender

lVListe.Items.Add(id);

}

rdr.Close();

rdr.Dispose();

}

|||

Just email me your SDF file and I will send you the working code - newsgroups are awful for this sort of thing and it would just be faster for me to fix it and send it to you.

Do you want all 5 columns in the listview (or did you only want the 3 you originally had in the sql select statement?)

Google me and contact me through my BLOG.

Darren Shaffer

|||

I did receive your SDF file and I have sent you back a working solution with a note explaining the necessary changes. Check your email.

Darren