Computed column in SQL

1>Create table
2>Enter column name and datatype
3>goto column property of that column which we wants computed
4>goto Computed Column Specification
5>In formula write your computed formula

like

(case when [start_date]<=getdate() AND [end_date]>=getdate() then 'active' else 'closed' end)

or using sql query

alter table test add status as(case when [start_date]<=getdate() AND [end_date]>=getdate() then 'active' else 'closed' end)

read more

Scheduling your SP to Execute automatically every day

With help of JOB in sql we can schedule it

at first

1> Addjob name

        use msdb;
        EXEC sp_add_job @job_name = 'test',@owner_login_name ='sa'

2>which SP from Which database is to be executed

here Star is database and tst is SP

   EXEC sp_add_jobstep @job_name = 'test',
   @step_name = 'test1',
   @subsystem = 'TSQL',
   @command = 'exec tst',
   @retry_attempts = 5,
   @retry_interval = 5,
   @database_name = 'Star'

3>Assign time at which sp should be execute

   EXEC sp_add_jobschedule @job_name = 'test',
   @name = 'Scheduled Delivery',
   @freq_type = 4, -- daily
   @freq_interval = 1,   ---only once
   @active_start_time = 230000 ; ----at 11pm

here is Detail

4>Add job name to job server

EXEC sp_add_jobserver @job_name = 'test'

if showing error that sql Server Agent is not started than,

type in run Services.msc,search for sql server agent and start service

To delete Job_name

exec sp_delete_job @job_name = 'test'


More Detais from Here

read more

Crystal Report to print multiple Data

1 > create dataset and add columns
2>create new crystal report
3>add that dataset and design crystal report
4>

using Microsoft.Reporting.WebForms;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

 private CrystalDecisions.CrystalReports.Engine.ReportDocument cr = new ReportDocument();
 static string Crypath = "";

protected void btnprine_Click(object sender, EventArgs e)
    {
        try
        {
            DataTable d1 = new DataTable();
            d1.TableName = "t1";
            d1.Columns.Add("Email");
            d1.Columns.Add("Address");
         
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                CheckBox ck = (CheckBox)GridView1.Rows[i].FindControl("print");
                if (ck.Checked == true)
                {
                    Label id = (Label)GridView1.Rows[i].FindControl("Label5");
                    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Example"].ToString());
                    SqlCommand cmd = new SqlCommand("SP", con);
                    cmd.Parameters.AddWithValue("@id", id.Text);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    DataRow drow = ds.Tables[0].Rows[0];
                    DataRow dr1 = d1.NewRow();                 
                    dr1["Email"] = drow["Email"];
                    dr1["Address"] = drow["Address"];                  
                    d1.Rows.Add(dr1);
                    Crypath = Server.MapPath("~/CrystalReport.rpt").ToString();
                    cr.Load(Crypath);
                    cr.Database.Tables[0].SetDataSource(d1);
                    cr.Refresh();
                }
            }
            Cry("_Report");
        }
        catch(Exception)
        {
           
        }
    }
    public void Cry(string titlename)
    {
        cr.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, titlename);
    }


Another Way

In Report Page

         {

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataTable ds = new DataTable();

        da.Fill(ds);

        DataView dv = new DataView(ds);

        if (Txt_Search_name.Text != "")

        {

            dv.RowFilter = "name LIKE '" + Txt_Search_name.Text.TrimEnd() + "%'";

            dv.RowStateFilter = DataViewRowState.CurrentRows;

            HttpContext.Current.Session["Para1"] = Txt_Search_name.Text.TrimEnd() + "%'";

        }

        else

        {

            HttpContext.Current.Session["Para1"] = "All";

        }

        if (Txt_Search_Mobile.Text == "")

        {

            HttpContext.Current.Session["Para2"] = "All";

        }

        else

        {

            HttpContext.Current.Session["Para2"] = Txt_Search_Mobile.Text;

        }

        if (DD_KYC.SelectedIndex == 0)

        {

            HttpContext.Current.Session["Para3"] = "All";

        }       

        else

        {

            HttpContext.Current.Session["Para4"] = DD_Type.SelectedValue;

        }

        DataTable dt = new DataTable();

        dt = dv.ToTable();

        HttpContext.Current.Session["DatatableReport"] = dt;

        GridView_data.DataSource = dt;

        GridView_data.DataBind();

    }

    protected void imgpdf_Click(object sender, ImageClickEventArgs e)

    {

        Btn_Search_Click(sender, e);

        string url = "Default_report.aspx?Name=List";

        ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "NewWindow", "window.open('" + url + "','_blank');", true);

    }

 

Create New Default_report.aspx

if (Request.QueryString["Name"] != null)

            {

                if (Request.QueryString["Name"] == "List")

                {

                    DataTable dt1 = new DataTable();

                    dt1 = (DataTable)HttpContext.Current.Session["DatatableReport"];          

                    ReportDocument crystalReport = new ReportDocument();

                    crystalReport.Load(Server.MapPath("../Reports/xyz.rpt"));

                    crystalReport.SetDataSource(dt1);

                    crystalReport.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "_Report");

                    //CrystalReportViewer1.ReportSource = crystalReport;

                    //CrystalReportViewer1.DataBind();

                }

}

For Loading PDF In Browser

 protected void Download_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Conection"].ToString());
        SqlCommand cmd = new SqlCommand("reg", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@RegNo", No.Text);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable ds = new DataTable();
        da.Fill(ds);

        ReportDocument crystalReport = new ReportDocument();

        if (lblChlnNo.Text.Contains("T"))
        {
            crystalReport.Load(Server.MapPath("../Reports/Report.rpt"));
        }
     
        crystalReport.SetDataSource(ds);
      
        try
        {
            ExportOptions CrExportOptions;
            DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
            PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions();
            CrDiskFileDestinationOptions.DiskFileName = Server.MapPath(@"../Reports/AllReport" + No.Text + ".pdf");
            CrExportOptions = crystalReport.ExportOptions;
            {
                CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
                CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
                CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
                CrExportOptions.FormatOptions = CrFormatTypeOptions;
            }
            crystalReport.Export();
        }
        catch (Exception ex)
        {

        }

        string url = "../Stores/Default_report.aspx?Name=Rpt_No";
        HttpContext.Current.Session["url"] = "../Reports/AllReport" + No.Text + ".pdf");

        ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "NewWindow", "window.open('" + url + "','_blank');", true);
        clear();
    }

 

in Default.aspx

 

 if (Request.QueryString["Name"] == "Rpt_No")
                {                 
                    ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "NewWindow", "window.open('" + HttpContext.Current.Session["url"] + "','_self');", true);
                }

 

read more

Creating graph from multiple columns and printing Graph with Div Tag

/*For Graph*/

 protected void Page_Load(object sender, EventArgs e)
    { 
               DataTable dt = GetData();
                LoadChartData(dt);
     }

private void LoadChartData(DataTable initialDataSource)
    {
        for (int i = 1; i < initialDataSource.Columns.Count; i++)
        {
            Series series = new Series();
            foreach (DataRow dr in initialDataSource.Rows)
            {
                if (!(dr[i] is DBNull))
                {
                    int y = Convert.ToInt32(dr[i]);
                    series.Points.AddXY(dr["Data"].ToString(), y);
                }
            }
            Chart1.Series.Add(series);
        }
    }

 private DataTable GetData()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Example"].ToString());
        SqlCommand cmd = new SqlCommand("select * from xyz where id=@id ", con);
        cmd.Parameters.AddWithValue("@id", Request.QueryString["id"]);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt1 = new DataTable();
        da.Fill(dt1);

        SqlCommand cmd1 = new SqlCommand("SP", con);
        cmd1.CommandType = CommandType.StoredProcedure;
        cmd1.Parameters.AddWithValue("@Name", Request.QueryString["NM"]);
        SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
        DataTable dts = new DataTable();
        da1.Fill(dts);

        DataRow dr = dt1.Rows[0];
        DataTable dt = new DataTable();
        dt.Columns.Add("Data");
        dt.Columns.Add("Value1");
        DataRow dr1 = dt.NewRow();
        dr1["Data"] = dr["Choice1"];
        lblr1.Text = dr["Choice1"].ToString();
        dr1["Value1"] = 12;
        dt.Rows.Add(dr1);

        DataRow dr2 = dt.NewRow();
        dr2["Data"] = dr["Choice2"];
        lblr2.Text = dr["Choice2"].ToString();
        dr2["Value1"] = 20;
      
        dt.Rows.Add(dr2);
        DataRow dr3 = dt.NewRow();
        dr3["Data"] = dr["Choice3"];
        lblr3.Text = dr["Choice3"].ToString();
        dr3["Value1"] = 30;
        dt.Rows.Add(dr3);

      return dt;
    }

To print this graph and Div Tag

 protected void print(object sender, EventArgs e)
   {

        /* To Print DIV */

        Response.ContentType = "application/pdf";
        Response.AddHeader("content-disposition", "attachment;filename=Panel.pdf");
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        StringWriter stringWriter = new StringWriter();
        HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);

        divprint.RenderControl(htmlTextWriter);
        StringReader stringReader = new StringReader(stringWriter.ToString());
        Document Doc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
        HTMLWorker htmlparser = new HTMLWorker(Doc);
        PdfWriter.GetInstance(Doc, Response.OutputStream);
        Doc.Open();
        using (MemoryStream stream = new MemoryStream())  /*To Print Graph Using iTextSharp*/
        {
            Chart1.SaveImage(stream, ChartImageFormat.Png);
            iTextSharp.text.Image chartImage = iTextSharp.text.Image.GetInstance(stream.GetBuffer());
            chartImage.ScalePercent(75f);
            chartImage.Alignment = iTextSharp.text.Element.ALIGN_CENTER;
            Doc.Add(chartImage);
        }
        htmlparser.Parse(stringReader);
        Doc.Close();
        Response.Write(Doc);
        Response.End();
}

read more

Set timer for online Exam

.ASPX

 <asp:Timer ID="Timer1" runat="server" Interval="1000" OnTick="Timer1_click">
        </asp:Timer>

 CS

  protected void Page_Load(object sender, EventArgs e)
{
       DataTable dt = bl.bind_Details();
        DataRow drows = dt.Rows[0];
        string timeing = drows["Exam_time"].ToString();
        string[] s = timeing.Split(new char[] { ':' });
        int hours = s[0];
        int min = s[1];
        int lastmin = Convert.ToInt16(min) - 1;
        secound = 60;
}

protected void Timer1_click(object sender, EventArgs e)
    {

        Session["sec"] = DateTime.Now.AddSeconds(secound);
        Session["min"] = DateTime.Now.AddMinutes(minute);
        Session["hour"] = DateTime.Now.AddHours(hour);

        TimeSpan tsec = new TimeSpan();
        TimeSpan tmin = new TimeSpan();
        TimeSpan thour = new TimeSpan();

        tsec = (DateTime)Session["sec"] - DateTime.Now;
        tmin = (DateTime)Session["min"] - DateTime.Now;
        thour = (DateTime)Session["hour"] - DateTime.Now;

        secound = (int)tsec.TotalSeconds;
        minute = (int)tmin.TotalMinutes;
        hour = (int)thour.TotalHours;

        if (secound <= 0)
        {
            if (hour == 0 && minute == 0)

                secound = 0;
            else
            {
                secound = 60;
                minute -= 1;
            }
        }
        if (minute <= 0)
        {
           if (hour  < 0 && minute < 0)
            {
                hour = minute = secound = 0;

            }
            else
            {
                if (hour == 0 && minute == 0)
                    hour = minute = 0;

                if (hour > 0 && minute < 0)
                {
                    minute = 59;
                    hour -= 1;
                }
            }
        }
        if (minute <= -1 || hour <= -1)
        {
            if (hour <= -1)
            {
                minute = 0;
                hour += 1;
            }
            else
                minute -= 1;
            secound = 0;
            minute += 1;

        }
        else

            if (secound > 0)
                secound -= 1;

        if (hour == 0 && minute == 0 && secound == 0)
        {
            submit_answer();
            result();
            Response.Redirect("TimeUp.aspx");                  
        }
        lbltime.Text = hour + " : " + minute + " : " + secound;
    }

read more

Create RDLC report

1> Create new Dataset , add columns
2> Create SP
3>add references of Microsoft.ReportViewer.WebForms
4>draag and drop RDLC report

Register assambly

ASPX

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

 <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"
                    Font-Size="8pt" InteractiveDeviceInfos="(Collection)"
                    WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt">
                    <LocalReport ReportPath="Resolution_report.rdlc">
                        <DataSources>
                            <rsweb:ReportDataSource Name="DataSet1" />
                        </DataSources>
                   </LocalReport>
 </rsweb:ReportViewer>

CS

on button click

protected void Btndownloadpdf_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Example"].ToString());
        SqlCommand cmd = new SqlCommand("Your_SP", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@id", id.Text);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);

        ReportDataSource datasource = new ReportDataSource("DataSet1", ds.Tables[0]);
        ReportViewer ReportViewer1 = new ReportViewer();
        ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(datasource);
        ReportViewer1.LocalReport.Refresh();
        byte[] byt = ReportViewer1.LocalReport.Render("PDF");
        Response.Buffer = true;
        Response.Clear();
        Response.ContentType = "application/pdf";
        Response.AddHeader("content-disposition", "attachment; filename= Report" + "." + "pdf");
        Response.OutputStream.Write(byt, 0, byt.Length); // create the file 
        Response.Flush(); // send it to the client to download 
        Response.End();
    }

read more

Javascript validation to allow only alpahbet or numeric value in textbox

Codes

charCode > 31 --> allow backspace to work

charCode > 32 --> allow space to work

 charCode <  97 || charCode >122 --> allow only alphabetic value

 charCode <  45 || charCode > 45 --> allow only - as special character

 charCode <  65 || charCode > 93 --> allow capslock character

 charCode <  65 || charCode > 90 --> allow only . as special character

 charCode <  48 || charCode > 57 --> allow numeric

/*For alpha and numeric*/

 <script type="text/javascript">
        function functionN1(evt) {
            if (evt.charCode > 32 && (evt.charCode < 48 || evt.charCode > 57) && (evt.charCode < 97 || evt.charCode > 122) && (evt.charCode < 65 || evt.charCode > 93)) {
                return false;
            }
        }
    </script>

 <asp:TextBox ID="txtcname" runat="server" onkeypress="return functionc(event)></asp:TextBox>

/*For numeric*/ 

 <script type="text/javascript">
        function functionx(evt) {
            if (evt.charCode > 31 && (evt.charCode < 48 || evt.charCode > 57)) {
                return false;
            }
        }
    </script>

 <asp:TextBox ID="txtcname" runat="server" onkeypress="return functionx(event)></asp:TextBox>

/*for alphabets*/

function functionN1(evt) {
            if (evt.charCode > 32 && (evt.charCode < 97 || evt.charCode > 122) && (evt.charCode < 65 || evt.charCode > 93)) {
                return false;
            }
        }
    </script>

 <asp:TextBox ID="txtcname" runat="server" onkeypress="return functionc(event)></asp:TextBox>

read more

Use ImageButton for uploading file and priview image on that ImageButton before uploading

.ASPX

<head runat="server">
    <title></title>
    <script type="text/javascript" src="http://code.jquery.com/jquery-1.8.2.js"></script>
    <script type="text/javascript">
        function showimagepreview(input) {
            if (input.files && input.files[0]) {
                var filerdr = new FileReader();
                filerdr.onload = function (e) {
                    $('#ImageButton1').attr('src', e.target.result);
                }
                filerdr.readAsDataURL(input.files[0]);
            }
        }
    </script>
    <script language="javascript" type="text/javascript">
        function clk() {
            document.getElementById("FileUpload1").click();
        }      
    </script>
</head>

<body>
    <form id="form1" runat="server">
    <div>
        <div style="display: none">
            <asp:FileUpload ID="FileUpload1" runat="server" onchange="showimagepreview(this)"/>
        </div>   
        <asp:ImageButton ID="ImageButton1" runat="server" OnClientClick="clk(); return false;"
            ImageUrl="~/img/b1.png" Width="40px" Height="40px"></asp:ImageButton>
        <asp:Button ID="btnupload" runat="server" OnClick="btnupload_Click" Text="Upload" />
    </div>
    </form>
</body>

calling from code behind

protected void Page_Load(object sender, EventArgs e)
    {
 ImageButton1.Attributes.Add("onclick", string.Format("jQuery('#" + FileUpload1.ClientID + "').click();return true;",ClientScript.GetPostBackEventReference(ImageButton1, null)));

reference  :-  aspdotnet-suresh

read more