SQL Server

Ashish Patel

How to Resolve the collation conflict and check Collate in SQL Server

In This Article, I’ll show you How to solve Resolve the collation conflict in SQL Server.
For Example I have SQL Query:
Query:

SELECT     *
FROM         categories INNER JOIN
                      search ON categories.cid = search.cat_id

Above SQL Query Giving me a below error.
Error:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Why:
Because Category.cid column has different collate than search.cat_id. So, we cannot use = operation between them.

Here categories.cid hase collate SQL_Latin1_General_CP1_CI_AS
And search.cat_id hase collate Latin1_General_CI_AS

Solution:

1. Have to make both column with same Collate so we can compare.
1a. here we change collate of search.cat_id to collate SQL_Latin1_General_CP1_CI_AS from Latin1_General_CI_AS

SELECT     search.*
FROM         categories INNER JOIN search 
ON categories.cid  = search.cat_id collate SQL_Latin1_General_CP1_CI_AS

OR
1b. here we change collate of categories.cid to Latin1_General_CI_AS from collate SQL_Latin1_General_CP1_CI_AS 

SELECT     search.*
FROM         categories INNER JOIN search 
ON categories.cid collate Latin1_General_CI_AS = search.cat_id

2. Use COLLATE DATABASE_DEFAULT

   SELECT     search.*
   FROM         categories INNER JOIN search 
ON categories.cid COLLATE DATABASE_DEFAULT = search.cat_id COLLATE DATABASE_DEFAULT

How to check Collate:
Go to SQL Server Object Explorer then go to your database table. Expand table & expand Column of table then right click on column which you want to check Collate.

Now Click on Property and you will see following image

Ashish Patel

Create XML using SQL Query in SQL Server 2008

In this blog I will show how to create XML using Query:

SELECT TOP 1000 [Code]
      ,[Name]
      ,[Price]
  FROM [Temp].[dbo].[Product]
  FOR XML RAW ('Product'),
  ROOT ('Products');

Here SQL statement “FOR XML RAW” creates one XML node for every corresponding row in database and columns will be created as attributes of that XML node.
Output:

<Products>
<Product Code="1" Name="Tea" Price="10.0000"/>
<Product Code="2" Name="Coffee" Price="25.0000"/>
</Products>

SQL ELEMENTS create Elements instead of Attributes in xml 

SELECT TOP 1000 [Code]
      ,[Name]
      ,[Price]
  FROM [Temp].[dbo].[Product]
  FOR XML RAW ('Product'),
ROOT ('Products'),
ELEMENTS;

Output:

<Products>
<Product>
  <Code>1</Code>
  <Name>Tea</Name>
  <Price>10.0000</Price>
</Product>
<Product>
  <Code>2</Code>
  <Name>Coffee</Name>
  <Price>25.0000</Price>
</Product>
</Products>
Ashish Patel

Edit Gridview Using jQuery Dialog ASP.Net C#

In this article, I’ll explain how to Edit records in ASP.Net GridView control using jQuery Dialog UI. There is many other way to do that but here is the easiest way to do it.

Database:
For this tutorial, I am using Employee database.

CREATE TABLE [dbo].[T_Employees](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](20) NULL,
	[LastName] [nvarchar](20) NULL,
	[Department] [nvarchar](40) NULL,
	[Location] [nvarchar](40) NULL
) ON [PRIMARY]
Insert INTO [dbo].[T_Employees] values ('Tanvi','Patel',' Physiotherapy ','Sydney') 
Insert INTO [dbo].[T_Employees] values ('Ashish','Patel','IT','Sydney')    
Insert INTO [dbo].[T_Employees] values ('Vaishu','Patel','Micro','Sydney')  
Insert INTO [dbo].[T_Employees] values ('Bhavik','Patel',' pediatrician ','Sydney')

Connection string:
Below is the connection string to connect to the database.

<connectionStrings>
    <add name="TempConnectionString" connectionString="Data Source=ASHISH;Initial Catalog=Temp;Persist Security Info=True;User ID=sa;Password=********" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Aspx Page:
In this tutorial, there are two aspx pages.

1. Default.aspx — To implement Gridview control
2. EditEmployee.aspx –To Edit Employee

Default.aspx page:
Below HTML Markup of the page you will notice that I have placed a Script Manager and an ASP.Net Update Panel on the page. Inside the Update Panel I have placed an ASP.Net GridView Control along with brnRefreah that will be used to refresh after Edit the records in the GridView Control.

        <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="true">
        </asp:ScriptManager>
         <asp:UpdateProgress ID="UpdateProgress1" runat="server">
        <ProgressTemplate>
        <img src="Loading.gif" alt="" />
        </ProgressTemplate>
        </asp:UpdateProgress>

    <asp:UpdatePanel runat="server" ID="UpdatePanel1" UpdateMode="Conditional" >
   <ContentTemplate>
       
       <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
            DataSourceID="SqlDataSource1">
            <Columns>
            <asp:TemplateField HeaderText="ID">
           <ItemTemplate >
               <a id="popup" href='EditEmployee.aspx?id=<%# Eval("ID") %>' >edit</a>
         </ItemTemplate>
           </asp:TemplateField>
              <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
                    ReadOnly="True" SortExpression="ID" />
               
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" 
                    SortExpression="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" 
                    SortExpression="LastName" />
                <asp:BoundField DataField="Department" HeaderText="Department" 
                    SortExpression="Department" />
                <asp:BoundField DataField="Location" HeaderText="Location" 
                    SortExpression="Location" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:TempConnectionString %>" 
            SelectCommand="SELECT Top 10 * FROM [T_Employees]"></asp:SqlDataSource>

           <asp:Button ID="btnRefresh" Text="refresh" runat="server" 
            onclick="btnRefresh_Click" />
   </ContentTemplate>
</asp:UpdatePanel>

You notice that in gridview I have placed Edit link (line 17 in above code) to pass ID of Employee to EditEmployee page. (For example. ‘editEmployee.aspx?id=1).
Now when user click on Edit link, Dialog box will display and load EditEmployee.aspx page. To create dialog box on click event of Edit link we are going to use jQuery Dialog UI. Here is code 

 
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.5.1/jquery.min.js"></script>
    <script type="text/javascript" src="http://ajax.microsoft.com/ajax/jquery.ui/1.8.6/jquery-ui.min.js"></script>
    <link type="text/css" rel="Stylesheet" href="http://ajax.microsoft.com/ajax/jquery.ui/1.8.6/themes/smoothness/jquery-ui.css">
    <script type="text/javascript">
        $(document).ready(function () {
            $('a#popup').live('click', function (e) {
                
                var page = $(this).attr("href")  //get url of link
              
                var $dialog = $('<div></div>')
                .html('<iframe style="border: 0px; " src="' + page + '" width="100%" height="100%"></iframe>')
                .dialog({
                    autoOpen: false,
                    modal: true,
                    height: 450,
                    width: 'auto',
                    title: "Edit Employee",
                    buttons: {
                        "Close": function () { $dialog.dialog('close'); }
                                },
                    close: function (event, ui) {
                        
                       __doPostBack('<%= btnRefresh.ClientID %>', '');  // To refresh gridview when user close dialog
                    }
                });
                $dialog.dialog('open');
                e.preventDefault();
            });
        });
    </script>

In dialog Close event, I use __doPostBack(‘<%= btnRefresh.ClientID %>‘, ”); to refresh Gridview. This code fire btnRefresh_click event thats why we need to place gridview1.databind() in it to refresh gridview.
Codebehide (Default.aspx.cs):

using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        
    }
    protected void btnRefresh_Click(object sender, EventArgs e)
    {
        GridView1.DataBind();
    }
}

EditEmployee.aspx Page:
In this page, we get Employee ID from Querystring. After getting Employee ID We use sql connection to get FirstName, LastName, Location and Department from Database.

EditEmployee.aspx

<form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
    <ContentTemplate>
    <h3>Edit Employee</h3>
        <asp:Label ID="lblResult" runat="server" ForeColor="Green"></asp:Label>
        <asp:Panel ID="pnlEdit" runat="server">
        
    <p>ID:<asp:TextBox ID="txtID" ReadOnly="true" runat="server"></asp:TextBox></p>
    <p> FirstName: <asp:TextBox ID="txtfName"  runat="server"></asp:TextBox></p>
    <p> LastName:<asp:TextBox ID="txtlNmae"  runat="server"></asp:TextBox></p>
    <p>Department: <asp:TextBox ID="txtDept"  runat="server"></asp:TextBox></p>
    <p>Location:<asp:TextBox ID="txtLocation"  runat="server"></asp:TextBox></p>
<p> <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
    </p>
    </asp:Panel>
    </ContentTemplate>
    </asp:UpdatePanel>
    </form>

Codebehind code: EditEmployee.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

public partial class EditPopup : System.Web.UI.Page
{
    string CT = ConfigurationManager.ConnectionStrings["TempConnectionString"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string EID = Request.QueryString["id"];
            
            using (SqlConnection cn = new SqlConnection(CT))
            {
                string query = "Select * from T_Employees where ID='" + EID + "'";
                using (SqlCommand cmd = new SqlCommand(query, cn))
                {
                    cn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    if(dr.Read() )
                    {
                        txtID.Text = EID;
                        txtfName.Text = dr["FirstName"].ToString();
                        txtlNmae.Text = dr["LastName"].ToString();
                        txtDept.Text = dr["Department"].ToString();
                        txtLocation.Text = dr["Location"].ToString();
                    }
                    cn.Close();
                    cn.Dispose();

                }
            }
        }
           
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        ///save Employee Recoed code
        ///

        using (SqlConnection cn = new SqlConnection(CT))
        {
            string query = "Update T_Employees  Set FirstName='"+txtfName.Text+"', LastName='"+txtlNmae.Text +"', Department='"+txtDept.Text +"', Location='"+txtLocation.Text +"' where ID='" + txtID.Text  + "'";
            using (SqlCommand cmd = new SqlCommand(query, cn))
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                
                cn.Close();
                cn.Dispose();
                lblResult.Text = "Employee Data Saved!!";
                pnlEdit.Visible = false;
            }
        }
        
    }
}

CSS File to style Gridview:

th {
font: bold 11px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
color: #4f6b72;
border-right: 1px solid #C1DAD7;
border-bottom: 1px solid #C1DAD7;
border-top: 1px solid #C1DAD7;
letter-spacing: 2px;
text-transform: uppercase;
text-align: left;
padding: 6px 6px 6px 6px;
background: #D5EDEF;
}
 
td {
border-right: 1px solid #C1DAD7;
border-bottom: 1px solid #C1DAD7;
background: #fff;
padding: 6px 6px 6px 6px;
color: #4f6b72;
}
 
td.alt
{
background: #F5FAFA;
color: #797268;
}
 
td.boldtd
{
font: bold 13px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
background: #D5EDEF;
color: #797268;
}

Download

[wpdm_file id=3]

Thanks.

Ashish Patel

Convert nvarchar to float or number in sql

I recently came across the following usefull SQL query, Maybe you’ll find it useful.
In SQL, convert nvarchar (string) to number can be achieve by using cast and convert SQL function.
For example, I have Following table

You may notice that UnitPrice has nvarchar datatype.
Here is my Query to convert UnitPrice datatype to float

SELECT [Name]
      ,[Description]
      ,[Unit]
      ,convert(float,[UnitPrice]) [UnitPrice]
      ,[CreateDate]
  FROM [Product]

Result:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

Reason: Have a close look at the table, iMac UnitPrice is 1,200.00. UnitPrice 1,200.00 have coma which stop sql to convert into float.
Final Query:

SELECT [Name]
      ,[Description]
      ,[Unit]
      ,convert(float,replace([UnitPrice],',','') )[UnitPrice]
      ,[CreateDate]
  FROM [Product]

Thanks