google

Newest Post

Showing posts with label MS SQL Server. Show all posts
Showing posts with label MS SQL Server. Show all posts

Select Date in SQL in Desired Formats like AM, PM

| Friday, May 6, 2016
Read more »
Use following in-built function to select the dates in desired formats. Following format returns date in AM, PM format

Query - SELECT FORMAT(GetDate(),'MM/dd/yyyy hh:mm:ss tt')
Result - 05/06/2016 03:06:02 PM

Select Date in SQL in Desired Formats like AM, PM

Posted by : Stranger
Date :Friday, May 6, 2016
With 0comments

Code to export the Grid View/Data Grid data into Excel File.

| Friday, February 13, 2015
Read more »
 protected void btnExportToExcel_Click(object sender, EventArgs e)  
   {  
     try  
     {  
       DataTable dt = student.GetDetails("B.Tech").Tables[0];  
       DataGrid GridView1 = new DataGrid();  
       GridView1.DataSource = dt;  
       GridView1.DataBind();  
       Response.Clear();  
       Response.Buffer = true;  
       Response.AddHeader("content-disposition", string.Format("attachment; filename=StudentsBTech.xls"));  
       Response.Charset = "";  
       Response.ContentType = "application/vnd.ms-excel";  
       StringWriter sw = new StringWriter();  
       HtmlTextWriter htw = new HtmlTextWriter(sw);  
       GridView1.GridLines = GridLines.Both;  
       GridView1.AllowPaging = false;  
       GridView1.RenderControl(htw);  
       Response.Output.Write(sw.ToString());  
       GridView1.DataBind();  
       Response.Flush();  
     }  
     catch (Exception ex)  
     {  
     }  
     finally { Response.End(); }  
   }  
   public override void VerifyRenderingInServerForm(Control control)  
   { }  

Code to export the Grid View/Data Grid data into Excel File.

Posted by : Stranger
Date :Friday, February 13, 2015
With 0comments

Convert DataSet or DataTable into JSON in C#

| Tuesday, June 10, 2014
Read more »
 public string ConvertToJSON(DataSet ds)  
 {  
 System.Web.Script.Serialization.JavaScriptSerializer serializer = 
new System.Web.Script.Serialization.JavaScriptSerializer();  
     List<Dictionary<string, object>> rows = 

new List<Dictionary<string, object>>();  
     Dictionary<string, object> row = null;  
     foreach (DataRow dr in ds.Tables[0].Rows)  
     {  
       row = new Dictionary<string, object>();  
       foreach (DataColumn col in ds.Tables[0].Columns)  
       {  
         row.Add(col.ColumnName.Trim(), dr[col]);  
       }  
       rows.Add(row);  
     }  
     return serializer.Serialize(rows);  
 }  

Convert DataSet or DataTable into JSON in C#

Posted by : Stranger
Date :Tuesday, June 10, 2014
With 0comments

How to apply RowFilter on DataView

| Friday, May 9, 2014
Read more »
Following code can be used to apply filter on the DataView

 DataSet ds = new DataSet();  
 ds= GetData();  
 DataView dv = ds.Tables[0].DefaultView;  
 dv.RowFilter = "SrNo='" + txtSNo.Text.Trim() + "'";   


LIKE, AND , OR operators can also be used in the RowFilter of Dataview.

To access the filtered records, each DataRowView of the DataView

 foreach (DataRowView drv in dv)  
       {  
         currentName = Convert.ToString(drv["Name"]);  
       }  



How to apply RowFilter on DataView

Posted by : Stranger
Date :Friday, May 9, 2014
With 0comments

Unable to find the requested .Net Framework Data Provider. It may not be installed.

| Thursday, April 10, 2014
Read more »
Above error can be caused by if the "Provider" given in "SqlDataSource" or any other data source control is not updated.

This error can be resolved either by updating the Provider details in connection string property of web.config file or you simply remove the

 ProviderName="<%$ ConnectionStrings:DbConnectionString.ProviderName %>"  


from SqlDataSource control or any other data source control.

SQL Server Update statement with Joins and Where clause

| Thursday, February 27, 2014
Read more »
Update statement of MS SQL server is used to update the tuples or set of tuples in a table. Update statement can also be used with joins to update the values from one table into another table as follows.
UPDATE  Customer
SET BrandName= BrandNameMaster.Id
from Customer Inner Join BrandNameMaster on
BrandName=BrandNameMaster.Name




This will update the "BrandName" column of the "Customer" table with the values of "Id" columns from the "BrandNameMaster" table based on the Inner join condition on columns "BrandName" of "Customer" table and "Name" column of "BrandNameMaster" table.

We can also additionally use where statement with such Update statement as follows.

UPDATE  Customer
SET BrandName= BrandNameMaster.Id
from Customer Inner Join BrandNameMaster on
BrandName=BrandNameMaster.Name

where  BrandName='Nike'










 If you have used any other variations of update statement, Please post in the comments.

Thanks 

SQL Server Update statement with Joins and Where clause

Posted by : Stranger
Date :Thursday, February 27, 2014
With 0comments
Prev
▲Top▲