TRY_CAST at Microsoft SQL Server 2012

For converting any values from one type to another we can use CAST function at Microsoft SQL Server. But when it fail to convert the values to specified type, it returns error. That’s why before use this CAST function, we have to confirm first that the column we are going to convert via CAST function have the correct data to convert.

But Microsoft SQL Server 2012 introduce a new function called TRY_CAST, which will cast to specified type as like CAST function. But there is a difference between TRY_CAST and CAST function. TRY_CAST will never fail to execute the query, because the values which couldn’t be converted to specified types will return the value as NULL.

Lets try both the function and see the differences.

Select CAST('A0001' as int)    

Select TRY_CAST('A0001' as int)

First query will return error. But second one will run successfully and return NULL as it fails to convert the values A0001 to int.

Comments

comments

Powered by Facebook Comments

Disable windows authentication login at MS SQL SERVER 2008

to disable windows authentication login at MS SQL SERVER 2008, follow the following steps:

1. Open Microsoft Sql Server Management Studio. Login as windows authentication or SQL Server authentication.

2.Now from left side Object Explorer, expand the security , then Logins. You will get the windows user list. user list will be like : computer name \ User Name.

Step1

3. Right click on user and click on Properties. A window will appear like below image.

Step2

Now click on disabled option and click OK. Then close the Microsoft SQL server management studio and start once again.

Now try to login via windows authentication and check what will happen.

Comments

comments

Powered by Facebook Comments

Generate script from Microsoft SQL Server Management Studio

From Microsoft SQL Server Management Studio right click on database,  a pop up menu will appear.  From popup menu select tasks > Generate Scripts. A new dialog box called Generate and Publish Scripts window will appear. This window will look like below picture.

GenerateScript_1

From this window click on next button. Choose Objects option will appear like below picture.

GenerateScript_2

Now you can either generate script for all database objects or specific object. To do this just select one option.  If you select “Select specific database objects” then you have to select the objects from list.

Now click on next button. Set scripting options will appear like below picture.

GenerateScript_3

We will save the script to a specific location, that’s way select first option. choose the file name. Now click on Advanced button. A new window will appear like below picture.

GenerateScript_4

select “schema and data” option of Types of data to script. This will generate the schema as well data. Click on OK button. Click on next button and review your selection. Then once again click on next button and it will start to generate the script. After generate script just click on Finish button.

Job Done.

Comments

comments

Powered by Facebook Comments

When we need junction table for one to many relationship?

For one to many relationships we don’t need to use junction table in relational database. As logic says, we can easily make one to many relationship between two tables via primary key and foreign key. Then why junction table for one to many relationship? Obviously there have some reason why we will use junction table to keep us as well as database safe for near future.

I will explain about it with an example. Mr. Scott is an database developer. His boss gives him a document and tells him to design ER diagram of the system. Mr. Scott starts to design the ER diagram. Somewhere in documentation he found the below lines:

“Till now each department is supervised by one employee. If management approve then may be near future more than one employee can supervised more than one department.”

Mr. Scott thinks that, as per current situation the relationship between Department and Employee will be one to many relationship. But near future it could be many to many relationship. Mr. Scott takes the current situation and designs the ER diagram of Department and Employee table by following ways.

OneToMany

Mr. Scott prepares the ER diagram and sends this to his boss. The system is developed by following the ER diagram. System is working smoothly. But problem arise after one year later, when a new decision is taken by customer and that is –

“From now more than one employee can run more than one department.”

Mr. Scott now changes the ER diagram and creates a junction table between Department and Employee. When database & frontend team got the change details, they informed that they have to change the system overall. And it takes a long time to change the system.

But question is, did Mr. Scott do the right things initially?

From my point of view it was wrong. He should design the system initially by following ways

OneToMany2

By creating a junction table called DepartmentEmployee, he could easily develop the ER diagram as per requirment. Keep in mind that at DepartmentEmployee table, DepartmentId should be primary key, then more then one department cannot be insert into Department table.

After that when new decision / changes comes after one year, then he just need to change few things at DepartmentEmployee table. First remove the primary key from DepartmentEmployee table and create a new composite key with DepartmentId and EmployeeId. New changes will be like below

OneToMany3

Now think with this small changes Mr. Scott’s company can saves the time and cost of the project from any aspect.

Comments

comments

Powered by Facebook Comments

Another reason for getting the error “A network-related or instance-specific error occurred while establishing a connection to SQL Server…” in MS SQL Server 2008

Normally the reason behind getting the error – “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)” in MS SQL server 2008 is for not to enabling the TCP/IP protocol of MSSQLServer or SQLExpress.

But today when I tried to run the software which database stored in another PC, couldn’t connect with the database at all. Then I checked the TCP/IP status is enabled or not from SQL Server configuration manager and I found that TCP/IP is already enabled for both the MSSQLServer and SQLExpress.

Then after two hours doing so many things, I got the solution.

When I installed MS SQL server in server machine in that time there was no IP assigned in that server. But few days ago I assigned an IP on that server to include this machine into domain. Then I thought that may be this could be a problem and I did what I thought and It works. Now I will tell you how I solved it.

Solution:

Step 1: Open SQL Server configuration manager from start > All Programs > Microsoft SQL Server 2008 > Configuration Tools.

Step 2: From SQL Server configuration manager window select Protocols for SQLExpress/Protocols for MSSQLSERVER (Which one you need) from left side tree menu. Then you will see all the protocols available in right side.

Step 3: From the available protocol right click on TCP/IP and click on properties. Then TCP/IP properties window will appear. Click on IP Addresses tab. Then set the IP address (IP Address that is assigned in SQL Server installed machine) into IP Address text box.

SQL SERVER TCPIP

And the problem is solved. Provide a image of this window for your help.

Comments

comments

Powered by Facebook Comments

Recursive queries in MS SQL Server 2008

Case 1: In human resource management system each employee is supervised by another employee. To do this I create a table where I assigned one employee with other by following ways.

Employee ID Name Supervisor ID
001 Paroar Habib Null
002 Sumon Mazumder 001
003 Kalim Uddin 001
004 Jewel Ahmed 002

Case 2 : In same human resource management system I have to create a dynamic menu to give the permission to individual user. For that reason I create a table as like follows:

vmenuid vmenuname vparent
M001 HR System Null
M002 Payroll M001
M003 Salary Benefits M002
M004 Recruitment M001

In both the case there is a recursive situation happens in both tables. So if I want to create a query to get all the menu details with parent id and steps, then I can write following queries which is basically called Recursive Queries Using Common Table Expressions.

WITH security_menu_Recursive(Parent,MenuId,MenuName,LEVEL)
AS
(
    SELECT vparent,vmenuid,vmenuname,0 AS LEVEL FROM dbo.SecurityMenu WHERE vParent = null
    UNION ALL
    SELECT vparent,vmenuid,vmenuname,Level + 1 AS LEVEL FROM dbo.SecurityMenu
    INNER JOIN security_menu_Recursive AS smr ON smr.menuid = dbo.SecurityMenu.vParent
)
SELECT parent,menuid,menuname,LEVEL FROM security_menu_Recursive

After execute the code, I got the below data.

Parent MenuID MenuName Level
Null M001 HR System 0
M001 M002 Payroll 1
M001 M004 Recruitment 1
M002 M003 Salary Benefits 2

If you know anything easy or alternates, then give your best comments.

Comments

comments

Powered by Facebook Comments

Remote validation in MVC3 : simple way to pass the form value from custom view model to controller via Remote Attribute

There are so many article on web about the remote validation in MVC3. But I didn’t find the right article to get the perfect solution for my project. I just spend a week  to solve it by my own idea and it works like a charm. You are thinking what’s the problem as remote validation is so easy in MVC3. That’s right, but if you need to do remote validation against more then one parameter as well any type of value from form then, this article will help you a lot to make you job done perfectly.

This article covered the following topics:

  • What I want to do?
  • What I did and save my hair loss?
  • Is there any alternates?

What I want to do?

What want to do?

As per above picture, I want to send the bo_account_id, bo_account_no and hidden value of FormType to controller to check that bo_account_no is exist or not. This doesBoExist method will work for both create and edit view. That’s way I use hidden value as ‘create’ at create view and ‘edit’ at edit view. In case of bo_account_id, at create view it will send null value and edit view it will send bo account no of which data want to edit.

What I did and save my hair loss?

BO Model

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace StockPortfolio.Models
{
    [MetadataType(typeof(BO_Validation))]
    [Bind(Include = "vBONo,vBrokerID,vInvestorID,nCommision")]
    public partial class BO
    {
        public string FormType { get; set; }

        public BO()
        {
        }
    }
    public class BO_Validation
    {
        [Key]
        [HiddenInput(DisplayValue = false)]
        public string vBOID { get; set; }

        [Required]
        [Display(Name = "BO Account NO")]
        [Remote("doesBoExist", "BO", AdditionalFields = "FormType,vBOID", HttpMethod = "POST", ErrorMessage = "BO account no already exists. Please enter a different BO account no.")]
        public string vBONo { get; set; }

        [Required]
        [Display(Name = "Broker Name")]
        public string vBrokerID { get; set; }

        [Required]
        [Display(Name = "Investor Name")]
        public string vInvestorID { get; set; }

        [Required]
        [Range(0.01,1)]
        [Display(Name = "Commision")]
        public decimal nCommision { get; set; }

    }

}

BOCreate Model

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace StockPortfolio.Models
{
    [Bind(Exclude = "SelectedStockExchange")]
    public class BOCreate
    {
        stock_sadequeEntities db = new stock_sadequeEntities();

        public BO bo { get; set; }
        public IEnumerable<SelectListItem> StockExchange { get; set; }
        public IEnumerable<SelectListItem> BrokerSelect { get; set; }
        public IEnumerable<SelectListItem> InvestorSelect { get; set; }

        [Required]
        [Display(Name = "Stock Exchange")]
        public string vStockExID { get; set; }

        public BOCreate()
        {
        }

        public BOCreate(BO boes)
        {
            this.bo = boes;

            var queryA = db.StockExchanges.Select(c => new SelectListItem
            {
                Value = c.vStockExchangeID,
                Text = c.vStockExchangeName,
            });
            StockExchange = queryA.AsEnumerable();

            var queryB = db.Brokers.Select(c => new SelectListItem
            {
                Value = c.vBrokerID,
                Text = c.vBrokerName,
            }).Where(u => u.Value == bo.vBrokerID);
            BrokerSelect = queryB.AsEnumerable();

            var queryC = db.Investors.Select(c => new SelectListItem
            {
                Value = c.vInvestorID,
                Text = c.vInvestorName,
            });
            InvestorSelect = queryC.AsEnumerable();

        }
    }
}

BOController (Due to some security issue of my project I didn’t post all the code of this controller, but below code is enough to understand the concept)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
using System.Web.Security;
using StockPortfolio.Models;
using StockPortfolio.Models.BOFolder;
using System.Web.Script.Serialization;
using MvcPaging;

namespace StockPortfolio.Controllers
{
    public class BOController : Controller
    {
        IBORepository BORepository;

        [HttpPost]
        public JsonResult doesBoExist(FormCollection parm)
        {
            BO bo = BORepository.GetBOByName(parm["bo.vBONo"], parm["bo.FormType"], parm["bo.vBOID"]);
            return Json(bo == null);
        }
    }
}

Create view (Due to some security issue of my project I didn’t post all the code of this view, but below code is enough to understand the concept)

@model StockPortfolio.Models.BOCreate

@{
    ViewBag.Title = "Create New BO Account";
}

<div class="row">
    <div class = "span8 offset2">
        <h1>Create New BO Account</h1>
        <hr />

        @if (!Html.ViewData.ModelState.IsValid)
        {
        <div class="alert alert-error">
          <a class="close" data-dismiss="alert">×</a>
          Incomplete data found. Please correct the errors and try again.
        </div>
        }

        @using (Html.BeginForm("Create", "BO", FormMethod.Post, new { @class = "form-horizontal", id = "BOform" })) 
        {
            <fieldset>
                @Html.HiddenFor(model => model.bo.FormType)

                <div class="control-group">
                    <label class="control-label" for="focusedInput">@Html.LabelFor(model => model.bo.vBONo)</label> 
                    <div class="controls">
                        @Html.TextBoxFor(model => model.bo.vBONo)
                        <span class="help-inline">@Html.ValidationMessageFor(model => model.bo.vBONo)</span>
                    </div>
                </div>
                <div class="form-actions">
                    <input type="submit" class="btn btn-primary" value="Create" /> 
                </div>
            </fieldset>
        }
        <P>@Html.ActionLink("Back to List", "Index")</P>
    </div>
</div>

I post all the required code. Now I will point the important code from above.

  1. FormType is created at BO model. (See BO Model [Line – 14])
  2. Use a remote validation attribute with required data. It contains AddtionalFields as FormType and vBOID (see BO Model [Line – 28])
  3. create a custom model with the object of BO and three SelectListItem StockExchange, BrokerSelect and InvestorSelect. This model is used for view. (see BOCreate Model [Line – 15 to 18])
  4. create a doesBoExist method with FormCollection parameter and pickup the individual data from parameter as parm["bo.vBONo"], parm["bo.FormType"], parm["bo.vBOID"] and that’s the main trick. (see BOController [Line – 20])

and that’s all.

Is there any alternates?

If you know any alternatives, please let us know.

This article is selected by ASP.NET and marked as Article of the Day.

Comments

comments

Powered by Facebook Comments

RenderBody, RenderPage and RenderSection methods in MVC 3

In this article we will learn about the three methods of MVC 3 and those are RenderBody, RenderPage and RenderSection.  We will learn by the following topics:

  • RenderBody
    • What is RenderBody?
    • How RenderBodyworks?
    • RenderBody Example
  • RenderPage
    • What is RenderPage?
    • How RenderPageworks?
    • RenderPage Example
  • RenderSection
    • What is RenderPage?
    • How RenderPageworks?
    • RenderPage Example

Now go to in detail…

RenderBody

What is RenderBody?

In layout pages, renders the portion of a content page that is not within a named section. [MSDN]

How RenderBody Works (graphical presentation)?

RenderBody

RenderBody Example

It’s simple. Just create a ASP.NET MVC 3 web application by visual studio 2010. After creating this application, you will see that some files and folders are created by default. After that open the _layout.cshtml file from views/Shared folder.  Basically this file will be used as a standard layout for all the page in project. Keep in mind that you can create more then one layout page in a application and to use layout page in other page is optional. _layout.cshtml file consist the following code.

<!DOCTYPE html>
<html>
<head>
    <title>@ViewBag.Title</title>
    <link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css" />
    <script src="@Url.Content("~/Scripts/jquery-1.5.1.min.js")" type="text/javascript"></script>
</head>
<body>
    <div class="page">
        <div id="header">
            <div id="title">
                <h1>My MVC Application</h1>
            </div>
            <div id="logindisplay">
                @Html.Partial("_LogOnPartial")
            </div>
            <div id="menucontainer">
                <ul id="menu">
                    <li>@Html.ActionLink("Home", "Index", "Home")</li>
                    <li>@Html.ActionLink("About", "About", "Home")</li>
                </ul>
            </div>
        </div>
        <div id="main">
            @RenderBody()
        </div>
        <div id="footer">
        </div>
    </div>
</body>
</html>

Now open another file called index.cshtml from views/home. This file consist the following code.

@{
    ViewBag.Title = "Home Page";
}

<h2>@ViewBag.Message</h2>
<p>
    To learn more about ASP.NET MVC visit <a href="http://asp.net/mvc" title="ASP.NET MVC Website">http://asp.net/mvc</a>.
</p>

Main thing is that by the above code you couldn’t find which layout page is being used by this index page. But there is little tricks done at MVC3. You will get a file called _ViewStart.cshtml at views folder. This file consist of  following code.

@{
    Layout = "~/Views/Shared/_Layout.cshtml";
}

This code means that by default all the content pages will follow the _Layout.cshtml layout page.  Now if we consolidate the _layout.cshtml and index.cshtml page both, we will get the following code.

<!DOCTYPE html>
<html>
<head>
<title>Home Page</title>
<link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css" />
<script src="@Url.Content("~/Scripts/jquery-1.5.1.min.js")" type="text/javascript"></script>
</head>
<body>
<div class="page">
<div id="header">
<div id="title">
<h1>My MVC Application</h1>
</div>
<div id="logindisplay">
@Html.Partial("_LogOnPartial")
</div>
<div id="menucontainer">
<ul id="menu">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
</ul>
</div>
</div>
<div id="main"><h2>@ViewBag.Message</h2>
<p>
To learn more about ASP.NET MVC visit <a href="http://asp.net/mvc" title="ASP.NET MVC Website">http://asp.net/mvc</a>.
</p>
</div>
<div id="footer">
</div>
</div>
</body>
</html>

It’s nothing complicated, it’s just replacing the code of RenderBody() of layout page by the code of content page.

if you want to use different layout for different content pages, then create a layout page as like _Layout.cshtml and just copy below code to your desired content page.

@{
Layout = "another layout page";
}

RenderPage

What is RenderPage?

Renders the content of one page within another page. [MSDN] The page where you will place the content could be layout or normal page.

How RenderPage Works (graphical presentation)?

RenderPage

RenderPage Example

Create a page called _StaticRenderPage at Views/Shared folder. Open it and paste the below code.

<p>
This messge from render page.
</p>

Open the Index.cshtml file from Views/Home folder and paste the below code.

@RenderPage("~/Views/Shared/_StaticRenderPage.cshtml")

Now If you merge the code of _StaticRenderPage to Index.cshtml, then you will get the below code.

@{
    ViewBag.Title = "Home Page";
}

<h2>@ViewBag.Message</h2>
<p>
    To learn more about ASP.NET MVC visit <a href="http://asp.net/mvc" title="ASP.NET MVC Website">http://asp.net/mvc</a>.
</p>
<p>
This messge from render page.
</p>

If you want to pass the data by using RenderPage, then you have to use the data parameter at RenderPage. I will give another example for this. To do this, at first create a class file called AvailableUser at Models/AccountModels. Create the class with the below code.

public class AvailableUser
    {
        public string UserName { get; set; }
        public string UserPassword { get; set; }

        public static List<AvailableUser> AllUsers()
        {
            List<AvailableUser> userList = new List<AvailableUser>();

            AvailableUser user1 = new AvailableUser
            {
                UserName = "Anupam Das",
                UserPassword = "lifeisbeautiful",
            };

            AvailableUser user2 = new AvailableUser
            {
                UserName = "Chinmoy Das",
                UserPassword = "GoodTime",
            };

            userList.Add(user1);
            userList.Add(user2);

            return userList;
        }
    }

Now go to AccountController and write down the below code

public ActionResult AvailableUserList()
{
return View(MvcApplication1.Models.AvailableUser.AllUsers());
}

Create a view page called AvailableUserList.cshtml at Views/Account with the below code.

@model IEnumerable<MvcApplication1.Models.AvailableUser>

@{
ViewBag.Title = "AvailableUserList";
Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>AvailableUserList</h2>

@RenderPage("~/Views/Shared/_DisplayAllUsers.cshtml", new { AvailableUser = Model })

At last create another view page called _DisplayAllUsers at Views/Shared with the below code.

@foreach (var usr in Page.AvailableUser)
{
<text>
@usr.UserName   @usr.UserPassword <br />
</text>
}

Now run the project (Account/AvailableUserList) and see the user list which comes from AvailableUser class.

RenderSection

What is RenderSection?

In layout pages, renders the content of a named section. [MSDN]

How RenderSection Works (graphical presentation)?

RenderSection

RenderSection Example

It’s simple, just add the below code at _layout page.

@RenderSection("Bottom",false)

and add the below code at Index page.

@section Bottom{
This message form bottom.
}

That’s All. But keep in mind that if you don’t want to use the Bottom section in all page then must use the false as second parameter at RenderSection method. If you will mention it as false then it will be mandatory to put Botton section at every content page.

Download the project

Now run the project and see how it works !!!

I will be happy, if you found anything wrong or know more please share it via comments.

This article is also available at :

The Code Project

The Code Project

This article is also selected by ASP.NET and marked as Article of the Day.

Comments

comments

Powered by Facebook Comments

Easy way to track the store procedure error in MS SQL SERVER 2008

You can easily trace all the errors of store procedure in MS SQL SERVER. To do this, first create a table called Error.

CREATE TABLE [dbo].[Error](
    [iAutoID] [int] IDENTITY(1,1) NOT NULL,
    [dErrorDate] [datetime] NOT NULL,
    [vErrorNumber] [nvarchar](max) NULL,
    [vErrorSeverity] [nvarchar](max) NULL,
    [vErrorState] [nvarchar](max) NULL,
    [vErrorProcedure] [nvarchar](max) NULL,
    [vErrorLine] [nvarchar](max) NULL,
    [vErrorMessage] [nvarchar](max) NULL
) ON [SECONDARY]

GO

ALTER TABLE [dbo].[Error] ADD  CONSTRAINT [DF_Error_dErrorDate]  DEFAULT (getdate()) FOR [dErrorDate]
GO

now create a store procedure by wrting the following code.

CREATE PROCEDURE [dbo].[prcDailyAttendanceGeneration]
WITH
EXECUTE AS CALLER
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON
    DECLARE @starttrancount INT
    Begin TRY
        SELECT @starttrancount = @@TRANCOUNT
        IF @starttrancount = 0
        BEGIN TRANSACTION

		-- Your own code start
		Execute prcInsertShiftDateWiseInfo
        -- Your own code end

		IF @starttrancount = 0
        COMMIT TRANSACTION
	End Try
    Begin Catch
        -- Test if the transaction is uncommittable.
        IF  XACT_STATE() <> 0 AND @starttrancount = 0
        BEGIN
            ROLLBACK TRANSACTION;
        END;
		-- This is the main tricks to store all the errors in error table.
		insert into Error(vErrorNumber,vErrorSeverity,vErrorState,vErrorProcedure,vErrorLine,vErrorMessage)
        SELECT ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE()
	End Catch
END

now if the procedure gives any error, error details will be saved into error table. By this way you can easily get all the error details from error table and can take necessary steps.

This article is also available at :

The Code Project

The Code Project

Comments

comments

Powered by Facebook Comments

Deploy MVC2 Application at IIS 5.1 (windows XP)

If you try to deploy the MVC2 application at IIS 5.1 then normally it will not work properly. It could show you the following errors.

Deploy MVC2 Application at IIS 5.1 (windows XP) Deploy MVC2 Application at IIS 5.1 (windows XP)

So what could be the ultimate solution to for this? Just Follow the below instructions and check that it works or not.

At first create a virtual directory and publish the project successfully. Then go to Internet Information Services and right click on virtual directory. Select Properties from the menu. A window will appear. Click on Configuration button.

Deploy MVC2 Application at IIS 5.1 (windows XP)

After clicking on Configuration button a Application Configuration window will appear. Now select first Application Mapping and click on Edit button. A Add/Edit Application Extension Mapping window will appear. Copy the executable location from text box. Paste it into notepad. Click on cancel button.

Deploy MVC2 Application at IIS 5.1 (windows XP)

Now click on Add button and once again Add/Edit Application Extension Mapping window will appear. Paste the executable location from notepad to executable text box. and write .* as extension. Also uncheck the Check that file exists. Now click on OK button.

Deploy MVC2 Application at IIS 5.1 (windows XP)

At last just change the ASP.NET version to 4.0 from virtual directory properties.

Now try to run the site once again. It should work.

Comments

comments

Powered by Facebook Comments