Tuesday, 16 July 2013

Extract Characters using Substring in SQL

Substring in SQL will Extract the characters from an starting point of the strings in the column name of the database.The syntax for it is

SUBSTRING(string or column name, startIndex, length)
string denotes the columnname or the string value
start index denotes the starting point from where extraction begins
length denotes the how many characters to be extracted.

Example Select query using substring:
SELECT SUBSTRING(Names,2,3) as 'substring' FROM TABLENAME
This query will return Names starting from 3 and return 3 characters from the strings in that columname. 

Monday, 15 July 2013

How to use Substring in Asp.net C#


The Substring method is used to extract the characters from any part of the string.The syntax is

Substring( start_position, num_of_chars_to_grab )

Example:

Substring(0,2)
0 is the starting index of the string
2 is the length or number of characters to be grab or extract.

Example to extract characters form a string using Textbox,Label and Button.

protected void Button1_Click(object sender, EventArgs e)

{

string a = TextBox1.Text.ToString();

string b = a.Substring(2, 2); //Extract 2 characters start from the 2 index position

Label1.Text = b.ToString();

}

Friday, 5 July 2013

Delete Confirmation box in the asp.net Grid view

Create a page with the gridview in the .aspx page as below

<html>
<head runat="server">

<script type="text/javascript">

function ConfirmationBox(Name) {

var result = confirm('Do you want to delete '+ Name +' Details' );

if (result) {

return true;
}

else {

return false;
}
}
</script>

</head>

<body>

<form id="form1" runat="server">
<div>

<asp:gridview autogeneratecolumns="false" datakeynames="Uid" id="deletegrid" onrowdatabound="deletegrid_RowDataBound" runat="server">

<columns>

<asp:boundfield datafield="Name" headertext="Name"/>
<asp:boundfield datafield="Mobile" headertext="Mobile"/>
<asp:boundfield datafield="Full Name" headertext="Fullname"/>

<asp:templatefield headertext="Delete"/>

<itemtemplate>

<asp:linkbutton id="deltlnkbtn" onclick="deltlnkbtn_Click" runat="server">
Delete User</asp:linkbutton>

</itemtemplate>

</asp:templatefield>

</columns>

</asp:gridview>

</div>
</form>
</body>
</html>
Now enter the following code in the .aspx.cs code behind as
SqlConnection con = new SqlConnection("Data Source=localhostname;Integrated Security=true;Initial Catalog=DBname");

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{

BindUser();

}
}

protected void BindUser()
{
//connection open
con.Open();

//sql command to execute query from database
SqlCommand cmd = new SqlCommand("Select * from tablename",con);

cmd.ExecuteNonQuery();

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

//Bind data to gridview
deletegrid.DataSource = ds;
deletegrid.DataBind();

con.Close();
}
protected void deletegrid_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
//get the name from a row
string Name =Convert.ToString(DataBinder.Eval(e.Row.DataItem,"Name"));

//identifying the control in gridview
LinkButton lnkbtns = (LinkButton)e.Row.FindControl("deltlnkbtn");

//raising javascript confirmationbox while clicking the link button
lnkbtns.Attributes.Add("onclick", "javascript:return ConfirmationBox('" + Name + "')");
}
}

protected void deltlnkbtn_Click(object sender, EventArgs e)
{

LinkButton lnkbtn= sender as LinkButton;

//get the particular row linkbutton
GridViewRow delgvrow = lnkbtn.NamingContainer as GridViewRow;

//get the userid of particular row
int userid = Convert.ToInt32(deletegrid.DataKeys[delgvrow.RowIndex].Value.ToString());

string Name = gvrow.Cells[0].Text;

con.Open();
SqlCommand cmd = new SqlCommand("delete from UserDetails where Uid="+Uid,con);

int temp=cmd.ExecuteNonQuery();

con.Close();

if (temp == 1)
{

BindUser();

//Displaying alert message after successfully deletion of user
ScriptManager.RegisterStartupScript(this, this.GetType(), "alertmessage", "javascript:alert('" + Name + " deleted successfully')", true);
}
}

Monday, 1 July 2013

Flying Image from one cart to another

<html> <head> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script> <!-- Script for flying image --> <script type="text/javascript"> $(document).ready(function(){ $(".productPriceWrapRight a img").click(function() { var productIDValSplitter = (this.id).split("_"); var productIDVal = productIDValSplitter[1]; var productX = $("#productImageWrapID_" + productIDVal).offset().left; var productY = $("#productImageWrapID_" + productIDVal).offset().top; var basketX = $("#basketTitleWrap").offset().left; var basketY = $("#basketTitleWrap").offset().top; var gotoX = basketX - productX; var gotoY = basketY - productY; var newImageWidth = $("#productImageWrapID_" + productIDVal).width() / 3; var newImageHeight = $("#productImageWrapID_" + productIDVal).height() / 3; $("#productImageWrapID_" + productIDVal + " img") .clone() .prependTo("#productImageWrapID_" + productIDVal) .css({'position' : 'absolute','border-width':'5px','border-color':'red','border-style':'dotted'} .animate({opacity: 0.5}, 100 ) .animate({opacity: 0.2, marginLeft: gotoX, marginTop: gotoY, width: newImageWidth, height: newImageHeight}, 1200, function() {   $(this).remove(); }); }); });
</script> </head> <body> <form id="form1" runat="server"> <div> <!-- Datalist can also be used --> <!-- add images in the gridview dynamicaly from the database --> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" > <Columns> <asp:TemplateField  HeaderText="Model"><ItemTemplate> <div class="productImageWrap" id="productImageWrapID_<%# Eval("ID") %>"> <img src = '<%# "Handler.ashx?ID= " + Eval ("ID") %> ' id = "img" /> </div> </ItemTemplate> </asp:TemplateField> <asp:TemplateField  HeaderText="Model"> <ItemTemplate> <div class="productPriceWrapRight"> <!-- a href tag can also be used --> <asp:LinkButton ID="a" CommandName="select" runat="server"> <img src="images/add-to-basket2.gif" alt="Add To Basket" width="111" height="32"id="featuredProduct_<%# Eval("ID") %>" /> </asp:LinkButton> </div> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> </div> </div> <div> <div id="basketTitleWrap"> Your Basket </div> </div> </form> </body> </html>

Wednesday, 19 June 2013

JAVASCRIPT VALIDATION FOR PASSWORD COMPARISION

<html>
<script type="text/javascript">
function checkPass() {

var pass1 = document.getElementById("<%=txtpassword.ClientID %>");
var pass2 = document.getElementById("<%=txtconformpass.ClientID %>");

var message = document.getElementById('confirmMessage');

var goodColor = "#66cc66";
var badColor = "#ff6666";

if (pass1.value == pass2.value)
{
message.style.color = goodColor;
message.innerHTML = "Passwords Match!"
}
else
{
message.style.color = badColor;
message.innerHTML = "Passwords not match!"
}
} 
</script>
<body>
<form id="form1" runat="server">

<label>Password</label><asp:TextBox ID="txtpassword" runat="server"
Width="160px" TextMode="Password"></asp:TextBox>
<br /><br />
<label>Conform Password</label><asp:TextBox ID="txtconformpass" runat="server"onKeyup="checkPass()" Width="160px" TextMode="Password"></asp:TextBox>
<span id="confirmMessage" class="confirmMessage"></span>  </form> </body>
</html>

Wednesday, 24 April 2013

How to store date and time separately using datetime picker in C#


Click Add New Item --> Select Windows Form --> Click Toolbox --> Select 2 datetime pickers as
dateTimePicker1 and dateTimePicker2.

Right Click on dateTimePicker1 --> Select properties --> Select Format --> Change it to Short .
Similarly Right Click on dateTimePicker2 --> Select properties --> Select Format --> Change it to Time .

Now Add an Button(button1) and name it as Insert.

Now Double click on the button, the coding .cs page will be opened. In that for the button click event write the following code.

private void button1_Click_1(object sender, EventArgs e)
{

datevalue = Convert.ToString(dateTimePicker1.Value);

DateTime tdate = DateTime.Now;

vdate = Convert.ToDateTime(datevalue);

datevalue= vdate.ToString("yyyy-MM-dd");

string timevalue = Convert.ToString(dateTimePicker2.Value);
DateTime vtime = DateTime.Now;

vtime= Convert.ToDateTime(timevalue);

timevalue = string.Format("{0}:{1} {2}", tt.Hour, tt.Minute, tt.Hour > 12 ? "PM" : "AM");

SqlConnection con = new SqlConnection ("Data Source=.\\SQLEXPRESS; Initial Catalog=sampledb;Integrated Security=True");

con.Open();

SqlCommand cmd = new SqlCommand("INSERT INTO TABLENAME (datefield,timefield)
VALUE ('" +datevalue+"','" +timevalue+"')", con); //Create database with fields datefield,timefield

cmd.ExecuteNonQuery();

con.Close();
}

Friday, 19 April 2013

3 - Tier architecture Asp.net C#

The 3- Tier architecture consists of 3 layers.They are
Application Layer or Presentation Layer
Buisness Logic Layer(BLL) or Buisness Access Layer(BAL)
Data Access Layer(DAL)

Application Layer or Presentation Layer

This layer is for contains the UI part.Through this layer only we get the input. FOR EXAMPLE:  In the aspx page textbox , button,etc... are used for getting the input and to display or present the output.

Here i will show an example to insert the username and password using 2 text boxes and a button.Write the following in the "userinsert.aspx" page.

<asp:TextBox ID="txtusername" runat="server"></asp:TextBox>
<asp:TextBox ID="txtpassword" runat="server"></asp:TextBox>
<asp:Button ID="btninsert" runat="server">Insert</asp:Button>

Buisness Logic Layer(BLL) or Buisness Access Layer(BAL) 

This layer acts as a interface between Application layer and Data Access Layer.Here we use this layer to assign the parameters i.e, get and set and other calculations related with the data like insert data, retrieve data and validating the data.

Right click on your project web application---> select add new item ----> select class file in wizard --->give name as userBLL.CS


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Drawing;
using System.Data;

public class userBLL

{

    userDAL DAL;
public userBLL()
{
        DAL = new userDAL(this);
}

    private string _UserName;
    private string _Password;
    

    private string _Mode;
    private string _Procedure;
  


    public string UserName    {
        get
        {
            return _UserName;
        }
        set
        {
            _UserName = value;
        }
    }

    public string Password

    {
        get
        {
            return _Password;
        }
        set
        {
           _Password = value;
        }
    }

   public string Mode

    {

        get
        {
            return _Mode;
        }
        set
        {
           _Mode = value;
        }
    }

    public string Procedure
    {
        get
        {
            return _Procedure;
        }
        set
        {
            _Procedure = value;
        }
    
    }


   public void Insert(string Connect)
    {
        DAL.Insert(Connect);
    }
  }

Data Access Layer(DAL)
This Layer contains methods or procedures to connect with database and to perform insert,update,delete,get data from database based on our input data.
Right click on your project web application---> select add new item ----> select class file in wizard --->give name as userDAL.CS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public class userDAL
{

    userBAL BAL;
    SqlConnection Con;
    SqlCommand cmd;

     public userDAL(userBAL BAL1)
    {
     BAL=BAL1;
    }
      public void Connection(string Conn)
    {
        Con = new SqlConnection(Conn);
        if (Con.State == ConnectionState.Closed)
        {
            Con.Open();
        }
    }
    public void Insert(string Connect)
    {
        Connection(Connect);
        cmd = new SqlCommand(BAL.Procedure, Con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = BAL.UserName;
        cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = BAL.Password;     
        cmd.Parameters.Add("@Mode", SqlDbType.VarChar).Value = BAL.Mode;
        cmd.ExecuteNonQuery();
        Con.Close();
    }
}

Now BLL and DAL are ready.Now once again return to the application layer and write the following in the button click event of the "userinsert.aspx.cs" page.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
string Conne = ConfigurationManager.ConnectionStrings["Connects"].ConnectionString.ToString();
    userBAL BAL = new userBAL();

  protected void Button1_Click(object sender, EventArgs e)
    {
        BAL.UserName =txtusername.Text;
        BAL.Password = txtpassword;
        BAL.Mode = "Insert"; //Stored procedure for insert
        BAL.Procedure = "SP_Tbl_userinsesrt"; //Stored procedure name 

        BAL.Insert(Conne);
    }

Thats it..3 Layers are completed.Now its ready to run....


back to top