Ritesh Kesharwani

Tuesday, July 20, 2010

Search text values in All Tables and the SPs in same database

Search text values in All Tables and the SPs in same database

This stored procedure/SQL Statement developed/tested in SQL Server 2008 and it has all backward compatability.

· Search text value in All the Stored Procedure in same database

Following statement will search text value in all the SP created in the same database.

SELECT * FROM SYSCOMMENTS WHERE TEXT LIKE'%<search Value>%'

· Search text values in All tables in same database

This SP will search text string in all the database tables in all the columns.

Ex: If you want to search one text like 'Country name: USA' then you run following SP and execute like

Exec SearchInAllTables 'Country name: USA'

You will get all the table name and related column name where this text exists.

USE [Database name]

CREATE PROCEDURE SearchInAllTables

(

@searchString NVARCHAR(1000)

)

BEGIN

CREATE TABLE #OutputResults (ColumnNa NVARCHAR(500), ColumnVal NVARCHAR(4000))

SET NOCOUNT ON

DECLARE @tableNa NVARCHAR(300), @columnNa NVARCHAR(200), @searchString2 NVARCHAR(1000)

SET @tableNa = ''

SET @searchString2 = QUOTENAME('%' + @searchString + '%','''')

WHILE @tableNa IS NOT NULL
BEGIN

SET @columnNa = ''

SET @tableNa =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @tableNa

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

), 'IsMSShipped'

) = 0

)

WHILE (@tableNa IS NOT NULL) AND (@columnNa IS NOT NULL)

BEGIN

SET @columnNa =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@tableNa, 2)

AND TABLE_NAME = PARSENAME(@tableNa, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

AND QUOTENAME(COLUMN_NAME) > @columnNa

)

IF @columnNa IS NOT NULL

BEGIN

INSERT INTO #OutputResults

EXEC

(

'SELECT ''' + @tableNa + '.' + @columnNa + ''', LEFT(' + @columnNa + ', 3630)

FROM ' + @tableNa + ' (NOLOCK) ' +

' WHERE ' + @columnNa + ' LIKE ' + @searchString2

)

END

SELECT ColumnNa, ColumnVal FROM #OutputResults

END

Tuesday, July 13, 2010

Error: Unrecognized attribute 'targetFramework'. (Window 7)

Error: Unrecognized attribute 'targetFramework'. (OS- Window 7)

I am using visual studio 2010, IIS 6.1 and OS is window 7, when I create web service using visual studio 2010 and run I am getting this error, after couple of research I found that this error causing because of web service created for .NET Framework 4.0 but IIS is pointing it to .NET Framework 2.0.

You can do the following steps to point your application into .NET framework 4.0

1) Open IIS (type inetmgr on Run window)

2) Go to "Application Pool"

3) Select application folder and double click

4) On the "Edit Application Pool" popup change .NET Framework 2 to 4.

5) Restart IIS

Thursday, July 08, 2010

“Manage Content and Structure” is not visible on Share Point “Site Actions”

"Manage Content and Structure" is not visible on Share Point "Site Actions"

1) Go to "Site Action"

2) Click on "Site Settings"

3) On the "Site Actions" section go to

4) Click on "Manage site features" and

5) Activate "SharePoint Server Publishing"

Now go to the "Site Action" again you will see one more option "Manage Content and Structure"

Note: If you get Runtime error while clicking on "Manage Content and Structure" then do the following steps

1) Click on "Site Actions"

2) Go to "Site Settings"

3) Go to the "Site Collection Administration"

4) Click on "Site Collection features"

5) Activate "SharePoint Server Publishing Infrastructure"

Now again click on "Manage Content and Structure" you will not get any runtime error.

Tuesday, June 01, 2010

Error: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

Error: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

I got this error when I tried to run my .NET application into VS 2010 with 64 bit OS/machine, to resolve this issue we have to forcefully set application setting to run this application for 32 bit, to do that please follow the steps below

1) Right click on the project and click on the properties

2) Go to the Build tab

3) Set Platform target from "Any CPU" to x86

4) Now run your application again, you will not get this error again.

Sunday, March 21, 2010

Error: ASP.NET Ajax client-side framework failed to load. OR 'Sys' is undefined.

Error: ASP.NET Ajax client-side framework failed to load. OR 'Sys' is undefined. in VS 2008

Cause:

I got this error very first time when I tried to use AJAX ScriptManager object using VS2008.

I was tring to registred script using ScriptManager like below

protected void Button1_Click(object sender, EventArgs e)

{

ScriptManager.RegisterStartupScript(

this,this.GetType(),"script","alert('Hi');",true);

}

Solution:

I found there are two way to solve this error

1) You need to add httpHandlers into the web.config below "compilation" tag

Code:

<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions,

Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>

</httpHandlers>

2) You can solve this error by using RegisterAsyncPostBackControl method

Using beolw code button control will not have any page refresh like a client control.

Code:

protected void Page_Load(object sender, EventArgs e)

{

// register the Button1 control as one that can cause partial postbacks.

ScriptManager1.RegisterAsyncPostBackControl(Button1);

}

Note: I may not get this error in latest version/patch of VS2008.

Wednesday, March 10, 2010

Combo box Auto Complete Property in C# and VB using .NET

Combo box Auto Complete Property in C# and VB using .NET

When you have a requirement for combo box control like: when user types some text/number then combo box should display/select value as per user typing the characters from keyboard.

Ex: If Combo Box having following items (Source code load the combo box value)

ComboBox1.Items.Add("ABC123")

ComboBox1.Items.Add("AABC123")

ComboBox1.Items.Add("AAABC123")

ComboBox1.Items.Add("AAAABC123")

ComboBox1.Items.Add("CCCEF123")

ComboBox1.Items.Add("1123ABC")

ComboBox1.Items.Add("2234BCD")

ComboBox1.AutoCompleteSource = AutoCompleteSource.ListItems

ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList

and requirement is when user type in the combo box the relative text should display

Ex: When user type "A" then combo box should select "ABC123" value from the list

When user type "AA" then combo box should select "AABCD123" value from the list

When user type "AAA" then combo box should select "AAABC123" value from the list

etc.

To achieve above requirements, very simple in .NET to use "ComboBox1.AutoCompleteMode" property, it has 4 mode and default is "None"

1) AutoCompleteMode.Append

This Mode will append the value as user type the character one by one and select the nearest value in the combo box

2) AutoCompleteMode.Suggest

This Mode will select the nearest value in the combo box when user type ONLY first characher in the combo box and will open (Drop) the combo box to suggest the other nearest possible value present in the combo box

Ex:

3) AutoCompleteMode.SuggestAppend
This Mode will append the value as user type the characters one by one and select the nearest value in the combo box and also will open (Drop) the combo box to suggest the other nearest possible value present in the combo box
4) AutoCompleteMode.None (Default)
This Mode will select the nearest value in the combo box when user type ONLY first characher in the combo box

Tuesday, March 09, 2010

How to create ASP.NET Web Parts for Windows SharePoint Services 3.0

Good video to learn how to create web part using ASP.net 3.0.

Get idea about web part and how to use in Share Point Service 3.0.

Full screen

http://mschnlnine.vo.llnwd.net/d1/ch9/2/2/4/7/8/1/192071_AspWebPartsForWss3.wmv

Saturday, March 06, 2010

What is “unsafe” Keyword in C#.NET

What is "unsafe" Keyword in C#.NET

In C# the value can be directly referenced to a variable, so there is no need of pointer. Use of pointer sometime crashes the application. But C# supports pointer and we can use pointer in C#.

The use of pointer in C# is defined as an unsafe code. So if we want to use pointer in C# the pointer must be present in the body of unsafe declaration. But pointer does not come under garbage collection.

Example:-

unsafe

{

int firstVariable;

int* secondPointerVariable;

firstVariable = 25;

secondPointerVariable = &firstVariable;

Console.WriteLine("secondPointerVariable = {0}", secondPointerVariable);

//returns secondPointerVariable = 25

}

Note: you may get compilation error ("Unsafe code may only appear if compiling with /unsafe") while compiling the above code.

If you' are compiling from the command line then include the /unsafe switch as the error suggests.
If you're compiling from Visual Studio, then look in your project's properties and, under the build tab, check the 'Allow unsafe code' box. It should work fine.

How to make an application offline in ASP.NET

How to make an application offline in ASP.NET

Microsoft's Internet Information Services (IIS) web server software is used to make an application offline. The IIS is instructed to route all incoming requests for the web site to another URL automatically.

Steps for application configuration:

From IIS manager -> right-click on the application -> choose Properties.
Choose Virtual Directory tab and indicate that the content for this application should come from "A redirection to a URL."
Then enter the same URL to redirect in "Redirect to".
Check the box "The exact URL entered above.

Web Service Vs WCF (Window Communication Foundation)

Web Service Vs WCF (Window Communication Foundation)

WCF is a set of .NET Framework-based technologies for building and running services. WCF is the next step in the evolutionary path that started with COM, COM+, and MSMQ, and continues through .NET Remoting, ASMX, System.Messaging, and .NET Enterprise Services. WCF combines these technologies and offers a unified programming model for developing services using any CLR-compliant language.

Feature	ASP.NET Web Service	WCF
Performance	Slower compared to WCF	The main benifit of the design of the DataContractSerializer is better performance over XML serialization
File Extension	File extention is asmx	File extension is .svc
Data Transformation	To and from Data Transition is done through XML Serializer	DataContractSerializer is used for data transition
Webmethods vs DataContract Attributes	ASP.NET WebService uses Webmethods to to translate .NET FW types in to XML.	The WCF uses the DataContractAttribute and DataMemeberAttribute to translate .NET FW types in to XML.
Limitations	Only Public fields or Properties of .NET types can be translated into XML.Only the classes which implement IEnumerable interface, ICollection interface can be serializable	Public/Private fields or properties of .NET types can be translated
IDictionary Interface class	Classes that implement the IDictionary interface, such as Hash table can not be serialized.	The DataContractSerializer can translate the Hash Table into XML. Hence using WCF we can even translate HashTable into XML
Know which fields /properties	XMLSerialization does not indicate the which fields or properties of the type are serialized into XML	DataCotratSerializer Explicitly shows the which fields or properties are serialized into XML
Exception handling	In ASP.NET Web services, Unhandled exceptions are returned to the client as SOAP faults.	In WCF Services, unhandled exceptions are not returned to clients as SOAP faults. A configuration setting is provided to have the unhandled exceptions returned to clients for the purpose of debugging.
Example	[WebService] public class Service : System.Web.Services.WebService { [WebMethod] public string Demo(string strDemog) { return strDemo; } }	[ServiceContract] public interface ITest { [OperationContract] string Demo (string strDemo); } public class Service : ITest { public string Demo(string strDemo) { return strDemo; } }

Learn more about WCF

http://www.xvpj.net/2008/03/08/wcf-step-by-step-tutorial/

http://www.15seconds.com/Issue/061130.htm