Listbox update issue

  • Thread starter Thread starter heddy
  • Start date Start date
H

heddy

I have a listbox that gets populated in code by a reader result set
from a stored proc on SQL Server 2005. In building this I created a
set of test rows in the DB and the code runs fine and populates the
box. Next I added functionality to add data. This adds data to the DB
and then forces the listbox to refresh by clearing it's contents and
then re-running the populate code.

However, when I add an item and do the refresh, the listbox does not
get the new entry. I debugged this and the DB gets updated (in fact I
stopped just after the DB update code and ran the proc that gets the
listbox data on the SQL server manager and it showed that the data was
indeed there.

But once the list box populate code runs, the new entry is missing. If
I terminate the application and run it again, the new data shows up.
Is something getting cached with the SQL reader object? It falls out
of scope in the proc that populates the list box so I assumed that it
was gone and got re-created on the next call. Both the reader object
and the connection object have their .close methods called before the
populate proc ends.

Can someone shed some light?

Thanks!
 
heddy,

Can you show how you are populating the listbox, as well as performing
the update?
 
Nicholas said:
heddy,

Can you show how you are populating the listbox, as well as performing
the update?
Click to expand...

The code that does the update of the listbox looks like this:

bool bResult = true; // assume success
SqlConnection Conn;
SqlDataReader reader = null;
this.lstProjects.Items.Clear();
Conn = new SqlConnection("user id=" + this.oUser.sUID + ";"
+
"password=" + this.oUser.sPW + ";"
+
this.oUser.sConnectString);
try
{
Conn.Open();
}

catch (Exception except)
{
bResult = false;
MessageBox.Show("Database connection Failed for user: "
+ this.oUser.sUID+ " -- Issue reported was " + except.Message);
return (bResult);
}

SqlCommand cmd = new SqlCommand("spGetProjectList",
Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UID",
this.oUser.sUID));
reader = cmd.ExecuteReader();

if (reader.HasRows)
{
// We have projects - Populate the list
while(reader.Read())
{

this.lstProjects.Items.Add(reader.GetSqlString(0));

}

this.lstProjects.SelectedIndex=0;

}

else
{
this.lstProjects.Items.Add("No Projects Defined for
this user");
}
}

reader.Close();
Conn.Close();

return (bResult);

This code actually resides within a try/catch block but that code is
irrelevant for this (I think :) ).

Anyway, that code populates the list box as you can see. I even tried
reader.Dispose() instead of close in case it was caching something.

Thanks for looking :)
 
Nicholas said:
heddy,

Can you show how you are populating the listbox, as well as performing
the update?
Click to expand...
Oh - and the DB update code here:

bool CreateNewProject()
{
SqlConnection Conn;
//IAsyncResult iaResult = null;
int iRows = 0;

SqlDataReader reader = null;

Conn = new SqlConnection("user id=" +
this.oUser.sUID.ToString() + ";" +
"password=" +
this.oUser.sPW.ToString() + ";" +
this.oUser.sConnectString);
try
{
Conn.Open();
}

catch (Exception except)
{
MessageBox.Show("Login Failed for user: " +
this.oUser.sUID.ToString() + " -- Issue reported was " +
except.Message);
}

try
{
SqlCommand cmd = new SqlCommand("spGetAuthority",
Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserName",
this.oUser.sUID.ToString()));
try
{
reader = cmd.ExecuteReader();
}
catch (Exception except)
{

MessageBox.Show("Database failure -- Issue reported
was " + except.Message);
return(false);

}


if (reader.HasRows)
{
cmd = new SqlCommand("spCreateProject", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserName",
this.oUser.sUID.ToString()));
cmd.Parameters.Add(new SqlParameter("@ProjectName",
this.txtProjectName.Text));
cmd.Parameters.Add(new SqlParameter("@ProjectPath",
this.txtRootPath.Text));
cmd.Parameters.Add(new
SqlParameter("@ProjectComments", this.txtComments.Text));
try
{
iRows = cmd.ExecuteNonQuery();
}
catch (Exception except)
{
MessageBox.Show("Database failure -- Issue
reported was " + except.Message);
return(false);
}

if (iRows==0)
{
MessageBox.Show("The database update failed - 0
rows were added");
return (false);
}

}

else
{
MessageBox.Show("User Database connect Login
Failed");
return (false);
}
}

finally
{
reader.Close();
Conn.Close();
}
return (true);
}
 
Oh - on the update DB code, after the line:

if (reader.HasRows)
{

I do a reader.close(); - For some reason that did not come across in my
paste (*boggle*).
 
Turns out the code ran asynch with the form so the update happened as
soon as the form opened and never happened when the form closed - so I
just opened the form as modal dialog box and it's all good!
 
Back
Top