Tuesday, July 8, 2008

Insert, Update, delete, paging in formview


        
        

ID:
Name:
City:
-

Name:
City:
-

Sample Database
ID:
Name:
City:
- -
Page :<%=fv.PageIndex+1%> « Prev Next »
public partial class formvieweditupdate : System.Web.UI.Page
{
SqlConnection dbConn = null;
SqlDataAdapter da = null;
DataTable dTable = null;
String strConnection = null;
String strSQL = null;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
bindData();
}
}
private void bindData()
{
try
{
strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
dbConn = new SqlConnection(strConnection);
dbConn.Open();
strSQL = "SELECT companyid, companyname,companycity  from companydetail order by companyname";
da = new SqlDataAdapter(strSQL, dbConn);
dTable = new DataTable();
da.Fill(dTable);
fv.DataSource = dTable;
fv.DataBind();
}
finally
{
if (dbConn != null)
{
dbConn.Close();
}
}
}

protected void Update(object sender, FormViewUpdateEventArgs e)
{
try
{
int cid = Convert.ToInt32((fv.Row.FindControl("theIDLabel1") as Label).Text);
string cname = (fv.Row.FindControl("theNameTextBox") as TextBox).Text;
string ccity = (fv.Row.FindControl("theCityTextBox") as TextBox).Text;
strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
dbConn = new SqlConnection(strConnection);
dbConn.Open();
SqlCommand   da = new SqlCommand( "Update companydetail set companyname=@comp,companycity=@comcity where companyid=@cid", dbConn);
da.Parameters.Add("@cid", SqlDbType.Int).Value = cid;
da.Parameters.Add("@comp", SqlDbType.VarChar).Value = cname;
da.Parameters.Add("@comcity", SqlDbType.VarChar).Value = ccity;
da.ExecuteNonQuery();
fv.ChangeMode(FormViewMode.ReadOnly);
}

finally
{
if (dbConn != null)
{
dbConn.Close();
bindData();
}
}

}
protected void Insert(object sender, FormViewInsertEventArgs e)
{
try
{

string cname = (fv.Row.FindControl("theNameTextBox") as TextBox).Text;
string ccity = (fv.Row.FindControl("theCityTextBox") as TextBox).Text;
strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
dbConn = new SqlConnection(strConnection);
dbConn.Open();
SqlCommand da = new SqlCommand("insert into companydetail values(@comp,@add,@comcity)", dbConn);
da.Parameters.Add("@add", SqlDbType.VarChar).Value ="";
da.Parameters.Add("@comp", SqlDbType.VarChar).Value = cname;
da.Parameters.Add("@comcity", SqlDbType.VarChar).Value = ccity;
da.ExecuteNonQuery();
fv.ChangeMode(FormViewMode.ReadOnly);
}

finally
{

if (dbConn != null)
{
dbConn.Close();
bindData();
}
}

}
protected void Delete(object sender, FormViewDeleteEventArgs e)
{
try
{
int cid = Convert.ToInt32((fv.Row.FindControl("theIDLabel") as Label).Text);
strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
dbConn = new SqlConnection(strConnection);
dbConn.Open();
SqlCommand da = new SqlCommand("delete from companydetail where companyid=@cid", dbConn);
da.Parameters.Add("@cid", SqlDbType.Int).Value = cid;
da.ExecuteNonQuery();
fv.ChangeMode(FormViewMode.ReadOnly);
}

finally
{

if (dbConn != null)
{
dbConn.Close();
bindData();
}
}

}
protected void ChangMode(object sender, FormViewModeEventArgs e)
{
fv.ChangeMode(e.NewMode);
if (e.NewMode.ToString() != "Edit" && e.NewMode.ToString()!="Insert")
{
bindData();
}

}

protected void pagechanged(object sender, FormViewPageEventArgs e)
{
fv.PageIndex = e.NewPageIndex;
bindData();
}
}

7 comments :

  1. It is realy excelent code for understanding the formview. it help me to mach ..............

    ReplyDelete
  2. Hello,

    The above article is really useful for anyperson. I have given your above code in my sample program. When i click on New, Edit, Delete button nothing is happening. Please help.

    ReplyDelete
  3. Thanks for such a detailed post on the FormView. If I can suggest try adding the syntaxhighlighter to enhance your page with code http://code.google.com/p/syntaxhighlighter/

    ReplyDelete
  4. Thanks for the post. It would be better if the code were syntax-highlighted. You could do this using google syntax highlighter plugin. I am not sure but I guess wordpress has its own syntax highlighting for code. Please google it once.

    You blog is really nice one. We expect more articles from you. You a lot of experiences. Each bit of them is very very useful for all asp.net programmers. Thanks.

    Happy Programming!

    ReplyDelete
  5. In the ChangMode method, why didn't you just write:

    fv.ChangeMode(e.NewMode);

    if (e.NewMode.ToString() != “Edit” && e.NewMode.ToString() !=”Insert”)
    bindData();

    It would avoid repeating fv.ChangeMode three times.

    ReplyDelete

AJAX, asp, Asp.net, asp.net and sql server security, Asp.net IntemIndex, C#, Css, DataBinder.Eval, DataKeyNames, Datalist, Datapager, DataSet, DataTable, DropDownList, FindControl, gridview, JavaScript, jquery, Listview, Paging, Regex, RegularExpression, Repeater, Server side validation, Sql Server, timer, timercallback, Validation, XML, xmlnode, XPath