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!

System.Transactions.TransactionManagerCommunicationException: Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool. —> System.Runtime.InteropServices.COMException (0x8004D024): The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D024)

You can resolve this by following the steps below

Control Panel – Administrative Tools – Component Services – My Computer properties – MSDTC tab – Security Configuration tab – Network DTC Access (checked) / Allow Remote Clients (checked) / Allow Inbound (checked) / Allow Outbound (checked) / Enable TIP Transactions (checked)

REBOOT computer

When you deal with the javascript and when you are using any form elements of the page in the script then you better make sure that your whole page gets load first. The form elements that you are using into the script will only get used until the page loads completely.

I have faced one situation where I was using popup window and was transferring the data of parent window to the opened popup. I have created one function into the popup window which will set some values into the text elements of the popup. I am calling this function from the parent window by passing some arguments to that function.

Now what happen is, when I am calling this function by putting some ‘alert()’ messages into the caller function and called function then the script is working fine for me. And when I am removing the ‘alert()’ message box from the script, the script stop working.

When I have analysis the script and debug it, I found that when I am calling a popup function from my parent window, the function did not set values into the text elements of the popup. This is because the popup window is not loaded completely. When I am putting an ‘alert()’ message box, the popup window gets sufficient time to load while I am busy clicking on the OK button for the alert message.

Thats it… I found that I was writing the script for the form elements which are not yet loaded into the popup and thats why I am getting this issue. I have resolved this issue by calling the javascript function of the popup only after it gets loaded completely.

If you found any similar issue then you can discuss here, I will try to solve it out.

Happy Programing!

Few months back I have posted one article on “Javascript: Maximum character validation and character counter for a textbox”, now here I have given one example on how to count words out of any textarea or textbox.

The counter of word will get incremented as you type in each word (one spell) into the textbox/textarea. This script will also work when anyone copy/paste the text into the textbox (copy/paste should not through mouse). Check the following script:

<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" TextMode="multiLine" MaxLength="100"
runat="server" onkeyup="javascript:WordCounter(this.id,'Label1');"
onblur="javascript:WordCounter(this.id,'Label1');"></asp:TextBox>
<asp:Label ID="Label1" runat="server" Text="100 chars
remaining"></asp:Label>
</div>
<script language="javascript" type="text/javascript">
function WordCounter(textId,lblID)
{
var this_field = document.getElementById(textId);
var label = document.getElementById(lblID);
var char_count = this_field.value.length;
var fullStr = this_field.value + " ";
var initial_whitespace_rExp = /^[^A-Za-z0-9]+/gi;
var left_trimmedStr = fullStr.replace(initial_whitespace_rExp, "");
var non_alphanumerics_rExp = rExp = /[^A-Za-z0-9]+/gi;
var cleanedStr = left_trimmedStr.replace(non_alphanumerics_rExp, " ");
var splitString = cleanedStr.split(" ");
var word_count = splitString.length -1;
if (fullStr.length <2)
{
word_count = 0;
}
if (word_count == 1)
{
wordOrWords = " word";
}
else
{
wordOrWords = " words";
}
if (char_count == 1)
{
charOrChars = " character";
}
else
{
charOrChars = " characters";
}
label.innerHTML = word_count + wordOrWords;
}
</script>
</form>
</body>

Happy Coding!!

I have two tables for e.g. tblStudents and tblSchools. Both are having different columns. For a reason I need to merge the two tables into one temporary table.

For example “tblStudents” contains the following design.

StudentID FirstName LastName Address DOB

Where as “tblSchools” contains the following design.

SchoolID Name Address IsSecodarySchool

Now, you would like to merge this two table columns into one temporary table then you can use the following sql script.

SELECT * INTO #tbl_Temp
FROM tblStudent, tblSchool where 1>2

Here, I have mentioned the “where” clause because I wanted to copy only the columns and not the data. You can ignore this clause if you want to copy the entire data too.

You can add as many tables you want to merge by separating with comma (,) between the table names.

You can add specific columns from specific tables by using table alias and column name combination.
For example
SELECT tblStudent.FirstName,tblStudent.LastName,tblSchool.Name INTO #tbl_Temp
FROM tblStudent, tblSchool where 1>2

Happy Programming!