SQL SERVER DO and DO NOT

 Basic SQL Query with many filters

Sometimes you have so much of data and you require to filter the data with few twist then you can implement this query that will simply check
TWO conditions between
TWO coloumns of
TWO Tables


take a look and you'll get explained better
------------------------------------------------------------------------------------------------------------

SELECT     Author_no
FROM         Author
WHERE     

(confirmation = 1) and Author_no  not in(SELECT distinct Author_nox FROM SecondAuthortable)

-------------------------------------------------------------------------------------------------------------
where Author_nox & Author_no,confirmation are three columns of two diffrent tables.
SecondAuthortable and Author simultaneously.

Converting Number Into Words By User Defined Function in SQL Server

Converting Number Into Words Function In SQL Server

Some time you  require to make things happen automatically in sql server,and when the word automatically comes in front of a programmer it means a function who can do it!So Lets Create A Function In SQL Who Can Do All These


Below In Just Two Steps You Will Create A Function in SQL Who Can Convert Your Numeric Value In Words.

1)Create Function,then Execute It!

___________________________________________________________________________________

CREATE FUNCTION dbo.udf_Num_ToWords (

 @Number Numeric (38, 0) -- Input number with as many as 18 digits

) RETURNS VARCHAR(8000) 
/*
* Converts a integer number as large as 34 digits into the 
* equivalent words.  The first letter is capitalized.
*
* Attribution: Based on NumberToWords by Srinivas Sampath
*        as revised by Nick Barclay
*
* Example:
select dbo.udf_Num_ToWords (1234567890) + CHAR(10)
      +  dbo.udf_Num_ToWords (0) + CHAR(10)
      +  dbo.udf_Num_ToWords (123) + CHAR(10)
select dbo.udf_Num_ToWords(76543210987654321098765432109876543210)
 
DECLARE @i numeric (38,0)
SET @i = 0
WHILE @I <= 1000 BEGIN 
    PRINT convert (char(5), @i)  
            + convert(varchar(255), dbo.udf_Num_ToWords(@i)) 
    SET @I  = @i + 1 
END
*
* Published as the T-SQL UDF of the Week Vol 2 #9 2/17/03
****************************************************************/
AS BEGIN

DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
     , @outputString = ''
     , @counter = 1
SELECT @length   = LEN(@inputNumber)
     , @position = LEN(@inputNumber) - 2
     , @loops    = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable   SELECT '00', ''
    UNION ALL SELECT '01', 'one'      UNION ALL SELECT '02', 'two'
    UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
    UNION ALL SELECT '05', 'five'     UNION ALL SELECT '06', 'six'
    UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
    UNION ALL SELECT '09', 'nine'     UNION ALL SELECT '10', 'ten'
    UNION ALL SELECT '11', 'eleven'   UNION ALL SELECT '12', 'twelve'
    UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
    UNION ALL SELECT '15', 'fifteen'  UNION ALL SELECT '16', 'sixteen'
    UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
    UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
    UNION ALL SELECT '30', 'thirty'   UNION ALL SELECT '40', 'forty'
    UNION ALL SELECT '50', 'fifty'    UNION ALL SELECT '60', 'sixty'
    UNION ALL SELECT '70', 'seventy'  UNION ALL SELECT '80', 'eighty'
    UNION ALL SELECT '90', 'ninety'   

WHILE @counter <= @loops BEGIN

 -- get chunks of 3 numbers at a time, padded with leading zeros
 SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

 IF @chunk <> '000' BEGIN
  SELECT @tensones = SUBSTRING(@chunk, 2, 2)
       , @hundreds = SUBSTRING(@chunk, 1, 1)
       , @tens = SUBSTRING(@chunk, 2, 1)
       , @ones = SUBSTRING(@chunk, 3, 1)

  -- If twenty or less, use the word directly from @NumbersTable
  IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
   SET @outputString = (SELECT word 
                                      FROM @NumbersTable 
                                      WHERE @tensones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
                       ELSE '' END
                               + @outputString
      END
   ELSE BEGIN -- break down the ones and the tens separately

             SET @outputString = ' ' 
                            + (SELECT word 
                                    FROM @NumbersTable 
                                    WHERE @tens + '0' = number)
              + '-'
                             + (SELECT word 
                                    FROM @NumbersTable 
                                    WHERE '0'+ @ones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
                       ELSE '' END
                            + @outputString
  END

  -- now get the hundreds
  IF @hundreds <> '0' BEGIN
   SET @outputString  = (SELECT word 
                                      FROM @NumbersTable 
                                      WHERE '0' + @hundreds = number)
                 + ' hundred ' 
                                + @outputString
  END
 END

 SELECT @counter = @counter + 1
      , @position = @position - 3

END

-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)


RETURN @outputString -- return the result
END

____________________________________________________________________________________ 

2)Use Function

here dbo.udf_Num_ToWords is name of function.

tbl_picalculate is table name

Amount is  a Coloumn name or for testing purpose you can simply type any value like 122 and execute the query.

So Concluding All,things become like this

i: select dbo.udf_Num_ToWords(122) from tbl_picalculate

ii: select dbo.udf_Num_ToWords(Amount) from tbl_picalculate

moreover....

Now The Very Basic Query of SQL 

           select username,amount from TABLE

becomes

            select username,dbo.udf_Num_ToWords(amount from) TABLE

and further you better know where to implement it!

Basic ASP.net for Starters

Basic ASP.Net With SQL Server: A Starting Touch

Basicly if you are in a mood to startup With ASP.net application,it means you can create both static and dynamic websites
Static website are just the design and words which do not have any relation with database interaction.they are easyto made and run.but when you comes to Dynamic website it means you are interacting with database.So the basic thing comes in mind is how to insert data in SQL table and how to retrieve it from that table??


To Understand Below Codes You Must Be Familiar with Visual studio & Basic Sql Concept

1)first create table in SQL server with two coloumn Date & Name,keep there datatype varchar(50)
save table name as MyTable


and here we go....

2)To Save your inserted record you need to Write A Code under any button click event (by mean of button click event ,i simply mean drag a button from toolbox to your .aspx page and double click it).
For that Open the Connection to database (In case of SqlCommand Not In SqlAdapter) then write an insert command to sql and execute it and in last close the connection!

Saving Command

con.Open();

SqlCommand cmd = new SqlCommand("insert into Mytable values ('" + TextBoxDate.Text + "','" + TextBoxName.Text + "')", con);

cmd.ExecuteNonQuery();

con.Close();

3)To View inserted data you need a control(Gridview) to show your data in a tablular form,For that you need to first Extracted your data by SqlAdapter Query, Save In Dataset And use that Dataset as datasource for GridView....

Binding Command

   void Binding()

    {

        SqlDataAdapter adp = new SqlDataAdapter("SELECT  * from Mytable ", con);

        DataSet ds = new DataSet();

        adp.Fill(ds);

        GridView1.DataSource = ds;

        GridView1.DataBind();

//in case of dropdown

        DropDownListPartyName.DataSource = ds;

        DropDownListPartyName.DataBind();

        DropDownListPartyName.Items.Insert(0, "---select---");

    }

or (by data table)

  void Binding()

    {

       Conn = new SqlConnection("Data Source=.;Initial Catalog=DB;Integrated Security=True");
        Conn.Open();
        Cmd = new SqlCommand();
        Cmd.Connection = Conn;
        Cmd.CommandText = "Select * from Mytable where Name=@Dname";
        Cmd.Parameters.AddWithValue("@Dname",'" + TextBox1.Text + "');
        Reader = Cmd.ExecuteReader();
        DataTable Dt = new DataTable();
        Dt.Load(Reader);
        Conn.Close();
        GridView1.DataSource = Dt;
        GridView1.DataBind();

    }

here if you didn't understand the dark one line...

if you have condition that you want to show the data from table like

Select * from Mytable where Name=(Text In TexBox1)

So for that you have to pass Textbox1.Text value to query which you can do by following...

either by this ..

Cmd.Parameters.AddWithValue("@Dname",'" + TextBox1.Text + "');

or 

Cmd.Parameters.Add("@Danme", SqlDbType.VarChar).Value = TextBox1.Text;




Now use this function binding() any where, any button click and it will Execute/Bind your table on that button click event!

Check Box Under Grid View

Multiple Selection With Checkbox Under Grid View






Some Time You Need A Mail Like Look Or Just Say Multiple Selection User Interface With Whom User Can Easily Intract,Here I  am giving you a short tutorial on  how to do multiple selection job in grid view

1)First thing :your UI page i.e aspx page 

 <asp:GridView runat="server" ID="GridView1" DataKeyNames="amt">
<Columns>
    <asp:TemplateField HeaderText="Select">
        <HeaderTemplate>
            <asp:CheckBox ID="chkSelectAll" runat="server" onclick="SelectAll(this.id)" />
        </HeaderTemplate>
        <ItemTemplate>
            <asp:CheckBox ID="chkSelect" runat="server" />
        </ItemTemplate>
    </asp:TemplateField>
    </Columns>
</asp:GridView>
<p>
<asp:Button ID="btnGet" runat="server" Text="Get Selected Records" OnClick="GetSelected" />
</p>


<script language="javascript" type="text/javascript">

    function SelectAll(Id)
{
        var myform = document.forms[0];
        var len = myform.elements.length;
document.getElementById(Id).checked == true ? document.getElementById(Id).checked = false :    document.getElementById(Id).checked = true;

        for (var i = 0; i < len; i++)
{
            if (myform.elements[i].type == 'checkbox') {
                if (myform.elements[i].checked) {
                    myform.elements[i].checked = false;
 }
                else
{
                    myform.elements[i].checked = true;
                }
            }
        }
    }

</script>

2)Your Server side coding i.e cs file

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class R2_trial : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["yourconnectionstring"]);
    DataSet ds = new DataSet();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

            GetData();

        }
    }
    private void GetData()
    {

        DataTable table = new DataTable();

        // get the connection

     
        {

            // write the sql statement to execute

            string sql = "SELECT amt,ProductId,Name from MyTable ";

            // instantiate the command object to fire
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                // get the adapter object and attach the command object to it
                using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
                {
                    // fire Fill method to fetch the data and fill into DataTable
                    ad.Fill(table);
                }
            }
        }
        // specify the data source for the GridView
        GridView1.DataSource = table;
        // bind the data now
        GridView1.DataBind();
    }
    protected void GetSelected(object sender, EventArgs e)
    {
        Response.Write("<h3>Selected records</h3>");
        foreach (GridViewRow row in GridView1.Rows)
        {
            CheckBox chk = (CheckBox)row.FindControl("chkSelect");
            if (chk.Checked)
            {
                Response.Write(GridView1.DataKeys[row.RowIndex].Value + ",");
            }
        }
    }
}


thing highlighted with green color are strongly recommended for beginners to watch them and understand what and where they used in this project sample!


Note for Beginner :just Copy paste above code,apply breakpoints and view whats going on in every step.

Asp.net POP UP by C#

Possibly there are three way to generate simple pop up

Basicaly This Article Is About How You Can Generate Asp.net POP UP by Using Server Side Codeing in C#

(other than ajax ,javascript)

 1)SIMPLE POP UP FOR C#

ClientScript.RegisterStartupScript(Page,GetType(), "urowntext","alert('alert');", true);





2)IF POP UP NOT WORKING IN AJAX UPDATE PANEL


lets take 'up' as your update panel name then

 closewindow = "alert('Rue No defined for the selected item.To redefine  delete from main grid.');";
 ScriptManager.RegisterStartupScript(this.up, this.up.GetType(), "closewindow", closewindow, true); 


3)BY REFERENCE OF WINDOW ASSEMBLY


follow the procedure like first right click on your project


Now click on Add Reference

And A Window Will Come In Front Of You

Select System.Window.Forms from .NET tab as shown above

Now you can try below Example to get pop up of your need

by First Adding this namespace on your form

Using System.Windows.Forms;

     // The simplest overload of MessageBox.Show. [1]
     //
     MessageBox.Show("Dot Net Perls is awesome.");
     //
     // Dialog box with text and a title. [2]
     //
     MessageBox.Show("Dot Net Perls is awesome.",
  "Important Message");
     //
     // Dialog box with two buttons: yes and no. [3]
     //
    MessageBox.Show("Is Dot Net Perls awesome?",
  "Important Question",
  MessageBoxButtons.YesNo);
     //
     // Dialog box with question icon. [4]
     //
     MessageBox.Show("Is Dot Net Perls awesome?",
  "Important Query",
  MessageBoxButtons.YesNoCancel,
  MessageBoxIcon.Question);
     //
     // Dialog box with question icon and default button. [5]
     //
      MessageBox.Show("Is Visual Basic awesome?",
  "The Question",
  MessageBoxButtons.YesNoCancel,
  MessageBoxIcon.Question,
  MessageBoxDefaultButton.Button2);

Transaction in SQL

SQL Transaction In C#                                                                                                        

SQL Transaction In C# means either run all command or do nothing (ROLLBACK)
to know the transaction process bit briefly lets take an example.

SqlTransaction trans;                       //globaly declared


try
{
cmd1=new sql(________);
int i=cmd.ExecuteNonQuery();



cmd2=new sql(________)
int j=cmd.ExecuteNonQuery()


trans.Commit();
if((i>0 ) && (j>0))
{
"record inserted"
}
catch(Exception)
{
trans.Rollback();
"Record not insered";
}
con.close();

Extract Last Characters From a String

Extract Last 3 Character From A TextBox And Show It On Label

Sometimes You Come To A Situation Where You Have Alot But You Need Only A Tiny Part Of It,Thats Where The Extract Concept  Come!
In C# Many Time You Will Get Strinig With Some Length But You Need In Your Own Size,So Just Follow The 2 Points Below....
1)
You can use this code in any click event...


int Len=TextBox1.Text.Length;
string Sub=TextBox1.Text.SubString(Len-3,3);
Lable.Text=Sub;


2)
and look yourself what will be result.

Maximum Request Length Exceeded

Configuring ASP.NET for Large File Uploads

"Maximum Request Length Exceeded" Problem arises when default setting of webconfig says you can not add any file more than 4mb or 3mb(or less)"




to increase the limit just add this line in your webconfig file under



<system.web>




<configuration>
  <system.web>
    <httpRuntime maxRequestLength ="2097151" enable="True" executiontimeout="45"/>
</system.web>
</configuration>


maxrequestlength means you are exceeding limit to...


100000=100MB


2000000=2GB

OPEN File From Server

OPEN Text,PDF,Image,Audio,Video Files From Server


In my Previous Post you get to know how to delete file from Server 
Some time situations come where you need to save file like txt,pdf,audio,video etc etc...
Now If Your PC have the software media to open it
like NOTEPAD for txt file,ADOBE for pdf,VLC for audio/video you can easily open all your file from server to your PC.

Just Follow Below Steps to Execute any file And Lets The file Automaticly open on your PC!


1)add this line in your namespace
using System.Diagnostics;


2)add this line in any event or pageload




//below line is use to open the file
 System.Diagnostics.Process.Start(Server.MapPath("TextFile.txt"));

Direct Jump On Line Number In HTML

Jump On Line Number In Html By Just clicking Any link,button or Text

<ul>
<li>
<a linkindex="249" href="#Obj">observer pattern:go to line no 249</a>
</li>
</ul>

Delete File From Server

Remove file From Server :DotNet

in my Previous Post you get to know how to Open Any file from Server 

Now If I am not wrong
You mean to say you want to send a Input by which the output should come with Delete a file from Server.




So When you Are working with Input Output the first thing Is
Add IO Namespace



1)using System..IO;


In the Head Of your .cs file .

2)and Type Below code With Your Own Understanding



        FileInfo TheFile = new FileInfo(MapPath(".") + "\\" + txtFile.Text);
        if (TheFile.Exists)
        {
            File.Delete(MapPath(".") + "\\" + txtFile.Text);
        }




Use Above Code Either In Page Load Or An Event !
To Remind you dont act as a fool to delete file from any other server where your this file does not exist!

Check If A Login Exists:Using ExecuteScalar() & ExecuteReader()

ExecuteScalar And ExecuteReader Use In Checking Login Existence 

In One Of  My Previous Blog I'v Already tought you About

Execute Scaler Use To Count Rows
Now Here We Basicly Deal With ExecuteScalar And ExecuteReader Use In Checking Login Existence



1)Check On Create Login If Any One Already Existed:by ExecuteScalar

SqlCommand cmd=new .......
Object obj=cmd.ExecuteScalar();
if(Obj!=null)
{
//alert (user alerready Existed)
}



2)Check On Login If SomeOne Existed Or Not


SqlCommand cmd=new .......
SqlDataReader dr=cmd.ExecuteReader()
if(dr.HasRows)
{
dr.Read();
if(dr["Username"].ToString()==TextBox1.Text && dr["Password"].ToString()==TextBox2.Text )
{
//Welcome User
}


}

How To Create A Web User Control

7 Easy Steps to Create a Simple Web User Control

From First ,You Must Know What Is Web User Control

In My Words Its Nothing But Simple A Control not a page Coded and designed by You which will further use in .aspx pages,And Below Is Bit Explained Definition.


1) An ASP.NET Web user control is similar to a complete ASP.NET Web page (.aspx file), with both a user interface page and code. You create the user control in much the same way you create an ASP.NET page and then add the markup and child controls that you need. A user control can include code to manipulate its contents like a page can, including performing tasks such as data binding.


A user controls differs from an ASP.NET Web page in these ways:


•The file name extension for the user control is .ascx.


•Instead of an @ Page directive, the user control contains an @ Control directive that defines configuration and other properties.


•User controls cannot run as stand-alone files. Instead, you must add them to ASP.NET pages, as you would any control.


•The user control does not have html, body, or form elements in it. These elements must be in the hosting page.

2)  Thank You For Giving Time In Above Lines Now Add New Item To Your Project i.e Web User Control ,you will get a web user control with .ascx extention (lets says its name Example.ascx)
Below Is A Sample How Your  Web User Control Looks Like

Example.ascx

And Just Work In It As You do In Normal Asp.net pages.(add button,textbox,label whatever and write there .cs too)


3)  Then Open Any of your .aspx page(let it be Default.aspx)


4)   Now What You Require From Here Onward Is You Have To Use Your Own Made Control In Your Default.aspx Page.


Now Two Things Remain...
1st Register That Control In You Page.
2nd Call Web User Control In Your Page.  






For That You Have To Follow Below Procedure....

5)Add This Line In Head Of Your aspx page


<%@ Register Src="~/Example.ascx" TagName="TN" TagPrefix="TPRF" %>


as shown in below Image











Now Remember Few Things
Above Line means 
Register(you are registering that control in your Page)
Src(path of that control)
TagName(it could be any thing you wish to name)
TagPreffix(it could be any thing you wish to name)

6)  Now Its Time To Call Web User Control


<TPRF:TN Id="webuser1" runat="Server"/>




7)  Now Run Your default Page It Will show you the Things As You Did Above.

To Remind If Adding More Times In Single Pages give you Error You Must Need To Add That Much Register <%Register%> Line How Much You Need To Call.

saving changes is not permitted.the changes you have made require the following tables to be dropped and re-created

Mainly the cause behind this is ,SQL prevent saving changes that require table re-creation

If I am not wrong do you guys facing this kind of pop up while saving a table,which you have build already.
below pop up shows that you can not update the design of table, usually this function work in bulk Professions,where Project head need the developer to do test on his table without modifying anything from database.

ok after this,lets come to the point.
just follow the below procedure...
which means go on TOOLS then OPTIONS.
(Note:before proceeding please close all opened window under sql window)

Add caption

After which you'll get this kind of screen of OPTIONS(below image),now click on Designer on left hand side of window,then table and definition designers...
in which you just have to UNCHECKED a option which is

• prevent saving changes that require table re-creation.
  (like below image!)

that all! you done it ,and now can modify your table whenever you require.

Problem with Cannot add duplicate collection entry of type 'add' with unique key attribute 'name'

Problem with Cannot add duplicate collection entry of type 'add' with unique key attribute 'name' set to.....

 If I m not wrong your problem may be looks something like this.. 

The Problem Is Not That You Have Done Something Wrong With Code...,
The Problem Is Because You Have Done Something Like This...

1)You Published Your Project In a Folder
2)Then You Created Final Virtual Directory In The Same Folder...So What Happens Usually Is ......There Are TWO web configs under the wwwroot folder...and you better knows that TWO SWORD CAN NOT BE PLACE IN SINGLE SCABBARD.
3)So Just Remove The External WebConfig....by means of external i mean the first web config before you have created virtual directory.
Let Me Know If You Face Further Error.