ER Source Code 4 U

STUFF IN MS SQL, Getting data from table in a single row separated by comma or other sign

2010-08-05T21:17:00.000+06:00

Consider following SQL statement for MS SQL

Select UserName from UserInfo

It will return

UserName

Anis

Hasibul

If you want to get these record in following way

UserName

Anis, Hasibul

Then you have to use STAFF
Consider following example of staff for getting above data

SELECT STUFF
(
 (Select TOP 100 PERCENT ',' + UserName from UserInfo FOR XML PATH(''))
 ,1
 ,1
 ,' '
) as UserName

Basic Structure for STUFF Statement
STUFF ( char_exp, start, length, char_exp_to_stuff)
Consider other examples

select stuff('Hello Again',1,0, 'World')
-- WorldHello Again
select stuff('Hello Again',1,1, 'World')
-- Worldello Again
select stuff('Hello Again',7,0, 'World ')
-- Hello World Again
--Ex: Remove a character
select stuff('Hello*World',charindex('*','Hello*World'),1,'')
-- returns: Hello World

Split in MS SQL SERVER

2010-06-30T09:21:00.000+06:00

There is no built in function in MS SQL Server;

Following function return Table valued data after spliting

-----------------------------------------------------------------------------------------------------

--select * from fnSplit('1,22,333,444,,5555,666', ',')

CREATE FUNCTION dbo.fnSplit(

@sInputList VARCHAR(8000) -- List of delimited items

, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items

) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN

DECLARE @sItem VARCHAR(8000)

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0

BEGIN

SELECT

@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),

@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

IF LEN(@sItem) > 0

INSERT INTO @List SELECT @sItem

END

IF LEN(@sInputList) > 0

INSERT INTO @List SELECT @sInputList -- Put the last item in

RETURN

END

-----------------------------------------------------------------------------------------------------

Following function Return specific splitted data
------------------------------------------------------------------------------------------------------

-- SELECT dbo.fnSplit('4.55.108.2','.', 2)

ALTER FUNCTION [dbo].[fnSplit]

(

@Expression NVARCHAR(max)

, @Delimiter NVARCHAR(max)

, @INDEX INT

)

RETURNS NVARCHAR(max)

BEGIN

DECLARE @RETURN NVARCHAR(max)

DECLARE @Pos INT

DECLARE @PrevPos INT

DECLARE @I INT

IF @Expression IS NULL OR @Delimiter IS NULL OR LEN(@Delimiter) = 0 OR @INDEX < 1

SET @RETURN = NULL

ELSE IF @INDEX = 1 BEGIN

SET @Pos = CHARINDEX(@Delimiter, @Expression, 1)

IF @Pos > 0 SET @RETURN = LEFT(@Expression, @Pos - 1)

END ELSE BEGIN

SET @Pos = 0

SET @I = 0

WHILE (@Pos > 0 AND @I < @INDEX) OR @I = 0 BEGIN

SET @PrevPos = @Pos

SET @Pos = CHARINDEX(@Delimiter, @Expression, @Pos + LEN(@Delimiter))

SET @I = @I + 1

END

IF @Pos = 0 AND @I = @INDEX

SET @RETURN = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), LEN(@Expression))

ELSE IF @Pos = 0 AND @I <> @INDEX

SET @RETURN = NULL

ELSE

SET @RETURN = SUBSTRING(@Expression, @PrevPos + LEN(@Delimiter), @Pos - @PrevPos - LEN(@Delimiter))

END

RETURN @RETURN

END

------------------------------------------------------------------------------------------------------

The maximum report processing jobs limit configured by your system administrator has been reached

2010-06-22T09:58:00.000+06:00

This is very common error when crystal report printing job limit over

We can solve this problem using two way:
1. Need to write following code on CrystalReportViewer Unload event
   a. Perform reportDocument Deispose
   b.Perform reportDocument Clone
   c.Perform reportDocument Close
d. Perform CrystalReportViewer Dispose

Code
============

rd.Dispose(); //Here rd is Report Document which is Initialized before

rd.Clone();

rd.Close();

CrystalReportViewer1.Dispose();

2. We can change the os register entry
a. Go to run and write regedit
b. Now go to HKEY_LOCAL_MACHINE\SOFTWARE\CRYSTAL DECISIONS\10.0\REPORT APPLICATION SERVER\SERVER\PrintJobLimit
c. You will find it 75.
d. Now set it -1 for unlimited printing job.

Cannot access shared files and folders or browse computers

2010-06-04T12:49:00.001+06:00

Most of the time I am unable to access shared files and folder from other pc. There is a service named Computer Browser stopped automatically I dont know why. But I found a solution from Microsoft site.

Here is the solution:

At first do following:
----------------------
i. Right-click My Computer > then click Manage.
ii. Double-click Services and Applications.
iii. Double-click Services.
iv. On the right side, right-click Computer Browser, and then click Start.
v. Close the Computer Management window.

Now try to access your shared files and folder if It doesnt work then do following.

Step 1: Enable NetBIOS over TCP/IP
-----------------------------------
i. Click Start, click Control Panel, and then click Network and Internet Connections.
ii. Click Network Connections.
iii. Right-click Local Area Connection, and then click Properties.
iV. Click Internet Protocol (TCP/IP), and then click Properties.
v. Click the General tab, and then click Advanced.
vi. Click the WINS tab.
vii. Under NetBIOS setting, click Enable NetBIOS over TCP/IP, and then click OK two times.
viii.Click Close to close the Local Area Connection Properties dialog box.
iX. Close the Network Connections window.

step 2: Enable Computer Browser
-----------------------------------
i. Right-click My Computer > then click Manage.
ii. Double-click Services and Applications.
iii. Double-click Services.
iv4. On the right side, right-click Computer Browser, and then click Start.
v. Close the Computer Management window.

Hope It will solve your Problem.

Filter data by date time in MS access, Select only date without selecting time from date time field in MS Access

2010-05-16T10:07:00.000+06:00

Select * from TransactionInfo where CDate(Int([CreatedDate])) between #2010-05-04# and #2010-05-05#
-----------------
it will return date like
-----------------

23-May-2010

Select * from TransactionInfo where [CreatedDate] between #2010-05-04# and #2010-05-05#

-----------------
it will return
-----------------

2010/05/23

How to enable Paging in GridView in ASP.net or Grid View Paging doesn’t work

2010-05-12T10:37:00.000+06:00

Do Following steps.

--> Set EnablePaging and Sorting Callback = True from Propertise.

Set your data source as general way.

Now do another extra job for working paging

Write down the following code under PageIndexGhanging Event

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

//Create your data set or table as u did

GridView1.PageIndex = e.NewPageIndex;

GridView1.DataSource = ds;

GridView1.DataBind();

}

Accounting Dr and Cr

2010-05-03T13:17:00.000+06:00

Dr: Ja nai sa hossa Debit.
Cr: Ja Dai sa hossa Credit.

Balance Calculation:
============================

Income/Liability
-------------------
OB+Cr-Dr OB=(Cr - Dr)
if result is (+) then Cr
if result is (-) then Dr

Asset/Expenses
-------------------
OB+Dr-Cr OB = (Dr - Cr)
if result is (+) then Dr
if result is (-) then Cr

Export to pdf

2010-04-30T22:19:00.000+06:00

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace WebApplication1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {


            MemoryStream oStream;
            Response.Clear();
            Response.Buffer = true;
            ReportDocument rd = new ReportDocument();

            rd.Load(Server.MapPath("cr1.rpt"));

            oStream = (MemoryStream)rd.ExportToStream(ExportFormatType.PortableDocFormat);
            Response.ContentType = "application/pdf";

            Response.BinaryWrite(oStream.ToArray());
            Response.End();
            oStream.Flush();
            oStream.Close();
            oStream.Dispose();
        }
    }
}

Finding out difference between two day, calculating day difference between two different day

2010-04-18T11:53:00.000+06:00

Here is the function for finding out difference

select datediff(day,'2010-04-01','2010-04-20')

datediff('day or month or year', FormDate, Todate)

Delete All data Rows from Data Base

2010-04-15T18:04:00.001+06:00

CREATE PROCEDURE sp_EmplyAllTable

AS

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'DELETE FROM ?'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

IIS date time format doesn’t work (date time format for IIS)

2010-04-15T17:05:00.000+06:00

Here is the solution:

Go to Run (Start > Run )
write regedit
Then select HKEY_USERS
Then select Any User Class (It may be default or S-1-5-18)
Then Control Panel
Then Select International
Now from the right side windo

double click sShortDate then set it “dd/MM/yyyy”
double click sLongDate then set it “dd MMMM yyyy”

Do above for All user class if you dont know your user class.

After completing this Restart your IIS

For Restarting IIS

Go to Control Panel
Then select “Administrative Tools”
Then Select Internet Information Service
Then Select Local Computer Node
Now right click and Select “All Tasks” >> Restart IIS >> Restart IIS Service >> OK
Now Check You IIS.

Hope It will work Now.

Reset Identity Column Value in SQL Server

2010-04-06T03:06:00.000+06:00

It would be wise to first check what the current identify value is. We can use this command to do so:

DBCC CHECKIDENT (’tablename’, NORESEED)

For instance, if I wanted to check the next ID value of my orders table, I could use this command:

DBCC CHECKIDENT (orders, NORESEED)

To set the value of the next ID to be 1000, I can use this command:

DBCC CHECKIDENT (orders, RESEED, 999)

Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000.
Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it. (which it really shouldn’t)

DBCC CHECKIDENT ( ‘databasename.dbo.orders’,RESEED, 999)

Clear All text Box from A Form

2010-03-25T23:16:00.000+06:00

This is very simple:
--------------------
foreach (Control ctrl in kryptonNavigator1.Pages[0].Controls)
            {
                if (ctrl is TextBox )
                {
                    ((TextBox)ctrl).Text = "";
                }
            }

Create DTO Automatically

2010-03-23T23:04:00.000+06:00

//Global Variable

string str_output = "";

string[] input = txtIn.Text.Split('\n');

foreach (string s in input)

{

if (s != "")

str_output = str_output + "string str_" + s.Trim()+";\n";

}

foreach (string q in input)

{

if(q!="")

str_output = str_output + " public string "+q.Trim() +" \n {\n get \n {\n return str_"+q.Trim ()+";\n }\n set \n {\n str_"+q.Trim() +" = value;\n }\n }\n\n";

}

Difference between Variable Table(@) and Temp(#) table

2010-02-15T11:32:00.000+06:00

Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.
Temp tables behave same as normal tables and are bound by transactions.

Consider Following Example
-----------------------------------------

BEGIN TRAN

declare @var table (id int, data varchar(20) )

create table #temp (id int, data varchar(20) )

insert into @var

select 1, 'data 1' union all

select 2, 'data 2' union all

select 3, 'data 3'

insert into #temp

select 1, 'data 1' union all

select 2, 'data 2' union all

select 3, 'data 3'

select * from #temp

select * from @var

ROLLBACK

select * from @var

if object_id('tempdb..#temp') is null

select '#temp does not exist outside the transaction'

Different types of SQL Command ( DDL, DML, DCL and TCL) // Difference between DML, DDL and DCL

2010-01-10T22:54:00.000+06:00

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command

TCL
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Calculate Execution and Cpu Time in SQL 2005

2010-01-07T10:21:00.000+06:00

SET STATISTICS TIME ON
Select * from vItemReceiveRegister
SET STATISTICS TIME OFF

It Results:
===============
SQL Server parse and compile time:
CPU time = 31 ms, elapsed time = 141 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(49 row(s) affected)

SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 354 ms.

Cursor in SQL 2005

2009-12-08T10:35:00.000+07:00

declare db_cursor CURSOR for

SELECT ItemSLNO FROM P_Return_ItemSLNOTraking where Com_PReturnId_ItemId = @Com_ReceiveId_ItemId

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @ItemSLNO

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SLNO = @SLNO+ ','+ @ItemSLNO

FETCH NEXT FROM db_cursor INTO @ItemSLNO

END

CLOSE db_cursor

DEALLOCATE db_cursor

Adding value and Display member in ComboBox

2009-11-15T12:30:00.000+07:00

cmbIsReceive.Items.Clear();
cmbIsReceive.Items.Add(new ListItem("Item Not Received.0", "0"));
cmbIsReceive.Items.Add(new ListItem("Item Received.1", "1"));

Creating table data Script in SQL 2005

2009-10-26T14:23:00.000+07:00

Here is the site for downloading SQL script

http://vyaskn.tripod.com/code.htm#inserts

using following code for creating Table data Script
---------------------------------------------------

exec sp_generate_inserts 'sys_user'

IIS FOR Crystal Report ToolBar Image

2009-09-10T23:47:00.000+07:00

use following Thing for Crystal Report Toolbar image; if it doesnt appear

Go to Start->Programs->Microsoft Visualstudio 2008->Visual Studio Tools->Visual Studio 2008 Command Prompt

and type

aspnet_regiis -c

Case in SQL Server 2005

2009-09-05T16:52:00.000+07:00

Select
BM.TotalAmount, BM.PaidAmount,BM.TotalAmount - BM.PaidAmount,
case when (BM.TotalAmount <= BM.PaidAmount) then 'Paid' else 'Unpaid' end Stat FROM BillMain BM another example Select (case InventoryValuationMethod when 'Default' then (Select sys_Value from sys_Default where sys_Parameter = 'InventoryValuationMethod') else InventoryValuationMethod end from ItemInfoBranch)

Multiple case in One statement

select * from (
select id,[First name],[Last name], salary, Timezone = case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in
('NY','NJ','VT','ME','NH','MA','RI','CT','PA','DE','MD',
'DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') then 'Eastern'
when state in
('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in
('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA',
'WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Hawaii' end
from emp) as mytype where TimeZone in ('Mountain','eastern')

SQL 2005 ERROR Solving ( Unable to cast COM object of type 'System.__ComObject' to interface type )

2009-08-28T11:13:00.000+07:00

Here is the error in details:
--------------------------------


Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.VisualStudio.OLE.Interop.IServiceProvider'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{6D5140C1-7436-11CE-8034-00AA006009FA}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

Solution:
==========
1. Need to create a registry file and marge it

[Copy from here]




Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}]
@="IServiceProvider"

[HKEY_CLASSES_ROOT\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}\NumMethods]
@="4"

[HKEY_CLASSES_ROOT\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}\ProxyStubClsid32]
@="{B8DA6310-E19B-11D0-933C-00A0C90DCAA9}"

[HKEY_CURRENT_USER\Software\Classes\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}]
@="IServiceProvider"

[HKEY_CURRENT_USER\Software\Classes\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}\NumMethods]
@="4"

[HKEY_CURRENT_USER\Software\Classes\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}\ProxyStubClsid32]
@="{B8DA6310-E19B-11D0-933C-00A0C90DCAA9}"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}]
@="IServiceProvider"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}\NumMethods]
@="4"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Interface\{6D5140C1-7436-11CE-8034-00AA006009FA}\ProxyStubClsid32]
@="{B8DA6310-E19B-11D0-933C-00A0C90DCAA9}"

[Copy ends here]

--> Then create a txt file and rename to .reg and save.
--> Then right click that file and select marge.

Hope it will solve your problem.

If Not then do second way.

Solution 2:
=============
Register the actxprxy.dll in system 32 using following way.

--> Go to command prompot
-->Then write regsvr32.exe actxprxy.dll
--> Then press enter hope it will solve your Problem.

Inserting Value & Text in ComboBox

2009-08-19T17:02:00.001+07:00

Required Library: System.Web.UI.WebControls

Sample Code:


ComboBox1.Items.Add(new ListItem("TxtMem","0"));
ComboBox1.Items.Add(new ListItem("TxtMem1","1"));

MessageBox in VWG

2009-08-18T12:29:00.000+07:00

Simple MessageBox:

=======================

private void button1_Click(object sender, EventArgs e)

{

MessageBox.Show("R u want to delete", "Query", MessageBoxButtons.YesNo, MessageBoxIcon.Question, new EventHandler(MessageHandeler));

}

private void MessageHandeler(Object sender, EventArgs e)

{

if (((Form)sender).DialogResult == DialogResult.Yes)

{

MessageBox.Show("Yes");

}

else

{

MessageBox.Show("No");

}

url:

http://www.visualwebgui.com/Developers/Resources/VideoTutorials/tabid/320/articleType/ArticleView/articleId/41/How-to-use-the-MessageBox-class.aspx

Creating Message Box Using Form

=====================================

Calling Form To Show

-----------------------

private void button2_Click(object sender, EventArgs e)

{

msg omsg = new msg();

omsg.Closed += new EventHandler(Closed_Form);

omsg.Show();

}

Creating Event Handler

-----------------------

private void Closed_Form(object sender, EventArgs e)

{

msg omsg = sender as msg;

if (omsg.isClosed == true)

{

MessageBox.Show(omsg.Value_txt);

}

Child Form

-----------------------

Download Source Code:

http://zuyi8w.bay.livefilestore.com/y1pOw9tbLxzpeSQyU8vfE9hPn-7_45wdC7SdW_uDpF40Q8YSW6mNtcMk1hhMtJ539InqjVrMc_LXnAmawvMGRTEkguPUCe7BOPX/MessageBox_VWG_6.2.4.zip?download

url:

http://www.visualwebgui.com/Developers/Resources/VideoTutorials/tabid/320/articleType/ArticleView/articleId/70/How-to-return-parameters-from-dialogs.aspx