How to get duplicate values from table in MS SQL Server

Here are some of the t-Sql queries you can use to find duplicate values

1. Using group by and having

select CabTransactions.TransactionId,y.cabid,y.orderid from
(
select min(cabid) as cabid,orderid,action from CabTransactions
group by cabid,orderid,action
having count(transactionid)>1
) as y,CabTransactions where y.cabid=CabTransactions.CabId and y.OrderId=CabTransactions.OrderId

2. Using not in

select * from cabtransactions where transactionid not in
(
    select min(transactionid) from cabtransactions group by cabid,orderid
)

Thanks
Ritesh

How to send POST parameters from jquery $.ajax to Web API ASP.NET MVC

Hi Guys,

I was facing a strange problem while sending data from jquery $.ajax to ASP.NET MVC Web API. I had earlier made lot of web appliactions using $.ajax with SOAP based (asmx),WCF (svc) files. But this time i struggled to post data into the web api's action.

I was having an Action like

 // POST api/values
        public string Post([FromBody]string value)
        {
            return value;
        }

When i tried this

$.ajax({
                 type: "POST",
                 dataType: "json",
                 contentType: 'application/json;charset=utf-8',
                 url: "/api/values",
                 data: {'value':'Test'},
                 success: function (data) {
                     alert(data);
                 },
                 error: function (error) {
                     jsonValue = jQuery.parseJSON(error.responseText);
                 }
             });

The value passed into "value" parameter of post was "null".

This is due to the following

1. Since web api requires the data to be in the format "=yourvalue". The existing $.ajax was not passing the data in post action.
2. The content type in $.ajax here is application/json, Where as the format as stated above is not a json format.

I successfully passed the post parameter by using the following

        $.ajax({
                 type: "POST",
                 dataType: "json",
                 url: "/api/values",
                 data: '=' + 'Test',
                 success: function (data) {
                     alert(data);
                 },
                 error: function (error) {
                     jsonValue = jQuery.parseJSON(error.responseText);
                 }
             });

As you can see i have not included "contentType: 'application/json;charset=utf-8'" in $.ajax. Also i have passed the data in the

format "=yourvalue" i.e data:'='+'Test', This passed the value "Test" in my post parameter i.e "value" in this case.

For complex types you use JSON.stringify i.e you can pass like

var complexdata= { 'value': 'Test','id':'350','name':'Ritesh' };

$.ajax({
                 type: "POST",
                 dataType: "json",
                 contentType: 'application/json;charset=utf-8',
                 url: "/api/values",
                 data: JSON.stringify(complexdata),
                 success: function (data) {
                     alert(data);
                 },
                 error: function (error) {
                     jsonValue = jQuery.parseJSON(error.responseText);
                 }
             });

For PUT in WEB API with $.ajax you can use like

var obj = {'Id':'3','EmpCode':'E001','EmpName':'Ritesh'};

             $.ajax({
                 type: "PUT",
                 dataType: "json",
                 contentType: 'application/json;charset=utf-8',
                 url: "/api/values/8",
                 data: JSON.stringify(obj),
                 success: function (data)
                 {
                     alert(data);
                 },
                 error: function (error) {
                     jsonValue = jQuery.parseJSON(error.responseText);
                 }
             });

Where your put method looks like

        // PUT api/values/5
        //To update an existing record
        public void Put(int id, [FromBody]Employee value)
        {

        }

Thanks
Ritesh Tandon

How to run SQL server job based on linked server T-SQL

Hi Guys,

I was facing problem while running a job based on the linked server. I was having two sql servers and was implementing the concept of distributed databases.

I created a stored procedures which takes the data from my server database to remote server database. I wanted to create a job for this so that after every 1 hour my database is synchronized.

I did the following -:

Check which user is associated with the SQL server agent service, usually it is

NT Service\SQLSERVERAGENT

Now Create a new job

1. Add mapping user as
NT Service\SQLSERVERAGENT and for remote server give the user name and password.

2. Make sure the owner user is the same user who is having
administrator rights.

3. Do not add any user in the run as user textbox.

4. Add your step and the T-SQL command.

5. Execute the Job

That's it folks

Thanks
Ritesh

Update Columns Values From Another Table Column Values in MS SQL

Hi Guys,

Today i am going to tell you how to update column values from another table column values.


Lets Suppose I have two tables with the following structures. Now i want to update ItemTypeMaster table, the column supercategoryid is to be updated with values existing in the ItemCategoryMaster table's supercategory id column values. The Item_Cat_Id is Primary Key and CategoryId column in ItemTypeMaster table is Foreign Key.

ItemCategoryMaster Table
Item_Cat_Id    int,
Item_Cat_Desc    varchar(500),
Gender    varchar(1),
IsActive    bit,
SuperCategoryId    int,
Item_Cat_Picture    varchar(500)

ItemTypeMaster Table
ItemTypeId    int,
TypeName    varchar(50),
TypeDesc    varchar(500),
CategoryId    int,
SubCategoryId    int,
SuperCategoryId    int,
Gender    varchar(50),
ItemTypePicture    varchar(500),
IsActive    bit

Here is how to achieve the task.I was struggling to update my table column value based on a column value in another table. However i was surprised how the MS SQL update statements has got power of joins. We can use the joins in update query to update our column values.

The command i used to update the values is

update A set supercategoryid = B.supercategoryid
From ItemTypeMaster A
Inner Join itemcategorymaster B
On A.categoryid = B.item_cat_id

Similary, You can use select command to update the column values. For ex. I want to update IsActive column values based on the min id, The query would be:

update itemtypemaster set isactive=1 where itemtypeid in
(
select min(itemtypeid) from itemtypemaster
group by gender,supercategoryid,categoryid,subcategoryid,typename
)

Similarly, You can delete duplicate records from the table by using:

delete from itemtypemaster where itemtypeid not in
(
select min(itemtypeid) from itemtypemaster
group by gender,supercategoryid,categoryid,subcategoryid,typename
)

Hope this help guys!!

Thanks
Ritesh

MVC 4 Form Validations With LINQ

Hi Guys,

Today i am going to tell you how to add validations on

a MVC form when LINQ is used.

The idea is to use MetadataType attribute and create a

partial class with the same name used in the LINQ

generated class.


Step 1. Create a table in the MS Sql database, with name Employee. Add columns
a. EmpId
b. EmpCode
c. EmpName

Step 2. Create Linq to Sql classes for the database with project name FirstAppDll.

Step 3. Create a model class for the employee

    [MetadataType(typeof(FirstAppDll.Employee))]
    public partial class Employee
    {
        [Required]
        public int EmpId { get; set; }

        [Required(ErrorMessage="Please Enter Employee Code")]
        [Range(3,12,ErrorMessage="Please Enter Employee Code Between 3 to 12 Characters")]
        public string EmpCode { get; set; }

        [Required(ErrorMessage="Please Enter Employee Name")]
        [StringLength(50,ErrorMessage="Employee Name Can Be Less Than 50 Characters")]
        public string EmpName { get; set; }
    }

Step 4. Your controller class should look like

// GET:/Employee/Create (For Blank Create Form)
        public ActionResult Create()
        {
            Employee emp = new Employee();
            return View(emp);
        }

        // POST:/Employee/Create (For saving the saved object from the form into the object)
        [HttpPost]
        public ActionResult Create(Employee obj)
        {
            if (ModelState.IsValid)
            {
                using (FirstAppDll.EmployeeLibraryDataContext context = new FirstAppDll.EmployeeLibraryDataContext())
                {
                    FirstAppDll.Employee emp = new FirstAppDll.Employee();
                    emp.EmpCode = obj.EmpCode;
                    emp.EmpName = obj.EmpName;
                    context.Employees.InsertOnSubmit(emp);
                    context.SubmitChanges();
                    return Redirect("/Employee/Create");
                }
            }
            return View(obj);
        }

Step 5. Generate the view by using the scaffolding for the partial class created. Generate the "Create" scaffold for the class Employee in the mvc project.


That's it folks, Check the form by typing localhost:yourport/Employee/Create and the validations are ready for you.

Thanks
Ritesh