文件下载地址包含sql2k5的mdf 请自行附加:http://files.cnblogs.com/iCaca/SqlOutputParameter.rar
createtable 是sql2k5的语法 2k可能会报错 请自行修改
Tabel User
1CREATE TABLE [dbo].[User](
2 [ID] [int] IDENTITY(1,1) NOT NULL,
3 [Name] [nvarchar](50) NULL,
4 [ReMark] [nvarchar](1000) NULL,
5 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
6(
7 [ID] ASC
8)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
9) ON [PRIMARY]
10
1CREATE TABLE [dbo].[User](
2 [ID] [int] IDENTITY(1,1) NOT NULL,
3 [Name] [nvarchar](50) NULL,
4 [ReMark] [nvarchar](1000) NULL,
5 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
6(
7 [ID] ASC
8)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
9) ON [PRIMARY]
10
存储过程
1CREATE PROCEDURE [dbo].[User_Delete]
2@ID int
3
4AS
5BEGIN
6Delete [user] where id=@id
7
8END
9
10CREATE PROCEDURE [dbo].[User_GetList]
11
12AS
13BEGIN
14select * from [user]
15END
16
17CREATE PROCEDURE [dbo].[User_Add]
18@Name nvarchar(50),
19@Remark nvarchar(1000),
20@Returns int output
21AS
22BEGIN
23insert into [user] values(@Name,@Remark)
24select @Returns= @@identity;
25END
26
27CREATE PROCEDURE [dbo].[User_Update]
28@ID int,
29@Name nvarchar(1000),
30@Remark nvarchar(2000)
31AS
32BEGIN
33update [user] set [Name]=@Name ,Remark=@Remark where id=@id
34
35END
1CREATE PROCEDURE [dbo].[User_Delete]
2@ID int
3
4AS
5BEGIN
6Delete [user] where id=@id
7
8END
9
10CREATE PROCEDURE [dbo].[User_GetList]
11
12AS
13BEGIN
14select * from [user]
15END
16
17CREATE PROCEDURE [dbo].[User_Add]
18@Name nvarchar(50),
19@Remark nvarchar(1000),
20@Returns int output
21AS
22BEGIN
23insert into [user] values(@Name,@Remark)
24select @Returns= @@identity;
25END
26
27CREATE PROCEDURE [dbo].[User_Update]
28@ID int,
29@Name nvarchar(1000),
30@Remark nvarchar(2000)
31AS
32BEGIN
33update [user] set [Name]=@Name ,Remark=@Remark where id=@id
34
35END
Default.aspx
1<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
2
3<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4
5<html xmlns="http://www.w3.org/1999/xhtml" >
6<head runat="server">
7 <title>无标题页</title>
8</head>
9<body>
10 <form id="form1" runat="server">
11 <div>
12 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
13 AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
14 <Columns>
15 <asp:CommandField ShowSelectButton="True" />
16 <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
17 SortExpression="ID" />
18 <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
19 <asp:BoundField DataField="ReMark" HeaderText="ReMark" SortExpression="ReMark" />
20 </Columns>
21 </asp:GridView>
22
23 </div>
24 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestSqlDataSourceConnectionString %>"
25 SelectCommand="User_GetList" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
26 <asp:FormView ID="FormView1" runat="server" DataKeyNames="ID" DataSourceID="SqlDataSource2" >
27 <EditItemTemplate>
28 ID:
29 <asp:Label ID="IDLabel1" runat="server" Text='<%# Eval("ID") %>'></asp:Label><br />
30 Name:
31 <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>'>
32 </asp:TextBox><br />
33 ReMark:
34 <asp:TextBox ID="ReMarkTextBox" runat="server" Text='<%# Bind("ReMark") %>'>
35 </asp:TextBox><br />
36 <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
37 Text="更新">
38 </asp:LinkButton>
39 <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
40 Text="取消">
41 </asp:LinkButton>
42 </EditItemTemplate>
43 <InsertItemTemplate>
44 Name:
45 <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>'>
46 </asp:TextBox><br />
47 ReMark:
48 <asp:TextBox ID="ReMarkTextBox" runat="server" Text='<%# Bind("ReMark") %>'>
49 </asp:TextBox><br />
50 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
51 Text="插入">
52 </asp:LinkButton>
53 <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
54 Text="取消">
55 </asp:LinkButton>
56 </InsertItemTemplate>
57 <ItemTemplate>
58 ID:
59 <asp:Label ID="IDLabel" runat="server" Text='<%# Eval("ID") %>'></asp:Label><br />
60 Name:
61 <asp:Label ID="NameLabel" runat="server" Text='<%# Bind("Name") %>'></asp:Label><br />
62 ReMark:
63 <asp:Label ID="ReMarkLabel" runat="server" Text='<%# Bind("ReMark") %>'></asp:Label><br />
64 <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"
65 Text="编辑">
66 </asp:LinkButton>
67 <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete"
68 Text="删除">
69 </asp:LinkButton>
70 <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
71 Text="新建">
72 </asp:LinkButton>
73 </ItemTemplate>
74 </asp:FormView>
75 <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:TestSqlDataSourceConnectionString %>"
76 InsertCommand="User_Add" InsertCommandType="StoredProcedure" SelectCommand="User_GetModel"
77 SelectCommandType="StoredProcedure" UpdateCommand="User_Update" UpdateCommandType="StoredProcedure" OnInserted="SqlDataSource2_Inserted" DeleteCommand="User_Delete" DeleteCommandType="StoredProcedure">
78 <UpdateParameters>
79 <asp:Parameter Name="id" Type="Int32" />
80 <asp:Parameter Name="Remark" Type="String" />
81 </UpdateParameters>
82 <SelectParameters>
83 <asp:Parameter Name="id"
84 Type="Int32" />
85 </SelectParameters>
86 <InsertParameters>
87 <asp:Parameter Name="Name" Type="String" />
88 <asp:Parameter Name="Remark" Type="String" />
89 <asp:Parameter Direction="InputOutput" Name="Returns" Type="Int32" />
90
91 </InsertParameters>
92 <DeleteParameters>
93 <asp:Parameter Name="ID" Type="Int32" />
94 </DeleteParameters>
95 </asp:SqlDataSource>
96 </form>
97</body>
98</html>
99
1<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
2
3<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4
5<html xmlns="http://www.w3.org/1999/xhtml" >
6<head runat="server">
7 <title>无标题页</title>
8</head>
9<body>
10 <form id="form1" runat="server">
11 <div>
12 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
13 AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
14 <Columns>
15 <asp:CommandField ShowSelectButton="True" />
16 <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
17 SortExpression="ID" />
18 <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
19 <asp:BoundField DataField="ReMark" HeaderText="ReMark" SortExpression="ReMark" />
20 </Columns>
21 </asp:GridView>
22
23 </div>
24 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestSqlDataSourceConnectionString %>"
25 SelectCommand="User_GetList" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
26 <asp:FormView ID="FormView1" runat="server" DataKeyNames="ID" DataSourceID="SqlDataSource2" >
27 <EditItemTemplate>
28 ID:
29 <asp:Label ID="IDLabel1" runat="server" Text='<%# Eval("ID") %>'></asp:Label><br />
30 Name:
31 <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>'>
32 </asp:TextBox><br />
33 ReMark:
34 <asp:TextBox ID="ReMarkTextBox" runat="server" Text='<%# Bind("ReMark") %>'>
35 </asp:TextBox><br />
36 <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
37 Text="更新">
38 </asp:LinkButton>
39 <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
40 Text="取消">
41 </asp:LinkButton>
42 </EditItemTemplate>
43 <InsertItemTemplate>
44 Name:
45 <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>'>
46 </asp:TextBox><br />
47 ReMark:
48 <asp:TextBox ID="ReMarkTextBox" runat="server" Text='<%# Bind("ReMark") %>'>
49 </asp:TextBox><br />
50 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
51 Text="插入">
52 </asp:LinkButton>
53 <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
54 Text="取消">
55 </asp:LinkButton>
56 </InsertItemTemplate>
57 <ItemTemplate>
58 ID:
59 <asp:Label ID="IDLabel" runat="server" Text='<%# Eval("ID") %>'></asp:Label><br />
60 Name:
61 <asp:Label ID="NameLabel" runat="server" Text='<%# Bind("Name") %>'></asp:Label><br />
62 ReMark:
63 <asp:Label ID="ReMarkLabel" runat="server" Text='<%# Bind("ReMark") %>'></asp:Label><br />
64 <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"
65 Text="编辑">
66 </asp:LinkButton>
67 <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete"
68 Text="删除">
69 </asp:LinkButton>
70 <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
71 Text="新建">
72 </asp:LinkButton>
73 </ItemTemplate>
74 </asp:FormView>
75 <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:TestSqlDataSourceConnectionString %>"
76 InsertCommand="User_Add" InsertCommandType="StoredProcedure" SelectCommand="User_GetModel"
77 SelectCommandType="StoredProcedure" UpdateCommand="User_Update" UpdateCommandType="StoredProcedure" OnInserted="SqlDataSource2_Inserted" DeleteCommand="User_Delete" DeleteCommandType="StoredProcedure">
78 <UpdateParameters>
79 <asp:Parameter Name="id" Type="Int32" />
80 <asp:Parameter Name="Remark" Type="String" />
81 </UpdateParameters>
82 <SelectParameters>
83 <asp:Parameter Name="id"
84 Type="Int32" />
85 </SelectParameters>
86 <InsertParameters>
87 <asp:Parameter Name="Name" Type="String" />
88 <asp:Parameter Name="Remark" Type="String" />
89 <asp:Parameter Direction="InputOutput" Name="Returns" Type="Int32" />
90
91 </InsertParameters>
92 <DeleteParameters>
93 <asp:Parameter Name="ID" Type="Int32" />
94 </DeleteParameters>
95 </asp:SqlDataSource>
96 </form>
97</body>
98</html>
99
Default.aspx.cs
1using System;
2using System.Data;
3using System.Configuration;
4using System.Web;
5using System.Web.Security;
6using System.Web.UI;
7using System.Web.UI.WebControls;
8using System.Web.UI.WebControls.WebParts;
9using System.Web.UI.HtmlControls;
10
11public partial class _Default : System.Web.UI.Page
12{
13
14 protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
15 {
16 SqlDataSource2.SelectParameters[0].DefaultValue = GridView1.SelectedValue.ToString();
17 FormView1.ChangeMode(FormViewMode.ReadOnly);
18 }
19
20 protected void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e)
21 {
22 SqlDataSource2.SelectParameters[0].DefaultValue = e.Command.Parameters[2].Value.ToString();
23 FormView1.ChangeMode(FormViewMode.Edit);
24 Page.DataBind();
25 }
26
27
28
29
30}
31
1using System;
2using System.Data;
3using System.Configuration;
4using System.Web;
5using System.Web.Security;
6using System.Web.UI;
7using System.Web.UI.WebControls;
8using System.Web.UI.WebControls.WebParts;
9using System.Web.UI.HtmlControls;
10
11public partial class _Default : System.Web.UI.Page
12{
13
14 protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
15 {
16 SqlDataSource2.SelectParameters[0].DefaultValue = GridView1.SelectedValue.ToString();
17 FormView1.ChangeMode(FormViewMode.ReadOnly);
18 }
19
20 protected void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e)
21 {
22 SqlDataSource2.SelectParameters[0].DefaultValue = e.Command.Parameters[2].Value.ToString();
23 FormView1.ChangeMode(FormViewMode.Edit);
24 Page.DataBind();
25 }
26
27
28
29
30}
31
最为关键的事件
void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
SqlDataSource2.SelectParameters[0].DefaultValue = e.Command.Parameters[2].Value.ToString();
FormView1.ChangeMode(FormViewMode.Edit);
Page.DataBind();
}
ObjectDataSource原理相同 插入完后 取e.Command.Parameters 的值