Jackson Pushpanathan's Blog

Below is the stored procedure which will query the database and find the list of objects which match the search string specified. CREATE PROCEDURE [dbo].[sp_Search_sProcs] @searchText varchar(100) AS SELECT sysobjects.name FROM sysobjects, syscomments WHERE syscomments.id = sysobjects.id and sysobjects.xtype = 'P' AND sysobjects.category = 0 AND syscomments.text LIKE '%' + @searchText +'%' ORDER BY sysobjects.name GO

I was struggling with this problem for nearly a day and a half, before I found the solution. Hence, thought I will blog this. The website I was developing was running extremely slow when run under debugger of visual studio. Initially I thought, it may be the slowness of webservices or the database components that we were using. But passing the value through the interfaces produced results quickly. hence I concluded that the problem was with my local website code. The fix was elusive and time was running out. I began deleting the files in bin directory and recompiled the code. still same effect. I deleted the project .suo file and eureka! the project started running correctly!!! I read some where that deleting .ncb file may help aswell.. Happy Debugging : )

I was trying to find a way to dynamically set class name from stylesheet using javascript. many of the sites adviced to use obj.style.className==' ', but this did not work! instead use this : obj.className==' ' -- this work is IE and Firefox.

I had to pass an object from asp to asp.net component but every time it returned an 'Invalid procedure call or argument:object name' I found that when passing objects from asp to .net components use the below method : oObject.Save(oObjectToBeSaved, errMsg) -- returned error message oObject.Save((oObjectToBeSaved), errMsg) -- works successfully. Also, the .net componenet which accepts parameters should have its signature set as ByRef..

Scenario: if you want to output some data as excel file, and display the File download dialog box, use the code below : Response.ContentType = "application/vnd.ms-excel" Response.AddHeader "Content-Disposition", "attachment;filename= .xls"

Set the parameters in the following order: rs.CursorType =adOpenKeyset rs.CursorLocation=adUseClient rs.open , Opening a recordset in this manner will change the default connection from ForwardOnly to Keyset.

When we want display a difference in months, datediff or date add does not help you in returning the last days of months, eg. if we do a dateadd(m,-3,'30 june 2005') will display '30 september 2005' but if you expected to get 1st of april 2005 then you got a problem, the below function gives a work around for the problem. DECLARE @Dt DATETIME SET @Dt = '30 June 2004' SET @Dt = DATEADD(m,-2,@Dt) SET @Dt = DATEADD(d,-DATEPART(d,@dt)+1,@Dt) SELECT @DT

Here is what I found today : My requirement was to dynamically add text boxes in the form based on selection made from a drop down box. Then the user gets to type in information on the text box and save it. I planned to use div tag , and create input boxes dynamically into the div tag. This worked great : Here is what I did : I built a html string (sInsertHTML) with the code to create input box . This I appended to the divTag's innerHTML property. divTag.innerHTML = divTag.innerHTML + sInsertHTML This produced the desired result. i.e. I was able to create controls based on the selection from the drop down, recursively. PROBLEM While the above condition worked great it lost any values previously input in those boxes!!! To walk you through on what I did : I used the drop down to select a value and clicked the insert button. The code to write input control inside the div tag creates the control. I type some value in the newly created textbox. I then use the selection drop...

An important point to remember when writing dynamic SQL is to ensure that the tables that you are using in your SQL statement should have 'select' permissions enabled for the query to work. if you can't allow select permissions on the table then avoid using dynamic SQL (this may be the case in most of the production environments). I had the unfortunate experience of using dynamic SQL in a project without realizing the above issue. Everything worked in my system and in the test environment as well, but when the solution was deployed to production, the query did not work. I found that, the users in the production environment had permissions to execute stored procedures alone, and for the dynamic sql to work ( which used EXEC ' ) required select permissions on the table. hence the stored procedure failed. I re-wrote the query using COALESCE statement to workout the where clause. The link below touches on how to build dynamic where clause. http://www.sqlteam.com/i...