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.Configur ation;
using System.Collecti ons;
using System.Componen tModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.Sess ionState;
using System.Web.UI;
using System.Web.UI.W ebControls;
using System.Web.UI.H tmlControls;
using MySql.Data.MySq lClient;

namespace eDen
{
/// <summary>
/// Summary description for administratorsi nformation.
/// </summary>
public class administratorsi nformation : System.Web.UI.P age
{
protected System.Web.UI.W ebControls.Labe l lblRole;
protected System.Web.UI.W ebControls.Data List dlTenants;
protected MySqlDataReader myReader = null;
protected DataSet myDataSet;
protected System.Web.UI.W ebControls.Data List dlLandlords;
protected System.Web.UI.W ebControls.Data List dlMaintenanceCo mpanies;
protected System.Web.UI.W ebControls.Data List dlBOG;
protected MySqlConnection myConnection;
protected MySqlDataAdapte r myDataAdapter;

private void Page_Load(objec t sender, System.EventArg s e)
{
lblRole.Text = "You are logged in as an Administrator." ;
if (!IsPostBack) {
try
{
string strConn =
ConfigurationSe ttings.AppSetti ngs["ConnectionStri ng"];
myConnection = new MySqlConnection (strConn);
MySqlCommand myCommand = myConnection.Cr eateCommand();
myCommand.Comma ndType = CommandType.Sto redProcedure;
myCommand.Comma ndText = "procGetTenants ForAdmin";
myCommand.Param eters.Add("IN_A DMINUSERID", eDen.header.u.u serid);
myCommand.Param eters["IN_ADMINUSERID "].Direction =
ParameterDirect ion.Input;
myDataAdapter = new MySqlDataAdapte r(myCommand);
myDataSet = new DataSet();
myDataAdapter.F ill(myDataSet, "Tenants");
dlTenants.DataB ind();

}
catch (MySql.Data.MyS qlClient.MySqlE xception ex)
{
Console.WriteLi ne(ex.ToString( ));
}
finally
{
if (myReader != null)
myReader.Close( );
myConnection.Cl ose();
}
}
}

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

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeCompo nent()
{
this.dlTenants. CancelCommand += new
System.Web.UI.W ebControls.Data ListCommandEven tHandler(this.d lTenants_Cancel Command);
this.dlTenants. EditCommand += new
System.Web.UI.W ebControls.Data ListCommandEven tHandler(this.d lTenants_EditCo mmand);
this.dlTenants. UpdateCommand += new
System.Web.UI.W ebControls.Data ListCommandEven tHandler(this.d lTenants_Update Command);
this.Load += new System.EventHan dler(this.Page_ Load);

}
#endregion

private void dlTenants_Cance lCommand(object source,
System.Web.UI.W ebControls.Data ListCommandEven tArgs e)
{
dlTenants.EditI temIndex = -1;
myDataAdapter.F ill(myDataSet, "Tenants");
DataBind();
}

private void dlTenants_EditC ommand(object source,
System.Web.UI.W ebControls.Data ListCommandEven tArgs e)
{
try
{
dlTenants.EditI temIndex = e.Item.ItemInde x;
myDataAdapter.F ill(myDataSet," Tenants");
DataBind();
}
catch (Exception ex)
{
Console.Write(e x.ToString());
}
}

private void dlTenants_Updat eCommand(object source,
System.Web.UI.W ebControls.Data ListCommandEven tArgs e)
{
HtmlInputText htEdit;
myDataAdapter.F ill(myDataSet, "Tenants");
htEdit = (HtmlInputText) e.Item.FindCont rol("txtfirstna me");
// [2] because if you look at procGetTenantsF orAdmin, firstname
// is the 3rd column. zero based.
myDataSet.Table s["Tenants"].Rows[e.Item.ItemInde x][2] =
htEdit.Value;
dlTenants.EditI temIndex = -1;
DataBind();
}

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

myDataAdapter.F ill(myDataSet," Tenants");

and the error I get is:

"System.NullRef erenceException : 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 =
ConfigurationSe ttings.AppSetti ngs["ConnectionStri ng"];
myConnection = new
MySqlConnection (strConn);
MySqlCommand myCommand =
myConnection.Cr eateCommand();
myCommand.Comma ndType =
CommandType.Sto redProcedure;
myCommand.Comma ndText =
"procGetTenants ForAdmin";

myCommand.Param eters.Add("IN_A DMINUSERID", eDen.header.u.u serid);

myCommand.Param eters["IN_ADMINUSERID "].Direction =
ParameterDirect ion.Input;
myDataAdapter = new
MySqlDataAdapte r(myCommand);
myDataSet = new DataSet();
myDataAdapter.F ill(myDataSet,
"Tenants");
dlTenants.DataB ind();

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