Archive for August, 2009

You can retrieve comma separated values from a database table using following type of SQL scripts

DECLARE @varList VARCHAR(1000)
SET @varList = SPACE(0)
SELECT @varList = @varList + ', ' + ContactName
FROM Customers
SELECT @varList
SELECT SUBSTRING(@varList, 3, 1000)

You can see that the statement “SELECT @varList” will return list like “ , Maria Anders, Ana Trujillo, Antonio Moreno, Thomas Hardy….etc ”

Here you can see the result will have extra comma at the start of the string result. To remove this I have added another select statement “SELECT SUBSTRING(@varList, 3, 1000)”. This will return string upto 1000 characters only, you can set this value as per your requirements.

Today I am writing about how you can auto-refresh the page and still how you can manage the view-state of the page.

There may be requirement to fill up a long lasting form and this might take you a longer time to think or type. Now the general problem that occurs while filling up this kind of form is when you submit the form the session got expired or the timeout event occurs. So to overcome this situation we can set some timer to that particular page for refresh so that after several minutes it got refreshed automatically and we did not face any timeout or session related issues.

Now there are several ways to auto-refresh the page, the one is to put meta tag like <meta http-equiv="refresh" content="600">. What this tag will do is it will refresh the page after every 10 minutes, but when the page gets refresh then it will not persist the view-state of the page. This means if you are filling up any form and the page gets refreshed then you will not able to get all the filled content and the page will get loaded in its original form.

There was one requirement where I need to auto refresh the page as well as need to keep the view-state of the page. To achieve this I have put a simple JavaScript code on BODY tag of my page as below:

<Body onload="setTimeout('Form1.submit();', 600000);">
<form id="Form1" method="post" runat="server">

Here this JavaScript method “setTimeout()” will submit your form data after every 10 minutes and the page will also get refreshed so that it will also persist the view-state of the page after auto refresh of the page. Please note that the time 600000 here is in milliseconds. (1 second = 1000 milliseconds)

Happy Programming!

After a very long time I am posting this stuff…

I have noticed that on day to day activity with MS SQL, one requires to split any string constant every now and then and so I have decided to make a post of it.

Please create below SQL function into your database and then you can use this to split any string constant with any separator you specified.


CREATE function [dbo].[fun_Split]
(
@String nvarchar(4000),
@Delimiter char(1)
) Returns @Results Table (Items nvarchar(4000))
As
Begin
Declare @Index int
Declare @Slice nvarchar(4000)
Select @Index = 1
If @String Is NULL Return
While @Index != 0
Begin
Select @Index = CharIndex(@Delimiter, @String)
If (@Index != 0)
Select @Slice = left(@String, @Index - 1)
else
Select @Slice = @String
Insert into @Results(Items) Values (@Slice)
Select @String = right(@String, Len(@String) - @Index)
If Len(@String) = 0 break
End
Return
End

You can use this function like example given below:
SELECT Items FROM dbo.fun_Split('1,2,3,4',',')

This will return a table with Column “Items” where you can find each comma separated values in different rows.

Happy Scripting!