Hi Guys,
This blog we explain how to create out
parameter stored procedure for insert data and show identity column values.
For show identity column value we create a
stored procedure using output parameter like.
Table: - Insdata
Id int primarykey
IDENTITY(1,1),
Name varchar(50),
City varchar(50)
Now we create a stored
procedure for insert data using out parameter
Create procedure
insoutparamdata
(@Id int OUTPUT,
@Name varchar(50),
@City varchar(50))
as
begin
insert into Insdata(Name,City)values(@Name,@City)
set @Id = SCOPE_IDENTITY()
return @Id
end
insstore.aspx
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.auto-style1
{
width: 100%;
}
.auto-style2
{
width: 353px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<table class="auto-style1">
<tr>
<td class="auto-style2">Name</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style2">City</td>
<td>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style2"> </td>
<td>
<asp:Button ID="btnSubmit" runat="server" OnClick="btnSubmit_Click" Text="Submit" />
<asp:Label ID="lblMsg" runat="server" Text="Msg"></asp:Label>
</td>
</tr>
</table>
<div>
</div>
</form>
</body>
</html>
Instore.aspx.cs
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;
using System.Data;
public partial class outparaminsert : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sai"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("outstore", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name",
txtName.Text);
cmd.Parameters.AddWithValue("@City",
txtCity.Text);
SqlParameter param = new SqlParameter("@Id",SqlDbType.Int);
//param.ParameterName
= "@Id";
//param.SqlDbType
= SqlDbType.Int;
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
con.Open();
cmd.ExecuteNonQuery();
string result = param.Value.ToString();
lblMsg.Text = result;
}
}
No comments:
Post a Comment