jQuery AJAX failed calling ASP.NET WebMethods

Recently I migrated all code files from ASP.NET Web Application to ASP.NET WebSite (with Framework 4.5.2), because of some requirements we need to move towards website template. At the time I faced this issue, all the ajax calls stopped working. When I placed breakpoint on WebMethod, It was not getting fired.

Here is the WebMethod code:

[WebMethod(EnableSession = true)]
public static string GetPageSummary(string pageid)
{
 string html = "";

 if (!string.IsNullOrEmpty(pageid))
 {
  html = PageHelper.GetPageSummaryHtml(pageid);
 }

 return html;
}

And here is the jquery Ajax call for that method:

$.ajax({

  type: "POST",
  url: "MyPage.aspx/GetPageSummary",
  data: "{ 'pageid':'" + id + "'}",
  contentType: "application/json; charset=utf-8",
  dataType: "json",
  success: function (response) {
   showModelPopupForSummary(response.d)
  },
  error: function (response) {   
   //error-handling code...
  }
 });

It all looks fine and working in previous project, but not with new WebSite project.

After spending way too much time on this, I found out the solution for this.

You have to change enum value for AutoRedirectMode. Go to App_Code/RouteConfig.cs, inside RegisterRoutes() method, you will see this line:

 settings.AutoRedirectMode = RedirectMode.Permanent;

Change the enum value to RedirectMode.Off, final statement would be:

 settings.AutoRedirectMode = RedirectMode.Off;

After changing this all my ajax calls started working successfully.

How to use table variable in dynamic sql?

I came across a scenario while working with user-defined-table-types where I was need to query from table variable using dynamic SQL. I feel it worth sharing here in case someone might need in future.

I have created a user-defined table-type:

CREATE TYPE [dbo].[UDTT_Items] AS TABLE(    
    [ItemId] int identity(1, 1),
    [ItemCode] [varchar](10) NULL,
    [ItemName] [varchar](255) NULL, 
    [StockQty] decimal(18,3 ) NULL, 
    PRIMARY KEY CLUSTERED 
(
    [ItemId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

In my stored procedure I declared a table variable for this UDTT:

declare @tblItems UDTT_Items

Here is how we normally use select statement on table variable.

select * from @tblItems

The problem comes when I tried to put this table variable in dynamic SQL. For example, if I try to run the above select statement from execute clause:

EXECUTE SP_EXECUTESQL N'select * from @tblItems'

It gives me the error message:

Must declare the table variable "@tblItems".

Fortuntely there is work around available by EXECUTE SP_EXECUTESQL. You can pass the table variable as a read-only parameter to dynamic SQL. So the following dynamic sql will work and run the given query as exptected.

DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='SELECT * FROM @tblItems;';
EXECUTE SP_EXECUTESQL @SQL,N'@tblItems UDTT_Items READONLY',@tblItems;

Here I put my query in @SQL variable, and when calling EXECUTE SP_EXECUTESQL, we have to first define our parameters that need to be passed as second argument, and the actual table variable(i.e. parameter value) in third argument.

MS SQL Server - Show Query Execution Time

If you are using MS SQL Server 2008 or higher version, you can use the setting STATISTICS TIME. It will display the execution or run time in separate messages tab.

Setting this option ON will display the number of milliseconds required for complete statement cycle, i.e. parse, compile, and execute.

For example, following query will display order count for each customer who get registered in the month of August 2018.

select c.FirstName, c.LastName, c.RegistrationDate, Count(1) as OrderCount
from dbo.Customers c
 inner join dbo.Orders o on o.OrderId = c.OrderId
where 
 c.RegistrationDate between '1 August 2018' and '31 August 2018'
group by c.FirstName, c.LastName, c.RegistrationDate

Here is the sample output from messages tab, showing the execution time for above statement.

(200 rows affected)

 SQL Server Execution Times:
   CPU time = 1640 ms,  elapsed time = 2259 ms.
   

References:

• Microsoft - SET STATISTICS TIME (Transact-SQL)

Generating random strings with T-SQL

For testing scenarios you may need to generate random strings. You can use one of the following ways for this purpose.

declare @alphabet varchar(36) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
select
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1);

This script will generate random string of length 5. If you need to increase or decrease number of characters in the generated string, you can add/remove substring statements in the above script.

A more easier approach can be to use NEWID() function, convert the result to varchar and then you can use LEFT(), RIGHT(), SUBSTRING() or any combination of string functions to generate string of desired length.

SELECT CONVERT(varchar(255), NEWID())

This will convert NEWID() output to varchar(255)

SELECT LEFT(CONVERT(varchar(255), NEWID()), 10)

This will display left 10 characters of the NEWID().

SELECT RIGHT(CONVERT(varchar(35), NEWID()), 10)

This will display 10 characters of the NEWID() from right.

SELECT SUBSTRING(CONVERT(varchar(35), NEWID()), 10, 25)

This will display substring of 15 characters starting from index 10 and ends at index 25.

Understanding DEFAULT constraints in MS SQL Server

If you have worked with multiple instances of a database, there is a high chance that you have faced issues with constraints default names. You generated the script for tables/schema changes and try to run on another instance of that database, where some constraint is being dropped from a table, and you get error message.

For example, you have an Items table, have a constraint defined on column Item_Code with default name, and a drop constraint statement like this:

 ALTER table [Items] drop DF__Items__Item_Code__07F6335B

It may worked on your current database instance, but on some other instances it may give you an error message like this:

 Msg 3728, Level 16, State 1, Line 1
 'DF__Items__Item_Code__07F6335B' is not a constraint.
 Msg 3727, Level 16, State 0, Line 1
 Could not drop constraint. See previous errors.

Why you get this error?

Because at the time you created this constraint, you have not provided a custom name, so SQL Server generated a default name. Since default name is auto-generated, there are no chances that this same name will be generated on each instance, no all other instances are having constraint with same name. When you try to run this drop constraint statement on other instances, you are getting this error.

Lets try to understand this with an example.

Create a new database.

Create a new table, lets say Items with this statement.

CREATE TABLE [dbo].[Items](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Item_Code] [nvarchar](255) NOT NULL,
 [Item_Name] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

At this point we have no constraints on any column of Items table. Lets defined a new one.

 ALTER TABLE [dbo].[Items] ADD  DEFAULT ('') FOR [Item_Code]
 GO

In this constraint, we are setting empty string as default value for column Item_Code. I did not provide any name, SQL Server has defined an auto-generated name, in my case it is DF__Items__Item_Code__07F6335A, and this is not a good approach.

Better is to define a custom name for constraints, which will remain static across all instances of this database.

Lets define a new constraint on Item_Name column, but this time with custom name:

 ALTER TABLE [dbo].[Items] ADD  CONSTRAINT [DF_Items_Item_Name]  DEFAULT ('') FOR [Item_Name]
 GO

Here constraint name is DF_Items_Item_Name, and will remain common on all nodes.

So far, we have created a new table and then defined constraints on existing columns.

The same problem will happen when you add new columns to this table. Lets add a new column CreatedBy with default name 'admin':

 ALTER TABLE [dbo].[Items] ADD [CreatedBy] [nvarchar](20) NOT NULL DEFAULT 'admin' 
 GO    

Here again, we did not provide a custom name, so SQL Server will place auto-generated. In my case it is DF__Items__CreatedBy__0DAF0CB0.

Good technique will be to provide custom name, lets create a new column CreatedOn, but this time with our specified name:

 ALTER TABLE [dbo].[Items] ADD [CreatedOn] [datetime] 
  CONSTRAINT [DF_Items_CreatedOn] DEFAULT (getdate()) NOT NULL 
 GO

Here constraint name is DF_Items_CreatedOn.

One thing to be aware that you can not change constraint names, you have to drop old one and create new constraint if you need to modify or rename.

I hope you found this post helpful. Share your thoughts in the comments below!