Aspdotnet-Sharepoint: T-SQL

Showing posts with label T-SQL. Show all posts

Monday, April 29, 2013

Edit,delete,update records in gridview using sqldatasource in asp.net

Title: Edit Delete Update in Grid view in asp.net

Description:In previous post i have given how to edit,delete,update in grid view using row events,Bind data to grid view using SQL data source(here you can see how to use it).For this we need to set the properties for grid view to enable the columns for db transaction.Might be get where the DML commands are executed?.The answer is the sqldatasource has properties to executes those commands when user perform those action on grid view

Example:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="OrderGrid.aspx.cs" Inherits="OrderGrid" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GvOrder" AllowSorting="True" Runat="server"
AutoGenerateEditButton="True" AutoGenerateDeleteButton="True" AutoGenerateColumns="False" DataSourceID="OrdrDb">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="OrderID"
SortExpression="OrderID" />
<asp:BoundField DataField="OrderName" HeaderText="OrderName"
SortExpression="OrderName" />
<asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
<asp:BoundField DataField="Address" HeaderText="Address"
SortExpression="Address" />
<asp:BoundField DataField="Amount" HeaderText="Amount"
SortExpression="Amount" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="OrdrDb" runat="server"
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
SelectCommand="SELECT * FROM [Orders]" UpdateCommand="UPDATE [Orders] SET [OrderName] = @OrderName, [Phone] = @Phone,
[Address] = @Address, [Amount] = @Amount WHERE [OrderID] = @OrderID" DeleteCommand="Delete [Orders] WHERE [OrderID] = @OrderID"></asp:SqlDataSource>
</div>
</form>
</body>
</html>

Result:
Display Order table table using Data source:

Update Records using edit operation in grid view

Tuesday, November 20, 2012

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

It Will happen when the primary key Field has null value which has been referenced to child table with foreign key relation.To avoid these kind of errors we need get the records which has null value in parent table.To get those details we need to use the below query .

select  * from Order O LEFT JOIN Sales s on O.Order_Id= s.Order_Id
WHERE s.Order_Id is null

If you are not get any data using this query ,we can solve in other using below queries

Second Method:
First i will get the records which is not null using foreign key filed.Then i will check the FK value with PK values .If any FK is referenced to null i will update record with existing PK value

select *from order where order_id is not null
update order  set order_id=4 where order_id=5

Thursday, October 11, 2012

How to Get Last updated /Inserted records from the table in sql server

How could you solve if you had a big problem with data base when insert the huge amount of duplicate records into table ?The same situation i have faced recently.by the time i simply makes the insertion process for that bulk records.But later on i got a trouble with those records .For this i want to checked out to display the recent insertion data from table.In the below query i have used date function and length function to get the last updated records which are greater than current date minus 5 (last five days) and length of header is greater than 4

select * from order
where len(order_head)<=4
and (inactive_date is not mull or inactive_date > getdate()-5)
and order_head is not null
and order_code like 'SAT%'

Finally all redundant insertion records has been deleted for last five days using the below query

Delete order
where len(order_head)<=4
and (inactive_date is not mull or inactive_date > getdate()-5)

Tuesday, October 9, 2012

How to insert data from view to table in sql server

Recently i have work with large database for data insertion which is from "excel sheet".For this first I will export data from excel sheet to data table,then "create view" (test_orders)which has data based on where condition.

insert into orders(ordr_code,ordr_desc,ordr_code,price_amt)
select [Title ID] ,Name,replace(ORDERCODE,'-',''),[List Price] from [test_orders]

In the above first i have select the data from view then insert into desired table.Here i have done string replacement for one column when fetch the data from view

Friday, November 25, 2011

How to delete Duplicate Rows from Table

Title:How to delete duplicate rows in table using SQL Server

Description:
While working with we applications ,we have to maintain the data base is unique.Now i will describe one example on data redundancy.We just take one registration site ,which is used to register the personal details .When the user give the details multiple times ,we have to check whether the data is exist or not.If the records are exit we can delete using below query.
Note:The Duplication rows in Database tables will exists by running the data repeatedly With out having the primary key on table.Here i have shown an example to remove the duplicate records in table

Example:

DELETE FROM Employee e1 WHERE ROW_NUMBER()<>(SELECT MIN( ROW_NUMBER())FROM EMployee e2 WHERE e1.empname= e2.empname)

Aspdotnet-Sharepoint

Top menu