Tuesday, June 29, 2010

SQL DATE FORMAT

You might have comeacross, situations where the date doesn't show the way you want. Hope this table will help you to get sorted out this issue.






























































































DATE FORMATS
Date Format Query (current date: 12/30/2006) Show
1 select convert(varchar, getdate(), 1) 12/30/06
2 select convert(varchar, getdate(), 2) 06.12.30
3 select convert(varchar, getdate(), 3) 30/12/06
4 select convert(varchar, getdate(), 4) 30.12.06
5 select convert(varchar, getdate(), 5) 30-12-06
6 select convert(varchar, getdate(), 6) 30 Dec 06
7 select convert(varchar, getdate(), 7) Dec 30, 06
10 select convert(varchar, getdate(), 10) 12-30-06
11 select convert(varchar, getdate(), 11) 06/12/30
101 select convert(varchar, getdate(), 101) 12/30/2006
102 select convert(varchar, getdate(), 102) 2006.12.30
103 select convert(varchar, getdate(), 103) 30/12/2006
104 select convert(varchar, getdate(), 104) 30.12.2006
105 select convert(varchar, getdate(), 105) 30-12-2006
106 select convert(varchar, getdate(), 106) 30 Dec 2006
107 select convert(varchar, getdate(), 107) Dec 30, 2006
110 select convert(varchar, getdate(), 110) 12-30-2006
111 select convert(varchar, getdate(), 111) 2006/12/30
Friday, June 25, 2010

SQL Split Function

I was just trying to do a split in a stored procedure, and found this
link. This is really helpfull comparing with other resources available on the web.

http://sqltutorials.blogspot.com/2007/09/sql-function-split.html

Below is Split Function in SQL

DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)

SET @String ='SQL,TUTORIALS'
SET @Delimiter = ','
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END

Result
- SQL
- TUTORIALS
Sunday, May 23, 2010

Dotnetnuke System.Security.SecurityException: Request for the permission of type 'System.Web.AspNetHostingPermission

 

I got this error when I try to install Dotnetnuke 05.04.02 on my computer on Wndows 7 and IIS7. There was no proper solution for this on the web, but I just change the application pool identity to NetworkService and application worked as expected.

Steps :
Go to IIS and select Application Pools.
Select the DNN web pool on right hand pane.
On Actions press Set Application Pool Default link.
Change the Identity under Process Model to NetworkService

ApplicationPool

Wednesday, May 19, 2010

Use a Web Proxy for Cross-Domain XMLHttpRequest Calls


I had an issue when calling web service through AJAX from 3rd party URL.
Here is the solution

http://developer.yahoo.com/javascript/howto-proxy.html

Friday, May 07, 2010

Using JQuery on blogspot

As I use JQuery these days, I just though how I can use JQuery on my blog.
I tried to do this and found It’s very easy to use it with blogger. Just a few steps
make this possible. You might be wondering where you can place the JQuery file in
blogger. Google has host these file on the following path “http://ajax.googleapis.com/ajax/libs/jquery/”. Just have to select the version you prefer to use.

Just see the following steps.

Step 1:
Add the following code between header tags of your blog’s HTML.

<
head>
<
script src='http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js' type='text/javascript'/>
</
head>

Example:
1

Step 2:
Add the functions in between header tags as follows,

<
head>
<
script src='http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js' type='text/javascript'/>

<
script type="text/javascript">
    function
MouseOn() {
        $(document).ready(function() {
        $("img").toggleClass("newClass");         
            //$("#testPara").append("Shiran the greatest" + count );

       
});
    }
</script>
</
head>

Example :
22

 

Step 3: (Optional)
If there are any CSS involved embed them also in between header tags as follows

<
head>
<
script src='http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js' type='text/javascript'/>

<
script type="text/javascript">
    function
ChangeCSS() {
        $(document).ready(function() {
             //here this will toggle CSS classes on an event
 
        $("img").toggleClass("newClass");       
          
       
});
    }

<style type="text/css">
.toggler {width: 100px; height: 200px;}

.newClass {width: 500px; height: 866px;}
</style>

</script>
</
head>


Need to call the above ChangeCSS() JS function where this need to be done.


Example:
this maight be in post or a HTML/JavaScript gadget

<img class="toggler" src="http://1.bp.blogspot.com/_8fX3E/S-L0RblzI/AAAADtQ/nB8kJYc/s200/108_f0.jpg" onmouseover="ChangeCSS()"/>


Give your visitors a shock.

Thursday, May 06, 2010

Enable JQuery intellisense in VS 2008

I just started to do some JQuery on one of my VS projects. I found that we can have intellisense on VS2008. To make this available I had to do some installations and follow few steps to get intellisense on VS2008, I hope these would helpful you as well in projects.

Step 1:
Install VS 2008 SP1, you can get it here

Step2:
Install VS 2008 Patch KB958502, you can get it here

Step3:
Download JQuery js file and JQuery vsdoc.js, both these should be one version
Eg: jquery-1.4.1-vsdoc.js and jquery-1.4.1.js (version may be differ on the version you have downloaded).
Put both files in the same place in your project.

JQuery

now you are ready to use. You will get intellisense and auto complete features on VS2009

Tuesday, April 27, 2010

using statement in C#

This is a nice little statement which reduces our coding and minimises prone to errors.
There are many situations we have to Dispose the object we created. Example

string connString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=MyDB;";

SqlConnection myConnect = new SqlConnection(connString);
SqlCommand cmd = myConnect.CreateCommand();
cmd.CommandText = "SELECT * FROM MyTable";

myConnect.Open();

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
Console.WriteLine("{0}\t{1}", dr.GetString(0), dr.GetString(1));
}

dr.Dispose();
myConnect.Close();
myConnect.Dispose();

Here on above example I just try to connect to a database and take out all records from MyTable.


I have created a SqlConnection object named myConnect  and a SqlDataReader object named dr, at the very bottom of the code snippet I have disposed those two objects using Dispose() method.


What will happen if I forget to dispose these objects? memory issues, using statement is a remedy for this and its makes code more readable, robust and clean. I will recode the above code snippet with using “using ” statement.

using (SqlConnection myConnect = new SqlConnection(connString))
{
SqlCommand cmd = myConnect.CreateCommand();
cmd.CommandText = "SELECT * FROM MyTable";

myConnect.Open();

using (SqlDataReader dr = cmd.ExecuteReader())
{

while (dr.Read())
{
Console.WriteLine("{0}\t{1}", dr.GetString(0), dr.GetString(1));
}
}
}

Don’t you feel more comfortable with using statement? Let me just go bit deep on using statement, how does this work? Actually when an using statement compiled, the CLR converts it to a try and Finally block. It does three things, get the resources (SqlConnection ), encloses in a try block and disposes in the Finally block. Hope you enjoyed …………….

Wednesday, April 21, 2010

Referring to another config file in web.config file

Instead of having all connection string detail on web.config file , we can create a new configuration
whatEvernName.config eg(connectionString.config) and have all connection string details in this new .config file.

Please go through the code below to understand this.

web.config
<connectionStrings configSource="connectionString.config" />

connectionString.config
<connectionStrings> 
<
clear /> 
<
add name="myConnection" connectionString="myConnectionString" providerName="System.Data.SqlClient" /> </connectionStrings>

This is really nice feature when you have a lot of tags in your web.config file,
as it increases the readability. There is another cool feature which is very useful for
developers when it’s come to development or rather I would say debug the code.
Most of the times we have our connection string in one file and there is a high chance to forget
to change live connection string properties to test environment properties, I have made such a mistake and had to restore a latest database backup, but luckily there was not any data lost.

This cool feature avoids taking place such a bad mistake. Try this it’s is really nice and you do not have to keep on changing your web.config’s conection string values. As in above code snippet you can have two config files as connectionString.debug.config and connectionString.releaase.config

VS IDE will take the valuse depending on what you have selected, debug or release. Isn’t it cool.
Enjoy…

Tuesday, April 20, 2010

GridView column text wrap width

We had a grid view and one of the column data was dynamically bound. Most of the time this grid goes out of the sreen area and makes user to scroll to right, because of this dynamically bounded column's text lenght. There were some solutions on the web but none of them working properly. Solution is, put the text inside a paragraph tag “<p> your text here</p>”

text-wrap

Friday, April 09, 2010

Rules to Better Websites Navigation

These rules I found while I am going through Scott Guthrie's web blog. It's captioned as "Adam Cogan's Rules for Better WebSites" and this was written in September 2003, pretty old but still apply.

http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterWebsitesNavigation.aspx#underline

Scott Guthie's web blog http://weblogs.asp.net/Scottgu/
Wednesday, February 10, 2010

Visual Studio 2010 Beta 2

Hi All,
This is what microsoft says, why don't you take this precious chance..........

"Now you can with
Visual Studio 2010 Beta 2 Jumpstart your next project with Visual Studio 2010 Beta 2. The "Go Live" license with this release means you can develop and deploy before the finished product hits the shelf."

Here is the URL http://www.microsoft.com/visualstudio/en-us/products/2010/default.mspx
Thursday, October 29, 2009

Microsoft Visual Studio 2005 with Microsoft SQL Server 2008 Support

I had an issue connecting to sql server 2008 database
in a VS 2005 application, I used .xsd (datase) to
manipulate data. I got an error,

I hope the fix would be helpfull to you as well, download and
install, Studio 2005 Service Pack 1 Update for Microsoft SQL Server 2008 Support

http://www.microsoft.com/downloads/details.aspx?FamilyID=e1109aef-1aa2-408d-aa0f-9df094f993bf&displaylang=en
Thursday, October 22, 2009

How do I get the return value and dataset when calling a stored procedure in a typed DataSet object?

There is no direct way to retrieve the returning value from a stored procedure in typed DataSet object. To accomplish this what you have to do is just add a new method in the partial class of the generated table adapter. Ill show how to do this step by step.

Consider the following stored procedure, which returns a dataset plus a return value. (not a complicated SP). In this case I just return 0 from the SP.

CREATE PROCEDURE [dbo].[GetUser]
@EmailID VARCHAR (100)
AS
BEGIN
SELECT
[UserID],
[FirstName],
[LastName],
[Phone]
FROM
[dbo].[User]
WHERE
[EmailID] = @EmailID

RETURN 0
END

Now add the method following method in the partial class of the generated table adapter. In my case it's UserTableAdapter.

 partial class UserTableAdapter
{
public object GetReturnValue(int commandIndex)
{
return this.CommandCollection[commandIndex].Parameters[0].Value;
}
}

Ok, now you can retrieve the return value plus the dataset,


private DSTableAdapters.UserTableAdapter _userAdapter = null;
protected DSTableAdapters.UserTableAdapter Adapter{
get{
if (_userAdapter == null)
_userAdapter = new DSTableAdapters.UserTableAdapter();
return _userAdapter;
}
}

This is the method that retrieve dataset and the get return value by calling the method (GetReturnValue(int commandIndex) ) we added in to generated partial class

public int GetUser(string emailid)
{
int success ;
try
{
        DS.UserDataTable userDataTable = Adapter.GetUser(emailid);
if (userDataTable.Rows.Count > 0)
{
//assingning to variables
FirstName = userDataTable.Rows[0]["FirstName"].ToString();
LastName = userDataTable.Rows[0]["LastName"].ToString();
success = int.Parse(Adapter.GetReturnValue(0).ToString());
}
}
catch (Exception ex)
{
//write you exception handling
}
    return success; 
}
Wednesday, October 14, 2009

Limiting Textarea length

I just wrote a java script to limit the max length of a teaxtarea control.
Here is the code snippet.

Limiting Textarea length


<'textarea name="txaDescription" onkeypress="return maxLength(this, 250);" textarea>

and the javascript

function imposeMaxLength(Object, MaxLen)
{
return (Object.value.length <= MaxLen);
}
Friday, September 25, 2009

Javascript optional parameters

Wednesday, September 09, 2009

Numeric only , windows programing , C#.Net

If you want to restrict a text box, to enter only numeric values.
Just do the following in the KeyPress event.

private void txtOpenCount_KeyPress(object sender, KeyPressEventArgs e)
{
string str = "\b0123456789";
e.Handled = !(str.Contains(e.KeyChar.ToString()));
}
Sunday, August 23, 2009

inputbox in C#

I had to do a windows program, which is not that familier to me with .Net technology as I am ASP.Net guy. But it was really interesting than I thought before start the developments. In my early carrier I have done lot of developments using VB6.0 and in present too.
I wanted to add a input box in my new windows program which is in C#.Net, suddenly I realised that there is no input dialog box with C#.Net but I found a link to do a input dialog box, which was really helpfull. I think it would be really helpfull you guys as well.

http://www.knowdotnet.com/articles/inputbox.html
Wednesday, August 12, 2009

SQL LIKE THAN YOU LIKE



Do not confuse with the title, let me explain what it is. Consider about the following table, (Persons)







One might need to get only the FirstName that starts with letter ‘W’
in this case we can write our query as follows

SELECT * FROM Persons WHERE FirstName LIKE 'W%'

Result would be





OK cool , what if you need to get all the records that FirstName starts
with letter ‘W’ and letter ‘R’, in this case I am sure most of developes
query would be as follows.

SELECT * FROM Persons WHERE FirstName LIKE 'W%' OR FirstName LIKE 'R%'







Fine, you got the result what you expected but there is another way to do this
without using OR FirstName LIKE ‘R%’

SELECT * FROM Persons WHERE FirstName LIKE '[WR]%'

Don’t you think this is better? Keep in mind you can use this not only for the first letter
you can use this for last letter even you can use with negation ( LIKE ‘[^WR]%’)

Tuesday, August 11, 2009

Get percentage of records from a table

Have you ever come across a situation where you need to get top  25%
records from a table. Here is the SQL that returns first 25% of records from a table.

SELECT TOP 25 PERCENT * 
FROM dbo.yourtable

This will return what you want, the 25% of records from the table. What if you want the second 25% of records.

SELECT TOP 50 PERCENT *
FROM ( SELECT TOP 50 PERCENT * 
FROM dbo.yourtable) results ORDER BY 1 DESC


On the above query by descending the records by first column, assuming that it is the primerykey or the records we need to filter by, you will get the top  25% which is the second 25% of the records.

Think how to get the third and the last 25% records leisurely.

Thursday, August 06, 2009

ConnectionStringBuilders


Connection string is a string variable which include many properties and values (InitialCatelog, uid, pwd , Data Source) needs to connect to a database.

SqlConnection cn = new SqlConnection();
cn.ConnectionString ="uid=sa;pwd=Initial Catalog=ADO; Data Source=(local); ConnectTimeout = 30";
cn.Open();

Creating connection string is bit tedious as it’s a string and could be error prone.

ADO.Net providers support connection string builder objects which allow you to create connection string , and establish the name/value pairs using strongly typed properties.

SqlConnectionStringBuilder cnStrBuilder = new SqlConnectionStringBuilder();
cnStrBuilder.UserID = "sa";
cnStrBuilder.Password = "";
cnStrBuilder.InitialCatalog = "ADO";
cnStrBuilder.DataSource = "(local)";
cnStrBuilder.ConnectTimeout = 30;
SqlConnection cn = new SqlConnection();
cn.ConnectionString = cnStrBuilder.ConnectionString;

Here on above example , creates a SqlConnectionStringBuilder variable and passing values for
ConnectionStringBuilder properties and then the ConnectionStringBuilder’s ConnectionString value been assigned to cn.ConnectionString instead directly passing ConnectionString string
as follows.

cn.ConnectionString ="uid=sa;pwd=Initial Catalog=ADO; Data Source=(local); ";

My Achievements

Member of

Blog Archive

Followers

free counters