Editing Data using DataList and MySql

[asadikhan]

Hi,

I am trying to use a datalist to edit data in a MySql database as
follows. For now I just want to be able to get the changes to be made
on the dataset. Here is my code:

using System;
using System.Configuration;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;

namespace eDen
{
/// <summary>
/// Summary description for administratorsinformation.
/// </summary>
public class administratorsinformation : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label lblRole;
protected System.Web.UI.WebControls.DataList dlTenants;
protected MySqlDataReader myReader = null;
protected DataSet myDataSet;
protected System.Web.UI.WebControls.DataList dlLandlords;
protected System.Web.UI.WebControls.DataList dlMaintenanceCompanies;
protected System.Web.UI.WebControls.DataList dlBOG;
protected MySqlConnection myConnection;
protected MySqlDataAdapter myDataAdapter;

private void Page_Load(object sender, System.EventArgs e)
{
lblRole.Text = "You are logged in as an Administrator.";
if (!IsPostBack) {
try
{
string strConn =
ConfigurationSettings.AppSettings["ConnectionString"];
myConnection = new MySqlConnection(strConn);
MySqlCommand myCommand = myConnection.CreateCommand();
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "procGetTenantsForAdmin";
myCommand.Parameters.Add("IN_ADMINUSERID", eDen.header.u.userid);
myCommand.Parameters["IN_ADMINUSERID"].Direction =
ParameterDirection.Input;
myDataAdapter = new MySqlDataAdapter(myCommand);
myDataSet = new DataSet();
myDataAdapter.Fill(myDataSet, "Tenants");
dlTenants.DataBind();

}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if (myReader != null)
myReader.Close();
myConnection.Close();
}
}
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.dlTenants.CancelCommand += new
System.Web.UI.WebControls.DataListCommandEventHand ler(this.dlTenants_CancelCommand);
this.dlTenants.EditCommand += new
System.Web.UI.WebControls.DataListCommandEventHand ler(this.dlTenants_EditCommand);
this.dlTenants.UpdateCommand += new
System.Web.UI.WebControls.DataListCommandEventHand ler(this.dlTenants_UpdateCommand);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

private void dlTenants_CancelCommand(object source,
System.Web.UI.WebControls.DataListCommandEventArgs e)
{
dlTenants.EditItemIndex = -1;
myDataAdapter.Fill(myDataSet, "Tenants");
DataBind();
}

private void dlTenants_EditCommand(object source,
System.Web.UI.WebControls.DataListCommandEventArgs e)
{
try
{
dlTenants.EditItemIndex = e.Item.ItemIndex;
myDataAdapter.Fill(myDataSet,"Tenants");
DataBind();
}
catch (Exception ex)
{
Console.Write(ex.ToString());
}
}

private void dlTenants_UpdateCommand(object source,
System.Web.UI.WebControls.DataListCommandEventArgs e)
{
HtmlInputText htEdit;
myDataAdapter.Fill(myDataSet, "Tenants");
htEdit = (HtmlInputText)e.Item.FindControl("txtfirstname");
// [2] because if you look at procGetTenantsForAdmin, firstname
// is the 3rd column. zero based.
myDataSet.Tables["Tenants"].Rows[e.Item.ItemIndex][2] =
htEdit.Value;
dlTenants.EditItemIndex = -1;
DataBind();
}

}
}
However, as soon as I click the Edit button, the code fails on the
following line inside EditComand handler:

myDataAdapter.Fill(myDataSet,"Tenants");

and the error I get is:

"System.NullReferenceException: Object reference not set to an instance
of an object."

I can't figure out what I'm doing wrong. Any ideas?

Thanks.

A

[agapeton]

Have you tried to instantiate the table directly?

Also...
Maybe you should try it with a reader to see it anything works...

[asadikhan]

Where do you want me to instantiate the table? And how?

What advantage would using a reader give me? The problem is not that I
can't read the data. The data displays fine, the problem occurs after I
hit the edit button.

[asadikhan]

I moved the following code out of page_load method and put it inside a
new void method called loaddata().

string strConn =
ConfigurationSettings.AppSettings["ConnectionString"];
myConnection = new
MySqlConnection(strConn);
MySqlCommand myCommand =
myConnection.CreateCommand();
myCommand.CommandType =
CommandType.StoredProcedure;
myCommand.CommandText =
"procGetTenantsForAdmin";

myCommand.Parameters.Add("IN_ADMINUSERID", eDen.header.u.userid);

myCommand.Parameters["IN_ADMINUSERID"].Direction =
ParameterDirection.Input;
myDataAdapter = new
MySqlDataAdapter(myCommand);
myDataSet = new DataSet();
myDataAdapter.Fill(myDataSet,
"Tenants");
dlTenants.DataBind();

And called this method inside page_load, edit, cancel and update
methods. Apparently you need to make a new connection each time when
you're showing the data initially, when the user clicks the edit
button, if he presses cancel or update.

Asad