Message box for web app

ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Code already exists');", true);

Editable Grid View (Part 2)

          Click here to view Part1

• Within Grid View RowDataBound method, we can bind the drop down lists in Edit item template.

[code] protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { try { if (e.Row.RowType == DataControlRowType.DataRow && GridView1.EditIndex == e.Row.RowIndex) { DropDownList ddlLocation = (DropDownList)e.Row.FindControl("ddlLocation"); DataTable dtLocation = myService.getLocation(); ddlLocation.DataSource = dtLocation; ddlLocation.DataValueField = "ListId"; ddlLocation.DataTextField = "LocationName"; ddlLocation.DataBind(); Label lblLocationId = (Label)e.Row.FindControl("lblLocationId"); ddlLocation.SelectedValue = lblLocationId.Text; } } catch (Exception ex) { throw ex; } } [/code]

• Add following codes within RowEditing and on RowCancellingEdit methods.

[code] protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { try { GridView1.EditIndex = e.NewEditIndex; bindDataToGrid(); } catch (Exception ex) { throw ex; } } protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { try { GridView1.EditIndex = -1; bindDataToGrid(); } catch (Exception ex) { throw ex; } } } [/code]

• Write the code as follows for row updating.

[code] protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { try { TextBox txtAddress = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtAddress"); DropDownList ddlLocation = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("ddlLocation"); CheckBox chkEditActive = (CheckBox)GridView1.Rows[e.RowIndex].FindControl("chkEditActive"); string custListId = GridView1.DataKeys[e.RowIndex].Value.ToString(); string address = txtAddress.Text; string locationListId = string.Empty; bool isActive = false; if (chkEditActive.Checked) { isActive = true; } if(ddlLocation.SelectedIndex > 0) { locationListId = ddlLocation.SelectedValue; } bool status = myService.updateCustomer(locationListId, address, isActive, custListId); GridView1.EditIndex = -1; bindDataToGrid(); } catch (Exception ex) { throw ex; } } [/code]

• Grid view design

[code] [/code]

Editable Grid View (Part 1)

• Create a Grid view.

• Convert some columns to template fields and bind data as follows.

• Sql query results are like this.

• Bind the data to grid as follows.

[code] namespace MyWebApp.Presentation { public partial class MyForm : System.Web.UI.Page { MyService myService = new MyService(); protected void Page_Load(object sender, EventArgs e) { try { DataTable dt = myService.getData(); GridView1.DataSource = dt; string[] dataKey = new string[1]; dataKey[0] = "CustListId"; GridView1.DataKeyNames = dataKey; GridView1.DataBind(); } catch (Exception ex) { throw ex; } } } } [/code]

• You can see the result like this.

Click here to view Part2

Create a simple web site (with data base connection)

• From the menu select  File --> New Project.
• Give a name for your project and save.
• Open the Web.config file and add the following lines to create the connection.

[code] <connectionStrings> <add name="AppConString" connectionString="Data Source=[Server name]; Initial Catalog=[Database name]; User ID=[Database username]; Password=[Database password]" providerName="System.Data.SqlClient" /> </connectionStrings> [/code]

• Create a new folder named "Data" and create a class within it named "DBCon".
• Add the following lines at the top of the class.

[code]using System.Data; using System.Data.SqlClient; using System.Configuration;[/code]

•  Add the methods  as follows to open and close connection. 

[code] public class DBCon { public SqlConnection con; public void ReadyConnection() { try { con = new SqlConnection(); con.ConnectionString = ConfigurationManager.ConnectionStrings["AppConString"].ConnectionString; if (con.State != ConnectionState.Open) con.Open(); } catch (Exception ex) { throw ex; } } public void CloseConnection() { try { if (con.State == ConnectionState.Open) con.Close(); } catch (Exception ex) { throw ex; } } } [/code]

• Add the following methods to deal with the database.

[code] public DataTable GetDataTableFromSQL(string sSQL) { DataTable dataTable = new DataTable(); try { ReadyConnection(); SqlCommand cmd = new SqlCommand(sSQL, con); cmd.CommandTimeout = 0; dataTable.Load(cmd.ExecuteReader()); return dataTable; } catch (Exception ex) { throw ex; } finally { CloseConnection(); } } public DataTable GetDataTableFromSp(string spName, SqlCommand cmd) { try { DataTable dt = new DataTable(); ReadyConnection(); cmd.CommandText = spName; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; cmd.CommandTimeout = 0; dt.Load(cmd.ExecuteReader()); return dt; } catch (Exception ex) { throw ex; } finally { CloseConnection(); } } public bool ExecuteNonQueryFromSQL(string sSQL) { try { ReadyConnection(); SqlCommand cmd = new SqlCommand(sSQL, con); cmd.CommandTimeout = 0; int count = cmd.ExecuteNonQuery(); if (count > 0) return true; else return false; } catch (Exception ex) { throw ex; } finally { CloseConnection(); } } public bool ExecuteNonQueryFromSp(string spName, SqlCommand cmd) { try { ReadyConnection(); cmd.CommandText = spName; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; int count = cmd.ExecuteNonQuery(); if (count > 0) return true; else return false; } catch (Exception ex) { throw ex; } finally { CloseConnection(); } } [/code]

• Then by calling these methods we can execute the database queries as needed.

Create temp table

[code] SELECT * INTO tempDistributor FROM Distributors [/code]

Insert/ Update a table with data from another table

Insert

[code] insert into Distributors(CompanyName,City,District,TelephoneNo, DistributorListId,TypeListId) select CompanyName,City,'Colombo',Phone,88,2 from Temp [/code]


Update

[code] update Distributors set Distributors.District = Temp.District from Distributors inner join Temp on Distributors.ListId = Temp.ListId [/code]

Create auto complete drop down list (Windows forms)

[code] List<DataRow> companyList = new List<DataRow>(); DataTable dt = loadEMEIsService.getLocationDetails(); if (dt.Rows.Count > 0) { DataRow dr = dt.NewRow(); dr["Location"] = "---Select---"; dr["ListId"] = -1; dt.Rows.InsertAt(dr, 0); cmbFromLocation.DataSource = dt; cmbFromLocation.DisplayMember = "Location"; cmbFromLocation.ValueMember = "ListId"; this.cmbFromLocation.AutoCompleteSource = AutoCompleteSource.ListItems; this.cmbFromLocation.AutoCompleteMode = AutoCompleteMode.SuggestAppend; } [/code]

Clear Grid View rows

[code] for (int i = 0; i < dataGridView1.Rows.Count ; i++) { dataGridView1.Rows.RemoveAt(i); i--; if (dataGridView1.Rows.Count == 0) break; } [/code]

Add a select row at the top of a drop down list

[code] DataTable tableItem = new DataTable(); tableItem = jobCardService.loadAllItems(); if (tableItem.Rows.Count > 0) { DataRow row = tableItem.NewRow(); row["ItemCode"] = "--------- Select --------"; row["ListId"] = "-1"; tableItem.Rows.InsertAt(row, 0); ddlItem.DataSource = tableItem; ddlItem.DataTextField = "ItemCode"; ddlItem.DataValueField = "ListId"; ddlItem.DataBind(); } [/code]

Add styles for Grid Views

[code] ----------------------- ----------------------- [/code]

Format numbers as currency

Call this java script function in text box onblur event.

[code] function formatCurrency(num) { num = num.toString().replace(/\$|\,/g, ''); if (isNaN(num)) num = "0"; sign = (num == (num = Math.abs(num))); num = Math.floor(num * 100 + 0.50000000001); cents = num % 100; num = Math.floor(num / 100).toString(); if (cents < 10) cents = "0" + cents; for (var i = 0; i < Math.floor((num.length - (1 + i)) / 3) ; i++) num = num.substring(0, num.length - (4 * i + 3)) + ',' + num.substring(num.length - (4 * i + 3)); return (((sign) ? '' : '-') + num + '.' + cents); } [/code]
[code] [/code]