Serial No in Gridview

To show serial no in GridView just embed this code within Template Field :

<asp:TemplateField>

  <ItemTemplate>

   <table class="style2">

       <tr>

      <td>

        <%#Container.DataItemIndex+1 %>

     </td>

    </tr>

   </table>

 </ItemTemplate>

</asp:TemplateField>

Confirm Message Box using C# in Web Application

To use windows confirm message box in web form at first add window namespace in your cs file.

using System.Windows;

Then add code given below to your desired place.

 

System.Windows.Forms.DialogResult dia = System.Windows.Forms.DialogResult.Yes;

if (gvOrder.Rows.Count > 0)
{
    dia = System.Windows.Forms.MessageBox.Show("Previous Order Is Not Placed Yet.Do U want to      Discard Order? ", "DELETE?", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Question, System.Windows.Forms.MessageBoxDefaultButton.Button1, System.Windows.Forms.MessageBoxOptions.ServiceNotification);
    if (dia == System.Windows.Forms.DialogResult.Yes)
     {
    //Place Your Code Here

     }

}
else
{
    //Place Your Code Here
}

Put New Line In C#

In C we have a habit  of using "\n" for new line and "\r" for  carriage return.Some operating systems use the value 0x0a ('\n') to mark the end of a line. Some use the value 0x0d ('\r') to mark the end of a line. Some use the sequence 0x0d,0x0a ("\r\n"). "\r\n" is a carriage return followed by a newline. Since the days of teletype machines is mostly gone, \r\n is usually converted to just \n and handled that way. But the notable thing is that Windows  uses NewLine as "\r\n"  whereas  "\n" in Unix.
 In C# you can use "Environment.NewLine" also. Here the code explaining example -:


 Label1.Text = "First Line" + Environment.NewLine + "Next Line";
 TextBox1.Text = "First Line" + Environment.NewLine + "Next Line";
 TextBox2.Text = "First Line\nNext Line";
You have to  change textmode property of textbox from singleline to multiline otherwise you wont be able to see the effect like you cant see this effect in Label.

Error converting data type varchar to bigint

Several times you need to convert a varchar field containing alphanumeric value.Like this one :

select [Order_Code]  From [dbo].[Trans_Order]

Using Substring method we can get numeric value.

select SUBSTRING([Order_Code],3,LEN([Order_Code]))  From [dbo].[Trans_Order]

But by using convert or cast method you encountered with a message "Error converting data type varchar to bigint".

select convert(bigint,SUBSTRING([Order_Code],3,LEN([Order_Code])))  From [dbo].[Trans_Order]

So,the actual way to convert any varchar containing only numeric value,is to use ISNUMERIC() method .
In where clause of select statement just check whether string returned by substring method is numeric or not.If its number only then convert or cast method will do their work comfortably.

Now you can use MAX method to get the max value also.

Get returned value from Stored Procedure

This example shows you how to get returned value from Stored Procedure.First of all create a stored procedure which returns a value.In this example of stored procedure it receives a parameter named "uname".
Then its getting the Id of user from function fn_getUserBD_ID and returns that value.

Create PROCEDURE sp_GetBDID
@uname varchar(25)=null
AS
 declare @dbBD_id bigint
BEGIN
    SET NOCOUNT ON;
    Select @dbBD_id=dbo.fn_getUserBD_ID(@uname)
    return @dbBD_id
END

Now to get the value write this code in code behind
    public void GetBDID()
    {
        string st = Session["BDGen"].ToString();
        SqlCommand cmd = new SqlCommand("sp_GetBDID", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@uname", st);

        // Return value as parameter
        SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
        returnValue.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(returnValue);

        if (con.State == ConnectionState.Closed)
            con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        lblUId.Text = returnValue.Value.ToString();
    }

Case Statement in Sql Server

We can use CASE expression in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and even inside of built-in functions.

A Simple Example :

select case due_amt when 0 then 'Paid' else due_amt end as FeeStatus from dbo.tbl_feeinfo

error code 0x8007045D

Meaning of Error 0x8007045d is "ERROR_IO_DEVICE" i.e. "The request could not be performed because of an I/O device error. There are various cause for this error like :

1.  May be the brand of the disc is incompatible with the burner.
2. May be the disc is defective.Use another one .
3. May be the burner have malfunctioned.

In summary, "error code 0x8007045D" simply means the media is bad.

Get Connection String Information without any help of Database

Today I m going to show you how to get the connection string value without any help of sql server and visual studio.

Step 1-: Create a blank text document, save it as all files and give file name as “a.udl”.

 Step 2-: Now double click it.It will open as given screen shot.

Step 3-: Select database provider in provider tab and click next.

Step 4-: Then in Connection tab provide all necessary details and finally click ok.

 Step 5-: Now open your udl file in text format i.e. notepad where you wil find your Connection String.

Nested Grid Through ADO.NET

Today I am going to show you how to implement the nested grid through ADO.NET. We can get various example of nested grid which are implemented through linq or sqldatasource or objectdatasource.
ScreenShots of Output are given below--:

In aspx put the grid in the given way-:
        <asp:GridView ID="gvParent" runat="server" AutoGenerateColumns="False"
            BackColor="White" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px"
            CellPadding="3" CellSpacing="1" GridLines="None"
            onrowcommand="gvParent_RowCommand">
            <Columns>
                <asp:BoundField DataField="Grp_Id" HeaderText="ID" SortExpression="Grp_Id" />
                <asp:BoundField DataField="GrpNm" HeaderText="Name" SortExpression="GrpNm" />
                <asp:TemplateField HeaderText="Group">
                    <ItemTemplate>
                        <asp:Button ID="btnShowChild" runat="server"
                            CommandArgument='<%# Bind("Grp_Id") %>' CommandName="ShowChild" Text="+" />
                             <asp:Button ID="btnHideChild" runat="server"
                            CommandArgument='<%# Bind("Grp_Id") %>' CommandName="HideChild" Text="-" Visible="false" />
                        <asp:GridView ID="gvChild" runat="server" AutoGenerateColumns="False">
                            <Columns>
                                <asp:BoundField DataField="Acct_Id" HeaderText="AcctId"
                                    SortExpression="Acct_Id" />
                                <asp:BoundField DataField="PartyNm" HeaderText="Party Name"
                                    SortExpression="PartyNm" />
                                <asp:BoundField DataField="TempAdd" HeaderText="Address"
                                    SortExpression="TempAdd" />
                            </Columns>
                        </asp:GridView>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
            <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
            <PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
            <RowStyle BackColor="#DEDFDE" ForeColor="Black" />
            <SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#594B9C" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#33276A" />
        </asp:GridView>



Code Behind File will be like this--:

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DbMandiConnectionString"].ConnectionString);
    string query = null;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindParentGrid();
        }
    }
    public void BindParentGrid()
    {
        query = "SELECT [Grp_Id] ,[GrpNm]  FROM [dbo].[tbl_Group]";
        SqlDataAdapter sdap = new SqlDataAdapter(query, con);
        DataSet ds = new DataSet();
        sdap.Fill(ds);

        gvParent.DataSource = ds;
        gvParent.DataBind();
        ds.Clear();
    }
    protected void gvParent_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName== "ShowChild")
        {
            query = "SELECT *  FROM [dbo].[tbl_AccountMaster] where [Grp_Id]="+e.CommandArgument.ToString()+"";
            SqlDataAdapter sdap = new SqlDataAdapter(query, con);
            DataSet ds = new DataSet();
            sdap.Fill(ds);
            for (int i = 0; i < gvParent.Rows.Count; i++)
            {
                if (e.CommandArgument.ToString() == (gvParent.Rows[i].Cells[0].Text))
                {
                    ((GridView)gvParent.Rows[i].FindControl("gvChild")).DataSource = ds;
                    ((GridView)gvParent.Rows[i].FindControl("gvChild")).DataBind();
                    ((Button)gvParent.Rows[i].FindControl("btnShowChild")).Visible = false;
                    ((Button)gvParent.Rows[i].FindControl("btnHideChild")).Visible = true;
                }
            }
        }
        if (e.CommandName == "HideChild")
        {
          
            for (int i = 0; i < gvParent.Rows.Count; i++)
            {
                if (e.CommandArgument.ToString() == (gvParent.Rows[i].Cells[0].Text))
                {
                    ((GridView)gvParent.Rows[i].FindControl("gvChild")).DataSource = null;
                    ((GridView)gvParent.Rows[i].FindControl("gvChild")).DataBind();
                    ((Button)gvParent.Rows[i].FindControl("btnShowChild")).Visible = true;
                    ((Button)gvParent.Rows[i].FindControl("btnHideChild")).Visible = false;
                }
            }
        }
    }
}

Put new line character in select statement

Several times we need to show the values in new line which is fetched thorugh select statement and put them in a label or textbox. Then there is a function CHAR() in sql server through which we can get our desired result.

Control character	Value
Tab	char(9)
Line feed	char(10)
Carriage return	char(13)

Example-:

select profile + CHAR(13)+CHAR(10) + address +CHAR(13)+CHAR(10)+phone, export_ref from tbl_profile

Output will be like that-: