jump to navigation

Launch of WebSite January 29, 2009

Posted by mudassarkhan in Uncategorized.
add a comment

I have launched my website

www.aspsnippets.com

I have closed this blog and all the articles will be available on the site.

Thanks all for the wonderful comments and appreciation.

Sending Email using VB January 16, 2009

Posted by mudassarkhan in VB.Net.
Tags: , , , , , ,
add a comment

Here I have discussed Sending Mails using VB.Net

 

First Import these Name Spaces

Imports System.Net

Imports System.Net.Mail

 

See the function below

Public Function SendEmail(ByVal strTo As String, ByVal strFrom As String, ByVal strSubject As String, ByVal strBody As String, ByVal strAttachmentPath As String, ByVal IsBodyHTML As Boolean) As Boolean

 

        Dim arrToArray As String()

        Dim splitter As Char = “;”

 

        ‘Loop through the recepient list and separate them

        ‘based on semi colon

        arrToArray = strTo.Split(splitter)

 

        Dim mm As MailMessage = New MailMessage()

        mm.From = New MailAddress(strFrom)

        mm.Subject = strSubject

        mm.Body = strBody

   

    ‘True – HTML Body

    ‘False – Text Body

        mm.IsBodyHtml = IsBodyHTML

 

        ‘Add the recepient email Addresses

        For Each s As String In arrToArray

            mm.To.Add(New MailAddress(s))

        Next

 

        ‘Add Attachment

        mm.Attachments.Add(New Attachment(strAttachmentPath))

        Dim smtp As SmtpClient = New SmtpClient()

        Try

            ‘Your SMTP Server

            smtp.Host = “Your smtp server”

 

            ‘SSL Settings depending on your Server

            smtp.EnableSsl = True / False

 

            ‘Reply To Address (Optional)

            mm.ReplyTo = New MailAddress(“your reply to email”)

 

            ‘Creadentials for the Server

            Dim NetworkCred As NetworkCredential

            =New System.Net.NetworkCredential()

 

            ‘Your Email

            NetworkCred.UserName = ” sender@abc.com”

 

            ‘Your Password

            NetworkCred.Password = “your password”

 

            smtp.UseDefaultCredentials = True

            smtp.Credentials = NetworkCred

 

            ‘Port No of the Server

            smtp.Port = Your Port No ‘ Port No

            smtp.Send(mm)

            Return True

        Catch

            Return False

        Finally

            smtp = Nothing

            mm = Nothing

        End Try

    End Function

 

To call this function

 

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

SendEmail(“sender@abc.com”, “recepient@abc.com”, “Subject”,

“Body”, “C:\MyDoc.doc”, True)

End Sub

 

Also these are the settings for sending email using Gmail

 

Host – smtp.gmail.com

SSL – true

Port No 465, 587 or just comment Port No

UserName – Gmail Email

Password Gmail Password

 

 

Job Schedular SQL Server 2000 January 13, 2009

Posted by mudassarkhan in SQL Server.
Tags: , ,
4 comments

  Here I am explaining how to create a Scheduled Batch Job in SQL Server 2000.

 

  The Steps to Create a Batch Job in SQL Server 2000 are described below.

 

 

 Open SQL Server Enterprise Manager. Expand the Management node.

 Right Click Job from the Sub Nodes and Select New Job from the context Menu.

 

 For details refer Figure 1.0

 

 

img14Figure 1.0

 

 

 

 

In the General Tab, you will have Fill the following Information

1. Name of the Batch Job

2. Category of the Batch Job

3. Owner of the Batch Job

4. Description of the Batch Job. If any

 

For Details refer Figure 2.0

img23Figure 2.0

 

 

 

 

 

 The second Tab on the New Job window is Steps. Here you have to Click on New Step which will open the New Job Step window. You will need to fill the following details.

 

 

 

 

  1. Step Name – Name of the Job Step
  2. Type – Type of the step. Since its a Stored Procedure I have Selected TSQL.
  3. Database – The Database on which the stored procedure or Query needs to run
  4. Command – The SQL Query you want to execute.

     For Details refer Figure 3.0

img32Figure 3.0 

For more details refer Figure 4.0

The Third Tab is for Scheduling the Batch Job. Click new a New Jon Schedule Window will open. In that Window, give name to the Job Schedule. And select Recurring Schedule Type since the job will run on daily basis. 

  

 

img43      Figure 4img54

 

 

 After you select Recurring Radio Button Click on Change Button. Set the Schedule Settings

 As given in Figure 5

 

 Figure 5

 

 

6. Finally, to delete a SQL Job you can do the following as depicted in  Figure 6

 

img62Figure 6

  When you click on New Job a Window will open. It has four Tabs. The First Tab is General

EXPORT DATA TO EXCEL ADO.NET January 12, 2009

Posted by mudassarkhan in ADO.Net, C#.
Tags: , , , , , , , ,
2 comments

Here I will explain How to insert data in Excel files using ADO.Net

First comes the Excel 97-2003 format (*.XLS)

For this Microsoft OLEDB Jet Driver will be used. Below is the function to insert Data into Excel Sheet,

just like you do in a Database.

 

Here’s the Mapping

Database   <—————>    Excel

Sheet         <—————>     Table

 

Here’s the function for Excel 97-2003 Format

 //Add New Row To Excel 97-2003 File

private Boolean AddExcelRow(String strFilePath)

{

if (!File.Exists(strFilePath)) return false;

String strExcelConn = “Provider=Microsoft.Jet.OLEDB.4.0;”

+ “Data Source=” + strFilePath + “;”

+ “Extended Properties=’Excel 8.0;HDR=Yes'”;

OleDbConnection connExcel = new OleDbConnection(strExcelConn);

OleDbCommand cmdExcel = new OleDbCommand();

try

{

    cmdExcel.Connection = connExcel;

 

    //Check if the Sheet Exists

    connExcel.Open();

    DataTable dtExcelSchema;

    dtExcelSchema =

    connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

    connExcel.Close();

    DataRow[] dr = dtExcelSchema.Select(“TABLE_NAME = ‘tblData'”);

 

    //if not Create the Sheet

    if (dr == null || dr.Length == 0)

    {

        cmdExcel.CommandText = “CREATE TABLE [tblData]” +

         “(ID varchar(10), Name varchar(50));”;

        connExcel.Open();

        cmdExcel.ExecuteNonQuery();

        connExcel.Close();

    }

 

    connExcel.Open();

   

    //Add New Row to Excel File

    cmdExcel.CommandText = “INSERT INTO [tblData] (ID, Name)” +

    ” values (‘1’, ‘MAK’)”;

    cmdExcel.ExecuteNonQuery();

    connExcel.Close();

    return true;

}

catch

{

    return false;

}

finally

{

    cmdExcel.Dispose();

    connExcel.Dispose();

}

}

 

To Insert Data in Excel 2007 (*.XLSX) OLEDB Jet will not work. For that you will have

to use the new Microsoft Ace Driver. Rest all will be the same as we did for Excel 97-2003 format.

 

Here’s the function

//Add New Row To Excel 2007 File

private Boolean AddExcelRow2007(String strFilePath)

{

    if (!File.Exists(strFilePath)) return false;

    string strExcelConn = “Provider=Microsoft.ACE.OLEDB.12.0;” +

    “Data Source=” + strFilePath + “;Excel 12.0;HDR=YES;”;

    OleDbConnection connExcel = new OleDbConnection(strExcelConn);

    OleDbCommand cmdExcel = new OleDbCommand();

    try

    {

        cmdExcel.Connection = connExcel;

 

        //Check if the Sheet Exists

        connExcel.Open();

        DataTable dtExcelSchema;

        dtExcelSchema =

        connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        connExcel.Close();

        DataRow[] dr =

  dtExcelSchema.Select(“TABLE_NAME = ‘tblData'”);

 

        //if not Create the Sheet

        if (dr == null || dr.Length == 0)

        {

            cmdExcel.CommandText = “CREATE TABLE [tblData]” +

            ” (ID varchar(10), Name varchar(50));”; ;

            connExcel.Open();

            cmdExcel.ExecuteNonQuery();

            connExcel.Close();

        }

 

        //Add New Row to Excel File

        cmdExcel.CommandText = “INSERT INTO [tblData]”

        + “(ID, Name) values (‘1’, ‘MAK’)”;

        connExcel.Open();

        cmdExcel.ExecuteNonQuery();

        connExcel.Close();

        return true;

    }

    catch

    {

        return false;

    }

    finally

    {

        cmdExcel.Dispose();

        connExcel.Dispose();

    }

}

 

To call the function on Button click.

 

protected void Button1_Click(object sender, EventArgs e)

{

    //Excel 97-2003

    AddExcelRow(“C:\\Book1.xls”);

 

    //Excel 2007

    AddExcelRow2007(“C:\\Book1.xlsx”);

   

}

 

Please post your comments.

 

Check Whether UserName Already Exists January 11, 2009

Posted by mudassarkhan in C#, JavaScript.
Tags: , , , , ,
6 comments

Many times I have seen people asking “How to identify whether username Exists” When it is typed

Hence I decided to explain it here

I will start with a TextBox for User Name and a Label which will display the message.

 

<asp:TextBox ID=”txtUserName” runat=”server”

onchange = “CheckUser(‘XMLHTTP.aspx?UserName=’ + this.value)”>

</asp:TextBox>

 

<asp:Label ID=”lblMsg” Text = “UserExists” runat=”server” style =” visibility:hidden “>

</asp:Label>

 

On the OnChange Event of the TextBox the CheckUser JavaScript dfunction will be called which is explained below.

The label by default is invisible.

 

Now I will be using JavaScript to make a AJAX Call to the Server and Check whether the User Name exists.

 

JavaScript Part

 

To do that you will need to create XML Http Request.

 

<script type = “text/javascript”>

var xmlhttp;

function CheckUser(url)

{

 

    xmlhttp=null;

    if (window.XMLHttpRequest)

    {

        // code for all new browsers

        xmlhttp=new XMLHttpRequest();

    }

    else if (window.ActiveXObject)

    {

        // code for IE5 and IE6xmlhttp=

        new ActiveXObject(“Microsoft.XMLHTTP”);

    }

    if (xmlhttp!=null)

    {

        xmlhttp.onreadystatechange=state_Change;

        xmlhttp.open(“GET”,url,true);

        xmlhttp.send(null);

    }

    else

    {

        alert(“Your browser does not support XMLHTTP.”);

    }

 }

 

The Above function creates a XML Http Request and sends it asynchronously to the Server.

 

And when the Reponse is Received the following function will be called.

 

 

function state_Change()

{

 

if (xmlhttp.readyState==4)

{// 4 = “loaded”

 

    if (xmlhttp.status==200)

    {// 200 = OK               

        // …our code here…

      var lblMesg = document.getElementById(“<%=lblMsg.ClientID%>”);

      if(xmlhttp.responseText==“true”)

      {

        lblMesg.style.visibility = “visible”; 

      }

      else

      {

        lblMesg.style.visibility = “hidden”;

      }

    }

    else

    {

      alert(“Problem retrieving XML data”);

    }

}

}

</script>

 

 

CodeBehind Server Side (XMLHttpCSharp.aspx)

 

protected void Page_Load(object sender, EventArgs e)

{

    if (Request.QueryString[“UserName”] != null)

    {

        String strUserName = Request.QueryString[“UserName”];

        //Check userName Here

        String strReturnStatus   = “false”;

        if (CheckUsername(Request.QueryString[“UserName”]) = true)

        {

            strReturnStatus = “true”;

        }

        Response.Clear();

        Response.Write(strReturnStatus);

        Response.End();

    }

}

 

On PageLoad As you can see I am Accepting the UserName in the QueryString and then checking whether username exists using CheckUsername function. If It Exists I return true else false.

 

Depending on the Response Received the state_change function Shows or Hides the label.

 

 

Using Select Stored Procedures ADO.Net January 10, 2009

Posted by mudassarkhan in ADO.Net, C#.
Tags: , , , , ,
4 comments

Here I will discuss how to get data using SQL Stored Procedure in ADO.Net

Here’s the Function

 

First import the following Namespaces

 

using System.Data;

using System.Data.SqlClient;

 

Now define your Connection string in the Web.Config

<connectionStrings>

<add name=conString

 connectionString=DataSource=.\SQLEXPRESS;Database=Northwind;

Integrated Security=true/>

</connectionStrings>

 

 

Here’s the function to call the Select Stored Procedure

public DataTable GetData(SqlCommand cmd)

{

    DataTable dt = new DataTable();

   String strConnString  

   =System.Configuration.ConfigurationManager.ConnectionStrings[“conString”].ConnectionString;

    SqlConnection con = new SqlConnection(strConnString);

    cmd.Connection = con;

    SqlDataAdapter sda = new SqlDataAdapter();

    cmd.CommandType = CommandType.StoredProcedure;

    try

    {

        con.Open();

        sda.SelectCommand = cmd;

        sda.Fill(dt);

    }

    catch

    {

        return dt;

    }

    finally

    {

        con.Close();

        cmd.Dispose();

        sda.Dispose();

    }

    return dt;

}

 

If Stored Procedure does not accept any input parameters. Call the above function in the following way.

 protected void Button1_Click(object sender, EventArgs e)

{

    String strStoreProcName = “sp_GetCustomersByID”;

    SqlCommand cmd = new SqlCommand(strStoreProcName);

    DataTable dt = GetData(cmd);

}

 

If Stored Procedure accepts input parameters, you can add in the following way.

 

protected void Button1_Click(object sender, EventArgs e)

{

    String strStoreProcName = “sp_GetCustomersByID”;

    SqlCommand cmd = new SqlCommand(strStoreProcName);

    cmd.Parameters.AddWithValue(“@CustomerID”, “ALFKI”); 

    DataTable dt = GetData(cmd);

}

 

 

You can bind the Returned DataTable to any Data Bound Controls like GridView, DataGrid, DropDownList and others in the following way

DataTable dt = GetData(cmd);

GridView1.DataSource = dt;

GridView1.DataBind();

 

Please do comment If you want something to be added in the Blog which you want to learn.

 

Accessing XML Files Using ADO.Net January 10, 2009

Posted by mudassarkhan in XML.
Tags: , , , , , ,
5 comments

Here I will discuss regarding Accessing XML files using ADO.Net

Consider an XML File

<CustomerDataSet>

            <CustomerTable>

                        <Name>John</Name>

                        <Age>34</Age>

            </CustomerTable>

            <CustomerTable>

                        <Name>Smith</Name>

                        <Age>26</Age>

            </CustomerTable>

</CustomerDataSet>

 

Here CustomerDataSet contains one Table called CustomerTable which has two Columns

Name

Age

And Currently the Table has 2 records.

 

Now to Read the XML File we will use DataSet ReadXML Property.

Syntax

DataSet ds = new DataSet();

ds.ReadXml(<XML FilePath>) ;

Example

Consider that the file Customers.XML is stored in C:\

DataSet ds = new DataSet();

ds.ReadXml(“C:\\Customers.XML”) ;

 

After this is done you can bind your DataSet to any data bound controls like

GridView, DataGrid, Repeater, DropDownList and many others using their

DataSource Property.

 

Example

DataSet ds = new DataSet();

 

ds.ReadXml(“C:\\Customers.XML”) ;

GridView1.DataSource = ds;

GridView1.DataBind();

 

To Add New Rows To your XML File you can use the DataSet WriteXML Property

Syntax

Syntax

DataSet ds = new DataSet();

ds.WriteXML(<XML FilePath>) ;

 

Example

ds.Tables[0].Rows.Add();

ds.Tables[0].Rows[ds.Tables[0].Rows.Count – 1][“Name”] = “MAK”;

ds.Tables[0].Rows[ds.Tables[0].Rows.Count – 1][“Age”] = “23”;

ds.Tables[0].AcceptChanges();

ds.WriteXml(“C:\\Customers.XML”);

 

To Search for An Existing Record and Update the Record, you will have to Query the DataSet in the following way.

DataRow[] dr = ds.Tables[0].Select(“Name = ‘Smith'”);

if (dr!=null && dr.Length > 0)

{

    dr[0][“Age”] = “25”;

}

ds.Tables[0].AcceptChanges();

 

 

 

 

 

 

To Delete a Particular Record. 

 

DataRow[] dr = ds.Tables[0].Select(“Name = ‘MAK'”);

if (dr!=null && dr.Length > 0)

{

    ds.Tables[0].Rows.Remove(dr[0]); 

}

ds.Tables[0].AcceptChanges();

 

 

Thus all the Database Operations such as Add, Update Delete and Search can be performed on XML Files also using DataSet

Disable Browser Back Button January 10, 2009

Posted by mudassarkhan in JavaScript.
Tags: , , ,
6 comments

Here I will discuss how to prevent user’s from going to previous page using Browser Back button

 

Consider the scenario

User comes to Page A does some work and is Redirected to Page B.

Page A ——————à Page B

 

Now if user clicks Browser Back button he can go back to Page A.

Page B——————à Page A

 

To prevent this you can do this trick by adding this script in the body of Page A

 

<body onload=”if(history.length>0)history.go(+1);”>

 

When you add this in the index of Page A, Page B is cached So even if user clicks browser back button he cannot go back.

 

But even though it works well in IE Firefox does not allow this script to run in events where user interaction is not there. Hence the above technique works only for IE.

Execute SQL Select Query using ADO.Net January 10, 2009

Posted by mudassarkhan in C#.
add a comment

Here I will discuss how to get data using SQL Select Query in ADO.Net

Here’s the Function

 

First import the following Namespaces

 

using System.Data;

using System.Data.SqlClient;

 

Now define your Connection string in the Web.Config

<connectionStrings>

<add name=conString

 connectionString=DataSource=.\SQLEXPRESS;Database=Northwind;Integrated Security=true/>

</connectionStrings>

 

 

Here’s the function

public DataTable GetData(SqlCommand cmd)

{

    DataTable dt = new DataTable();

   String strConnString  

   =System.Configuration.ConfigurationManager.ConnectionStrings[“conString”].ConnectionString;

    SqlConnection con = new SqlConnection(strConnString);

    cmd.Connection = con;

    SqlDataAdapter sda = new SqlDataAdapter();

    cmd.CommandType = CommandType.Text;

    try

    {

        con.Open();

        sda.SelectCommand = cmd;

        sda.Fill(dt);

    }

    catch

    {

        return dt;

    }

    finally

    {

        con.Close();

        cmd.Dispose();

        sda.Dispose();

    }

    return dt;

}

 

 

To call the above function without any parameters

 

protected void Button1_Click(object sender, EventArgs e)

{

    String strQuery = “SELECT * FROM Customers”;

    SqlCommand cmd = new SqlCommand(strQuery);

    DataTable dt = GetData(cmd);

}

 

 

And Now to call the above query using parameters. That I need only those records which have CustomerID as ALFKI. Here I am using Parameterized Queries So that SQL Injections can be avoided.

 

So the function will be called this way.

 

protected void Button1_Click(object sender, EventArgs e)

{

    String strQuery = “SELECT * FROM Customers WHERE CustomerID=@CustomerID”;

    SqlCommand cmd = new SqlCommand(strQuery);

    cmd.Parameters.AddWithValue(“@CustomerID”, “ALFKI”); 

    DataTable dt = GetData(cmd);

}

 

 

You can bind the Returned DataTable to any DataBound Controls like Gridview, DataGrid, DropDownList and others in the following way

DataTable dt = GetData(cmd);

GridView1.DataSource = dt;

GridView1.DataBind();

 

Please do comment If you want something to be added in the Blog which you want to learn.

 

 

Send Email Using C# January 9, 2009

Posted by mudassarkhan in C#.
Tags: , , ,
18 comments

Here I have discussed How to send Emails using C#.

See the function below

public Boolean sendemail(String strTo, string strFrom, string strSubject, string strBody,string strAttachmentPath, bool IsBodyHTML)

    {

        Array arrToArray;

        char[] splitter = { ‘;’ };

        arrToArray = strTo.Split(splitter);

 

        MailMessage mm = new MailMessage();

 

 

        mm.From = new MailAddress(strFrom);

        mm.Subject = strSubject;

        mm.Body = strBody;

        mm.IsBodyHtml = IsBodyHTML;

 

        foreach (string s in arrToArray)

        {

            mm.To.Add(new MailAddress(s));

        }

 

        if (strAttachmentPath != “”)

        {

            //Add Attachment

            Attachment attachFile = new Attachment(strAttachmentPath);

            mm.Attachments.Add(attachFile);

        }

 

        SmtpClient smtp = new SmtpClient();

 

        try

        {

 

            //(4) Send the MailMessage (will use the Web.config settings)

            smtp.Host = “YourSmtpServer”;

            smtp.EnableSsl = “true/false”; //Depending on server SSL Settings

            System.Net.NetworkCredential NetworkCred = new System.Net.NetworkCredential();

            NetworkCred.UserName = “Your Email”;

            NetworkCred.Password = “Your Password”;

            smtp.UseDefaultCredentials = true;

            smtp.Credentials = NetworkCred;

            smtp.Port = 000;//Specify your port No;

 

            smtp.Send(mm);

            mm = null;

            smtp = null;

            return true;

        }

        catch (Exception ex)

        {

            mm = null;

            smtp = null;

            return false;

        }   

} 

 

 

 To call this function

 

protected void Button1_Click(object sender, EventArgs e)

{

//Without Attachment

        sendemail(“recepient@abc.com”, “sender@xyz.com”, “Subject”, “Body”, “”, true);

 

        //With Attachment

        sendemail(“recepient@abc.com”, “sender@xyz.com”, “Subject”, “Body”, “c:\\mypdf.pdf”, true);}

 

 

You can also use threading so that your page does not wait for the email to get send and the users get a quick response and mail is send in background

 

To do that

 

protected void Button1_Click(object sender, EventArgs e)

{

  System.Threading.Thread threadSendMails;

  threadSendMails = new System.Threading.Thread(delegate() {    sendemail(“recepient@abc.com”, “sender@xyz.com”, “Subject”, “Body”, “”, true); });

 threadSendMails.IsBackground = true;

 threadSendMails.Start();

 

 }

 

Also these are the settings for sending email using Gmail

 

Host – smtp.gmail.com

SSL – true

PortNo 465 or just comment portNo

UserName – Gmail Email

Password Gmail Password