CRUD (Create, Retrieve, Update and Delete) in MVC. When we start to learn new language, first we try to run an application with “Hello World” and then CRUD functionality. So in this article we will see how to select records from database (with WebGrid, pagination and sort functionality), update a record, add new record and finally delete a record in ASP.Net MVC. Let’s create a simple table... By Ali Adravi On 17 Aug 2013 Viewed: 105,947
Parameterized view means we can pass some value to get the data from the view the table by using view. The parameter accepts values that can be supplied later by prompting the user or programmatically, it is possible in MS-Access and FoxPro but is it supported in SQL Server? Answer is big NO, but there are ways to achieve the same feature, which we will discuss in this article. By using view... By Ali Adravi On 07 Sep 2013 Viewed: 24,754
Try to add ADO.Net Entity Data Model by using Visual Studio 2013 and having SQL Server 2008 R2 and got the error "Unable to add data connection.", so change my mind and try the same by using Linq to SQL Classes and again go the same error. As usual I try some other options to connect to the database but still the error message is same so decided to explore to fix it. Here is the complete... By Ali Adravi On 06 Nov 2014 Viewed: 22,043
Bulk insert with text qualifier from a text or csv file into sql server can be achieved by using a format file, in format file we can define the sequence of columns in source file (txt or csv file), data type, length, destination table column, version and number of columns, we will see everything in detail with different examples Suppose we have to import following file to our database ... By Ali Adravi On 10 Jun 2013 Viewed: 20,575
Bulk insert is used to Import a data file into a database table or view in a user-specified format. It's not daily using kind of code, so it's very common question on most of the site like. How to import CSV file into a table? How to insert records from a CSV file to a table, delimited by comma? How to load CSV file to SQL Server database? We will discuss here the same but with more detail, so... By Ali Adravi On 08 Feb 2013 Viewed: 17,173
I was checking how to validate a user email from database without post back, using JavaScript. After some search and RND found the way to call the web service method with the help of Ajax and JavaScript. Let’s say we have a registration page on which we accept email from the user and we expect it should be unique in entire database. So we need a method to immediately validate the email from... By Ali Adravi On 24 Jun 2012 Viewed: 14,569
When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's say we have a customers table with following structure and data ![alt text][1] Now suppose we... By Ali Adravi On 13 Feb 2013 Viewed: 12,556
sql reporting subreport with toggle To create a toggle sub report in sql reporting service is too easy but if you will not aware about it then it is really difficult for you. So in this article we will see how to create a report with having sub report, and sub report data will load only when we will toggle (0pen) the sub report and not on the load of main report. To learn basic of reports see... By Ali Adravi On 09 Sep 2013 Viewed: 11,686
just think to pass comma separated Id as a parameter or how to use comma delimited value as parameter and use that in SQL server, since there is no way to use parameter as an array so we can use string for multiple IDs or values but if it's integer then we cannot use it in IN clause in SQL Server even if it is string (varchar). So how to use multiple values or IDs as parameter and use that in IN... By Ali Adravi On 02 May 2013 Viewed: 9,933
XACT_STATE() is a scalar function that gives the user transaction state of a current running request. It indicates whether the request has an active user transaction, and whether the transaction is capable of being committed or not. XACT_STATE returns the following three values - **1**: The current request has an active user transaction. The request can perform any actions, including... By Ali Adravi On 28 Dec 2012 Viewed: 7,919
How to change column name and data type of a column cannot be done in one statement but first we need to change the column name and then data type, In this post we will check many other things like how to change data type of a column, how to drop a column, how to add a new column, how to make a column not nullable etc. So first we will see how to change a column name as well as data type of... By Hamden On 05 May 2013 Viewed: 5,932
In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get our result, see this Declare @price Decimal(10,2) Set @price=12345678.90 Select... By Myghty On 26 Mar 2013 Viewed: 3,311
ISNULL in Sql Server is uded to check and return alternate value if value is NULL, while to only check the NULL value, we can us IS NULL with space, in MySQL and Sqlite, for same functionality, we can be use IFNULL and in Oracle we can use NVL. Suppose we want to return 0 if column value is null then we can use select ISNULL(columnName, 0) from table_name Column type and alternate... By Hamden On 31 Aug 2013 Viewed: 3,140
In Sql Server we use DateTime column but in many cases we need to get either only date or only time. So we will see different ways to get these values according to our requirements. If you are using SQL Server 2008 or newer version then luckily we have two types Date and Time Let’s see this in action with Sql Server 2008 (it will not work in older version of SQL) SELECT Getdate()... By Hamden On 07 Sep 2013 Viewed: 2,574
When we create function we can use default value for parameters if we need so, is there any way to create a procedure with default value parameter in SQL Server so when we don’t pass the value for that parameter they can use default values? Yes, we can. In this article we will create procedure with default parameter value. Let's see default template provided by microsoft: SET ANSI_NULLS... By Ali Adravi On 14 May 2013 Viewed: 1,740
I need to create script for entire database changes every week for my client to run on production server, when I was creating the script I need to check whatever I have created so I try to use transaction and to roll back every change, so I used try catch but it inserted many records in some master tables while there was some error in script and still error was not catching by try catch where i... By Hamden On 11 Oct 2013 Viewed: 1,611
Delete duplicate records from table is the very common question and task we need in our day to day life, so we will see how easily we can delete duplicate records from a table by using subquery and using partition by function supported by Sql Server 2005 and later version. Say the table name is Address, having following records AddressID CustomerID Address City Zip Country 1 ... By Ali Adravi On 26 Aug 2013 Viewed: 1,456
SQL Reporting services also known as SSRS is a great tool to create report from database. In this article we will learn how to create a basic Report. We will learn how to create a shared data source so we can use it for all the reports in our project, How to create our data set to bind columns in report. For advanced report see my other article [SSRS subreport with drill down step by step in sql... By Ali Adravi On 10 Sep 2013 Viewed: 1,119
We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new table with same structure and identity column and copy existing table records to this table and drop the... By Ali Adravi On 02 May 2013 Viewed: 1,084
Count function takes one parameter and returns the number of rows for the matching criteria. Select Count(*) From employees It will return count of entire records into Employees table, same result can also be get from following sql statement Select Count(1) From employees In this statement we used, 1 in place of *, there are discussion about count(*) and count(1),... By Hamden On 31 Aug 2013 Viewed: 1,070