Fetching Values Of Sql Table as Comma Seprated Values In SQL Variable

Declare @Str varchar(2000)

Begin

(Select @Str=COALESCE(@Str, '','') + Cast([Employee_name] as nvarchar(100)) + ','

from dbo.Employee )

Select substring(@Str,0,len(@str))

End

Max Length Validation (Display remaining character while typing)

Max Length validation is sometime doesn't give the right impact on the user as user has something in mind to type but at the end he/she came to know becuase of the max length of the field user is not allowed to type that long comment/value. So, better way is to display how many characters left from the total max length which user can enter in the field while he/she is entring the value of the field.

Code for the same is mentioned below.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>

<script language="java-script">
function textCounter(field, countfield, maxlimit)
{
if (field.value.length > maxlimit)
{
field.value = field.value.substring(0, maxlimit);
}
else
{
countfield.value = maxlimit - field.value.length;
}
}
</script>

<style
.txtBoxes
{
border:solid 1px DarkGray;
width:100px;
}
</style>

</head>
<body>
<form id="form1" runat="server">
<table border="0">
<tr>
<td>
<textarea "textCounter(areaTxtDetails, remlen, 10);" "textCounter(areaTxtDetails, remlen, 10);"
id="areaTxtDetails" "textCounter(areaTxtDetails, remlen, 10);" "textCounter(areaTxtDetails, remlen, 10);"
name="areaTxtDetails" rows="2" cols="65" class="areaTxtDetails" runat="server"> </textarea>
</td>
</tr>
<tr>
<td>
<span style="font-family:Arial;font-size:78%;">You have</span> <input type="text" id="remlen" cssclass="txtBoxes" width="30px"> <span style="font-family:Arial;font-size:78%;">characters remaining.</span>
</td>
</tr>
</table>

</form>
</body>
</html>

Max Length validation

Max Length validation is generally we use like we dont allow user to enter the anything once the limit is over but most of the time we left with one loop hole in that.

Restrict the user on the keypress but we don't restrict the user on paste and if user paste something he/she can cross the limit event because at that time the key press event doesn't gets fired. So below is the solution for the same.

C# code

txtComments_Demotion.Attributes.Add("onkeypress", "java-script:maxLength('" + txtComments_Demotion.ClientID + "','92');");
txtComments_Demotion.Attributes.Add("onpaste", "java-script:maxLengthPaste('" + txtComments_Demotion.ClientID + "','92');");

Javascript function in aspx page.

function maxLength(fieldName,maxChars)
{
field=document.getElementById(fieldName);
if(field.value.length >= maxChars) {
event.returnValue=false;
return false;
}
}

function maxLengthPaste(fieldName,maxChars)
{
field=document.getElementById(fieldName);
event.returnValue=false;
if((field.value.length + window.clipboardData.getData("Text").length) > maxChars) {
return false;
}
event.returnValue=true;
}

Accessing Xls sheet Data Microsoft.Office.Interop.Excel.dll

In my last article about Accessing data from xls from .NET code I used OledbConnection but that was not covering all my needs so for the better control I found another way of calling the Xls sheet data and I have created another component. The component code and the calling code is mentioned below in c# language.

Note: This require the reference for the Microsoft.Office.Interop.Excel.dll which you can find in your local system. Go to Start -> Search -> Search for (Microsoft.Office.Interop.Excel.dll) -> Copy the same to bin folder and reference from there.

Component Code.

/*
* Created by Dalip Vohra
* Date: 07 June 2008 00:09
*/
using System;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
using System.Data;
using System.Data.OleDb;
namespace ExcelManagement.Direct
{
///

/// Description of TimedAccess.
///

public class ExcelAccess
{
#region Private Variables
ApplicationClass app;
Workbook book = null;
Worksheet sheet = null;
Range range = null;
#endregion
#region Constructor
public ExcelAccess(string _xlsFile, int _xlsFileSheetIndex)
{
app = new ApplicationClass();
app.Visible = false;
app.ScreenUpdating = false;
app.DisplayAlerts = false;
string execPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
book = app.Workbooks.Open(execPath + @"\..\..\" + _xlsFile, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value);
sheet = (Worksheet)book.Worksheets[_xlsFileSheetIndex];
}
#endregion
#region ChangeSheetIndex Method
public void ChangeSheetIndex(int SheetIndex)
{
sheet = (Worksheet)book.Worksheets[SheetIndex];
}
#endregion

#region ChangeSheetToSheetName Method
public void ChangeSheetToSheetName(string NewSheetName)
{
System.Collections.IEnumerator s = ((System.Collections.IEnumerator)book.Worksheets.GetEnumerator());
int i=1;
while (s.MoveNext())
{
if (((Worksheet)s.Current).Name == NewSheetName)
{
sheet = (Worksheet)book.Worksheets[i];
return;
}
i++;
}
}
#endregion
#region Read Range Method(Based on the Starting Cell).
public object[,] ReadRange(string StartingCell)
{
try
{
range = sheet.get_Range(StartingCell, Missing.Value);
range = range.get_End(XlDirection.xlToRight);
range = range.get_End(XlDirection.xlDown);
string downAddress = range.get_Address(
false, false, XlReferenceStyle.xlA1,
Type.Missing, Type.Missing);
range = sheet.get_Range(StartingCell, downAddress);
return (object[,])range.Value2;
}
catch (Exception e)
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
return null;
}
}
#endregion
#region Read Range Method.
public object[,] ReadRange(string StartingCell, string EndingCell)
{
try
{
// get a range to work with
range = sheet.get_Range(StartingCell, EndingCell);
return (object[,])range.Value2;
}
catch (Exception e)
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
return null;
}
}
#endregion
#region Read Cell Method
public string ReadCell(string cellName)
{
try
{
range = sheet.get_Range(cellName, Missing.Value);
if (range.Value2 != null)
{
return range.Value2.ToString();
}
else
{
return string.Empty;
}
}
catch (Exception e)
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
return null;
}
}
#endregion
#region Dispose Method
public void Dispose()
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
}
#endregion
}
}

Calling code


ExcelManagement.Direct.ExcelAccess _access = new ExcelManagement.Direct.ExcelAccess("Book1.xls", 2);
MessageBox.Show(_access.ReadCell("b1"));
_access.ChangeSheetIndex(1);
_access.ChangeSheetToSheetName("Sheet1");
object[,] values = _access.ReadRange("A1", "b2");
for (int i = 1; i <= values.GetLength(0); i++)
{
for (int j = 1; j <= values.GetLength(1); j++)
{
if (values[i, j] != null)
{
MessageBox.Show(values[i, j].ToString());
}
else
{
MessageBox.Show("");
}
}
}
_access.Dispose();

Accessing Xls Sheet Data from .NET Application

Some time back i was in need to fetch some data from the Excel sheet from .NET Application. I have done some R & D on the Net and found some great examples of the same then i created my own sample component which will serve you like the basis component and you can enhance the same futher as per your need.

/*
* Created by Dalip Vohra
*/

using System;
using System.IO;
using System.Data;
using System.Data.OleDb;
namespace ExcelManagement.OleDB
{
///

/// Description of TimedAccess.
///

public class ExcelAccess
{
#region Private Variables
string connString;
OleDbConnection oledbConn;
#endregion

#region Constructor
public ExcelAccess(string _xlsFile, string _xlsFileSheetName)
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../../Book2.xls;Extended Properties=Excel 8.0";
oledbConn = new OleDbConnection(connString);
try
{
// Open connection
oledbConn.Open();
}
catch
{
throw (new Exception("Connection Exception"));
}
}
#endregion

#region GetSheetData Method
public DataSet GetSheetData(string SheetName)
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + SheetName + "$]", oledbConn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
DataSet ds = new DataSet();
oleda.Fill(ds, "RecordsTable");
return ds;
}
#endregion

#region GetRangeFromSheet Method

public DataSet GetRangeFromSheet(string SheetName, String StartingCell, String EndingCell)
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + SheetName + "$" + StartingCell + ":" + EndingCell + "]", oledbConn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
DataSet ds = new DataSet();
oleda.Fill(ds, "RecordsTable");
return ds;
}
#endregion

#region Dispose Method

public void Dispose()
{
connString = null;
oledbConn.Close();
}
#endregion

}
}

Infinium System Handling Via Net Manage.

I found 3 ways to handle the Infinium System via Net Manage.

One is to write the pure script which is bit difficult to write but easy to debug.
Second is to scrap and record the screen one by one.
Third is the mixture of the above 2.

So second approach is easy to create but difficult to debug.

I thing the mixture of both the approaches are the best way to achieve the targets.

Net Manage

Hi Guys,

These days I was so busy as I was working on a new tool called Net Manage and truly speaking I find it very interesting because its like a different experience.

Net Manage is use to interact with the existing Infinium System. Its like automating the task user can perform on that system and expose those task like a WebService for .Net Developer and Java Beans for Java Developers.

So, developer is able to integrate the Existing Infinium System with Java/.NET.

The concept of Net Manage is like a concept of reading the dimensions of the screen and performing the keystoke on the particular dimensions to move to the next screen.

It saved a lot of work otherwise one has to understand the whole database structure to integrate the existing infinium system with the new building system which is in process.