Convert Number to Word in Crystal Report

How to write amount in word in crystal report.

Today we are  discussing about to convert amount in word. i mean 12,082.00 in to "TWELVE THOUSAND EIGHTY-TWO ONLY" in Crystal report.

if

right(Totext({@total}),2)="00" then

uppercase(ToWords({@total},0))+" "+"Only"

else

uppercase(ToWords({@total},0))+" AND 

"+uppercase(ToWords(tonumber(right(Totext({@total}),2)),0))+" "+"Paisa 

Only"

Read More

Create a Job in Sql Server

How to Create a job(Schedule) in Sql Server

USE [msdb]

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'JOB NAME',

                   @enabled=1,

                   @notify_level_eventlog=0,

                   @notify_level_email=0,

                   @notify_level_netsend=0,

                   @notify_level_page=0,

                   @delete_level=0,

                   @description=N'No description available.',

                   @category_name=N'[Uncategorized (Local)]',

                   @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'STEP NAME',

                   @step_id=1,

                   @cmdexec_success_code=0,

                   @on_success_action=1,

                   @on_success_step_id=0,

                   @on_fail_action=2,

                   @on_fail_step_id=0,

                   @retry_attempts=0,

                   @retry_interval=0,

                   @os_run_priority=0, @subsystem=N'TSQL',

                   @command=N'STORED PROCEDURE NAME',

                   @database_name=N'DATABASE NAME',

                   @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily',

                   @enabled=1,

                   @freq_type=8,

                   @freq_interval=40,

                   @freq_subday_type=2,

                   @freq_subday_interval=10,

                   @freq_relative_interval=0,

                   @freq_recurrence_factor=1,

                   @active_start_date=20140106,

                   @active_end_date=99991231,

                   @active_start_time=0,

                   @active_end_time=235959,

                   @schedule_uid=N'9f8316ca-3d7a-42f4-83f3-93e6b1037c90'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Read More

Update table with join

Update  table with join.

We have two table with name s1 and s2

In first table s1 we have add1 column with null value.

We have another table s2 that contains loc column . Now we need a sql query to update table s1 add1 column with second s2 loc column.

Table name s1

id      Name                add1

1      Rahul                NULL

2      Ramu                NULL

3      Sachi                NULL

4      Sakshi              NULL

5      Aki                   NULL

Table name s2

Id     loc

1      Mahipalpur

2      Bettiah

3      Banglore

4      Gurgaon

5      Noida

The Sql Query for update with join is given bellow:-

update s1 set add1=st2.loc from s1 st1 join s2 st2 on st1.id=st2.id

id              Name                        add1

1              Rahul                        Mahipalpur

2              Ramu                        Bettiah

3              Sachi                        Banglore

4              Sakshi                      Gurgaon

5              Aki                           Noida

Read More

Crud operation in single page in mvc using Json

Today we discuss about the crud operation  in single page in  mvc 4

First we create model using linq to entity (using edmx file).

                                                                    List of Users

User Creation

User Deletion

                                                                       

 User Modification

User Details

Code for Controller:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using mvcwithjson.Models;

namespace mvcwithjson.Controllers

{

    public class popupmodelController : Controller

    {

        dbMVCEntities en = new dbMVCEntities();

        public ActionResult Index()

        {

           

            return View(en.student_info.ToList ());

        }

        public JsonResult display(string id)

        {

            int srno = Convert.ToInt32(id);

            var v = en.student_info.Where(m => m.id == srno).Select(m => new { ID = m.id, Name = m.name, age = m.age }).FirstOrDefault();

            return Json(v, JsonRequestBehavior.AllowGet);

        }

        [HttpPost]

        public string delete(string ID)

        {

            int id = int.Parse(ID);

            var v = en.student_info.FirstOrDefault(m => m.id == id);

            if (v != null)

            {

                en.student_info.Remove(v);

                en.SaveChanges();

            }

            return "Record deleted successfully";

        }

        [HttpPost]

        public string create( string name, int age)

        {

            try

            {

                student_info r = new student_info()

                {

                  

                    name = name,

                    age = age

                };

                en.student_info.Add(r);

                en.SaveChanges();

                return "Data saved successfully";

            }

            catch

            {

                return "1";

            }

        }

        [HttpPost]

        public string update(int id, string name, int age)

        {

            try

            {

                student_info st = en.student_info.FirstOrDefault(m => m.id == id);

                if (st != null)

                {

                    st.name = name;

                    st.age = age;

                    en.SaveChanges();

                }

                return "Data updated successfully";

            }

            catch

            {

               

                return "1";

            }

        }

        

    }

}

Code for View:-

@model IEnumerable<mvcwithjson.Models.student_info>

@{

    Layout = null;

}

<!DOCTYPE html>

<html>

<head>

    <meta name="viewport" content="width=device-width" />

    <title>Index</title>

    <script src="~/Scripts/jquery-1.7.1.js"></script>

    <script src="~/Scripts/JavaScript1.js"></script>

    <script type="text/javascript">

        $(function () {

            //                                      code for create start

            // **************************************************************

            $("#create").live("click", function (e) {

                e.preventDefault();

                $("#mask").fadeIn("slow");

                $("#c1").fadeIn("slow");

            });

            $("#close").hover(function () {

                $(this).css("cursor", "pointer");

            });

            $("#close").click(function () {

                $("#mask").fadeOut("slow");

                $("#c1").fadeOut("slow");

            });

            $("#save").click(function () {

                $.post("popupmodel/create", {

                    userid: $("#userid").val(),

                    name: $("#name").val(),

                    age: $("#age").val()

                }, function (data) {

                    if (data == "1")

                        alert("Userid alredy exsists");

                    else {

                        $("#b").load("popupmodel/display");

                        $("#mask").fadeOut("slow");

                        $("#c1").fadeOut("slow");

                    }

                    location.reload(true);

                });

            });

            //                                       code for create end

            //************************************************************

            //***********************************************************          //                                 Started  code for details part

            $("#detailclose").hover(function () {

                $(this).css("cursor", "pointer");

            });

            $("#detailclose").click(function () {

                $("#mask").fadeOut("slow");

                $("#cdetails").fadeOut("slow");

            });

            $(".clsDetails").live("click", function (e) {

                e.preventDefault();

                $("#mask").fadeIn("slow");

                $("#cdetails").fadeIn("slow");

                var v = $(this).attr("id");

                $.getJSON("popupmodel/display", { id: v }, function (data) {

                    if (data != null) {

                        $("#detailuserid").html(data.ID);

                        $("#detailname").html(data.Name);

                        $("#detailage").html(data.age);

                    }

                });

            });

            //                         END     code for details part

            //***********************************************************

            //                                         started  code for edit

            $("#editclose").hover(function () {

                $(this).css("cursor", "pointer");

            });

            $("#editclose").click(function () {

                $("#mask").fadeOut("slow");

                $("#cedit").fadeOut("slow");

            });

            $(".clsedit").live("click", function (e) {

                e.preventDefault();

                $("#mask").fadeIn("slow");

                $("#cedit").fadeIn("slow");

                var v = $(this).attr("id");

                var vid = v.substring(4);

                $.getJSON("popupmodel/display", { id: vid }, function (data) {

                    if (data != null) {

                        $("#hdn").val(data.ID);

                        $("#editname").val(data.Name);

                        $("#editage").val(data.age);

                    }

                });

            });

            $("#Update").click(function () {

                var id = $("#hdn").val();

                var name = $("#editname").val();

                var age = $("#editage").val();

                $.post("popupmodel/update", { id: id, name: name, age: age }, function (data) {

                    if (data != null) {

                        location.reload(true);

                    }

                    $("#mask").fadeOut("slow");

                    $("#cedit").fadeOut("slow");

                });

            });

            //                                        End code for edit

            //**********************************************************

            //                                  start  code for delete

            //*******************************************************

            $(".clsdelete").live("click", function (p) {

                p.preventDefault();

                $("#mask").fadeIn("slow");

                $("#cdelete").fadeIn("slow");

                var v = $(this).attr("id");

                var vid = v.substring(3);

                $.getJSON("popupmodel/display", { id: vid }, function (data) {

                    if (data != null) {

                        $("#hdndel").val(vid);

                        $("#delname").html(data.Name);

                        $("#delage").html(data.age);

                    }

                });

            });

            $("#deleteclose").hover(function () {

                $(this).css("cursor", "pointer");

            });

            $("#deleteclose").click(function () {

                $("#mask").fadeOut("slow");

                $("#cdelete").fadeOut("slow");

            });

            $("#btndel").click(function () {

                var id = $("#hdndel").val();

                $.post("popupmodel/delete", { id: id }, function (data) {

                    if (data != null) {

                        location.reload(true);

                    }

                    $("#mask").fadeOut("slow");

                    $("#cedit").fadeOut("slow");

                });

            });

            //                                  End code for delete

            //***********************************************************

        });

    </script>

</head>

<body>

    <div id="mask" style="height: 100%; width: 100%; background-color: gray; opacity: 0.5; z-index: 1000; display: none; position: fixed; top: 0px; left: 0px;"></div>

  

                            @* Start Design for Create Registration

            ********************************************************@

   

     <div id="c1" style="height: 50%; width: 30%; display: none; background-color: white; border: 1px solid; z-index: 5000; position: fixed; top: 20%; left: 35%;">

        <table style="width: 100%; margin-left: auto; margin-right: auto;">

            <tr>

                <td style="text-align: right;" colspan="2">

                    <span id="close" style="position: relative; top: 25%; text-align: right;">

                        @*Close*@

                        <img src="~/img/close.png" alt="" height="30" width="30" />

                    </span>

                </td>

            </tr>

        </table>

        <table style="width: 80%; margin-left: auto; margin-right: auto;">

            <tr>

                <td colspan="2" style="text-align: center">

                    <h3>Registration Form</h3>

                </td>

            </tr>

            <tr>

                <td>User id</td>

                <td>@Html.TextBox("userid")</td>

            </tr>

            <tr>

                <td>Name</td>

                <td>@Html.TextBox("name")</td>

            </tr>

            <tr>

                <td>Age</td>

                <td>@Html.TextBox("age")</td>

            </tr>

            <tr>

                <td></td>

                <td>

                    <input id="save" type="button" value="Save" /></td>

            </tr>

        </table>

    </div>

   

                                     @* End Design for Create Registration

            *********************************************************@

     @*                             Start Design for View Registration Details

            ******************************************************@

    <div id="cdetails" style="height: 28%; width: 30%; display: none; background-color: white; border: 1px solid; z-index: 8000; position: fixed; top: 20%; left:35%;">

        <table style="width: 100%; margin-left: auto; margin-right: auto;">

            <tr>

                <td style="text-align: right;" colspan="2">

                    <span id="detailclose" style="position: relative; top: 25%; text-align: right;">

                        @*Close*@

                        <img src="~/img/close.png" alt="" height="30" width="30" />

                    </span>

                </td>

            </tr>

        </table>

        <table style="width: 70%; margin-left: auto; margin-right: auto;">

            <tr>

                <td colspan="2" style="text-align: center">

                    <h3>Registered User</h3>

                </td>

            </tr>

            <tr>

                <td>User id</td>

                <td><span id="detailuserid"></span></td>

            </tr>

            <tr>

                <td>Name</td>

                <td><span id="detailname"></span></td>

            </tr>

            <tr>

                <td>Age</td>

                <td><span id="detailage"></span></td>

            </tr>

            <tr>

                <td>@*<input id="Save" type="button" value="Save" />*@</td>

                <td></td>

            </tr>

        </table>

    </div>

     @*                             End Design for View Registration Details

            **********************************************************@

  

   

      @*                             Start Design for Edit Registration Details

            ********************************************************@

    <div id="cedit" style="height: 30%; width: 40%; display: none; background-color: white; border: 1px solid; z-index: 85000; position: fixed; top: 20%; left: 35%;">

         <table style="width: 100%; margin-left: auto; margin-right: auto; margin-top:0;">

           <tr>

               <td style="text-align:center;">

                     <h3>Registration Form</h3>

               </td>

               <td  style="text-align:right;margin-top:0; ">

                      <span id="editclose" style="position: relative ; text-align:right;">

                        <img src="~/img/close.png" alt="" height="30" width="30" />

                    </span></td>

            </tr>

        </table>

         <table style="width: 70%; margin-left:auto;margin-right:auto;">

           

            <tr>

                <td></td>

                <td>@Html.Hidden("hdn")

                </td>

            </tr>

            <tr>

                <td>Name</td>

                <td>@Html.TextBox("editname")</td>

            </tr>

            <tr>

                <td>Age</td>

                <td>@Html.TextBox("editage")</td>

            </tr>

            <tr>

                <td>

                    </td>

                <td><input id="Update" type="button" value="Save" /></td>

            </tr>

        </table>

    </div>

     @*                             End Design for Edit Registration Details

            *****************************************************@

     @*                             Start Design for Delete Registration Details

            *******************************************************@

    <div id="cdelete" style="height: 30%; width: 30%; display: none; background-color: white; border: 1px solid; z-index: 88000; position: fixed; top: 20%; left: 35%;">

      

         <table style="width: 100%; margin-left: auto; margin-right: auto; margin-top:0;">

          <tr>

               

              <td style="text-align:center;">

                   <h3>Are you sure to delete?</h3>

              </td><td  style="text-align: right;">

                   

                    <span id="deleteclose" style="position: relative; right: 0; top: 0%;">

                        <img src="~/img/close.png" alt="" height="30" width="30" />

                    </span></td>

            </tr>

        </table>

       

       

         <table style="width: 60%; margin-left:auto;margin-right:auto;">

           

            <tr>

                <td></td>

                <td>@Html.Hidden("hdndel")

                </td>

            </tr>

            <tr>

                <td>Name</td>

                <td><span id="delname"></span></td>

            </tr>

            <tr>

                <td>Age</td>

                <td><span id="delage"></span></td>

            </tr>

            <tr>

                <td>

                    </td>

                <td><input id="btndel" type="button" value="Delete" /></td>

            </tr>

        </table>

    </div>

     @*                             End Design for Delete Registration Details

            *******************************************************@

   

     @*                             Start Design for show the list of  Registered Users

            ********************************************************@

   

    <div style="width:400px; margin-left:auto; margin-right:auto;">

   

    <p>

        <a id="create" href="#">Create</a>

    </p>

    <div id="b">

        <table style="width:100%; text-align:left;">

            <tr>

                <th>

                    @Html.DisplayNameFor(model => model.name)

                </th>

                <th>

                    @Html.DisplayNameFor(model => model.age)

                </th>

                <th></th>

            </tr>

            @foreach (var item in Model)

            {

                <tr>

                    <td>

                        @Html.DisplayFor(modelItem => item.name)

                    </td>

                    <td>

                        @Html.DisplayFor(modelItem => item.age)

                    </td>

                    <td>

                        @Html.ActionLink("Edit", "display", new { id = item.id }, new { id = "edit" + @item.id, @class = "clsedit" }) |

                        @Html.ActionLink("Details", "display", new { id = item.id }, new { id = item.id, @class = "clsDetails" }) |

                        @Html.ActionLink("Delete", "display", new { id = item.id }, new { id = "del" + @item.id, @class = "clsdelete" })

                    </td>

                </tr>

            }

        </table>

    </div>

        </div>

     @*                             End Design for show the list of  Registered Users

            ************************************************************@

</body>

</html>

Read More