.: January 2012

Sunday, January 8, 2012

Get Stored Procedure Return Value

SqlConnection con = null;
        try
        {
            string connString = ConfigurationManager.ConnectionStrings["cnTest"].ConnectionString;
            con = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_ValidateUser";
            cmd.Parameters.AddWithValue("@Name", txt1.Text);
            con.Open();
            // Return value as parameter
            SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(returnValue);

            // Execute the stored procedure

            cmd.ExecuteNonQuery();
            con.Close();

            Int32 ret= Convert.ToInt32(returnValue.Value);
            Response.Write(Convert.ToString(ret));

        }
        catch (SqlException ex)
        {
            // handle error
        }
        catch (Exception ex)
        {
            // handle error
        }
        finally
        {
            con.Close();
        }

Create Proc usp_ValidateUser
@Name Varchar(50)
AS
BEGIN
        if exists(Select Name from tblTestUser where Name=@Name)
        Begin
                return 1
        End

        else
         Begin
                return -1
        End


END

Get selected index, value,text of ASP.Net drop down list in javascript

<%-- In Head section--%>
<script type="text/javascript" language="javascript">

            function getDropdownListValues() {
            var DropdownList = document.getElementById('<%=aspdropdown.ClientID %>');
            var SelectedIndex = DropdownList.selectedIndex;
            var SelectedValue = DropdownList.value;
            var SelectedText = DropdownList.options[DropdownList.selectedIndex].text;

            var LabelDropdownList = document.getElementById('<%=lblDropdownList.ClientID %>');
            var sValue = 'Index: ' + SelectedIndex + '<br/> Selected Value: ' + SelectedValue + '<br/> Selected Text: ' + SelectedText;

            LabelDropdownList.innerHTML = sValue;
        }
    </script>

<%-- In body section--%>

<div>
        <asp:Label runat="server" ID="lblText">Asp.net DropdownList</asp:Label><br />

        <asp:DropDownList ID="aspdropdown" runat="server" onchange="getDropdownListValues();">
            <asp:ListItem Value="Test1" Text="Testing1"></asp:ListItem>
            <asp:ListItem Value="Test2" Text="Testing2"></asp:ListItem>
            <asp:ListItem Value="Test3" Text="Testing3"></asp:ListItem>
            <asp:ListItem Value="Test4" Text="Testing4"></asp:ListItem>
            <asp:ListItem Value="Test5" Text="Testing5"></asp:ListItem>
        </asp:DropDownList> <br />

        <asp:Label runat="server" ID="lblDropdownList"></asp:Label>
    </div>

Friday, January 6, 2012

HTML To PDF Converter Asp.net

using Pdfizer; //download Pdfizer and itextsharp dll also
using System.IO;

protected void btnDownload_Click1(object sender, EventArgs e)
    {
        string sPathToWritePdfTo = Server.MapPath(".") + "\\test.pdf";
        string path = Server.MapPath("./images/logo3w.png");
        System.Text.StringBuilder sbHtml = new System.Text.StringBuilder();
        sbHtml.Append("<html>");
        sbHtml.Append("<head>");
        sbHtml.Append("</head>");
        sbHtml.Append("<body>");
        sbHtml.Append("<div>");
        sbHtml.Append("<img src='" + path + "' alt='' />");
        sbHtml.Append("</div>");
        sbHtml.Append("<br/>");
        sbHtml.Append("<table border='0' cellpadding='0' cellspacing='0'>");
        sbHtml.Append("<tr><td>testing the text</td></tr>");
        sbHtml.Append("</table>");
        sbHtml.Append("</body>");
        sbHtml.Append("</html>");

        using (System.IO.Stream stream = new System.IO.FileStream
        (sPathToWritePdfTo, System.IO.FileMode.OpenOrCreate))
        {
            Pdfizer.HtmlToPdfConverter htmlToPdf = new Pdfizer.HtmlToPdfConverter();
            htmlToPdf.Open(stream);
            htmlToPdf.Run(sbHtml.ToString());
            htmlToPdf.Close();
        }
        System.Web.HttpContext.Current.Response.Clear();
        System.Web.HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "devtesting.pdf"));
        System.Web.HttpContext.Current.Response.ContentType = "application/pdf";
        System.Web.HttpContext.Current.Response.WriteFile(sPathToWritePdfTo);
        System.Web.HttpContext.Current.Response.End();
    }

/*http://itextpdf.com/terms-of-use*/

Wednesday, January 4, 2012

Export To CSV using ASP

<%
sub Write_CSV_From_Recordset( RS )

    '
    ' This sub-routine Response.Writes the content of an ADODB.RECORDSET in CSV format
    ' The function closely follows the recommendations described in RFC 4180:
    ' Common Format and MIME Type for Comma-Separated Values (CSV) Files
    ' http://tools.ietf.org/html/rfc4180
    '
    ' @RS: A reference to an open ADODB.RECORDSET object
    '

    if RS.EOF then

      '
      ' There is no data to be written
      '
      exit sub

    end if

    dim RX
    set RX = new RegExp
        RX.Pattern = "\r|\n|,|"""

    dim i
    dim Field
    dim Separator

    '
    ' Writing the header row (header row contains field names)
    '

    Separator = ""
    for i = 0 to RS.Fields.Count - 1
      Field = RS.Fields( i ).Name
      if RX.Test( Field ) then
        '
        ' According to recommendations:
        ' - Fields that contain CR/LF, Comma or Double-quote should be enclosed in double-quotes
        ' - Double-quote itself must be escaped by preceeding with another double-quote
        '
        Field = """" & Replace( Field, """", """""" ) & """"
      end if
      Response.Write Separator & Field
      Separator = ","
    next
    Response.Write vbNewLine

    '
    ' Writing the data rows
    '

    do until RS.EOF
      Separator = ""
      for i = 0 to RS.Fields.Count - 1
        '
        ' Note the concatenation with empty string below
        ' This assures that NULL values are converted to empty string
        '
        Field = RS.Fields( i ).Value & ""
        if RX.Test( Field ) then
          Field = """" & Replace( Field, """", """""" ) & """"
        end if
        Response.Write Separator & Field
        Separator = ","
      next
      Response.Write vbNewLine
      RS.MoveNext
    loop

end sub

'
' EXAMPLE USAGE
'
' - Open a RECORDSET object (forward-only, read-only recommended)
' - Send appropriate response headers
' - Call the function
'
    Set oConnection = Server.CreateObject("ADODB.Connection")
    oConnection.Open "Driver={SQL Server};Server=test123;Database=Test123;Uid=testsa;Pwd=test@123;"

    Set SQLStmt = Server.CreateObject("ADODB.Command")
    set rs=Server.CreateObject("ADODB.recordset")
    rs.Open "Select Id,Name as UserName from tblTestUser", oConnection

    dim RS1
    set RS1 = Server.CreateObject( "ADODB.RECORDSET" )
    RS1.Open "Select Id,Name as UserName from tblTestUser",oConnection
    Response.ContentType = "text/csv"
    Response.AddHeader "Content-Disposition", "attachment;filename=export.csv"
    Write_CSV_From_Recordset RS1
%>