Hi *.*,
Recently I had to write an insert stored procedure and needed to return the ID of the row I was inserting.As i unaware of SCOPE_IDENTITY() function i was doing some piece of bad coding at their.Getting max id of the table just after the insertion... oooh :( . Last day i was going through asp fourms I came to know abt this. then i plan to make a try and i achieved the goal too.
On HTML<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="400" border="0" align="center">
<tr>
<td width="50%"></td>
<td width="50%"></td>
</tr>
<tr>
<td colspan="2" align="center"><asp:Label ID="lblMsg" runat="server" ></asp:Label></td>
</tr>
<tr>
<td align="right">Name :</td>
<td align="left"><asp:TextBox ID="txt_Nme" runat="server" ></asp:TextBox></td>
</tr>
<tr>
<td align="right">Address : </td>
<td align="left"><asp:TextBox ID="txt_Add" runat="server" ></asp:TextBox></td>
</tr>
<tr>
<td align="right">Phone number : </td>
<td align="left"><asp:TextBox ID="txt_Pnumber" runat="server" ></asp:TextBox></td>
</tr>
<tr>
<td colspan="2" align="center"><asp:Button ID="btn_Submit" runat="server"
Text="Submit" onclick="btn_Submit_Click" /></td>
</tr>
</table>
</div>
</form>
</body>
</html>
On server side
protected void btn_Submit_Click(object sender, EventArgs e)
{
try
{
string _Name = txt_Nme.Text.Trim();
string _Add = txt_Add.Text.Trim();
string _Pnumber = txt_Pnumber.Text.Trim();
string _Conn = "PWD=****;UID=sa;Initial Catalog=[DB Name];Data Source=localhost";
SqlConnection con = new SqlConnection(_Conn);
con.Open();
SqlCommand cmd = new SqlCommand("sp_SIne", con);
cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Add(new SqlParameter("@Pk_Id", SqlDbType.Int, 4));
// cmd.Parameters["@Pk_Id"].Value = 0;
cmd.Parameters.Add(new SqlParameter("@Str_name", SqlDbType.NVarChar, 20));
cmd.Parameters["@Str_name"].Value = _Name;
cmd.Parameters.Add(new SqlParameter("@Str_Add", SqlDbType.NVarChar, 15));
cmd.Parameters["@Str_Add"].Value = _Add;
cmd.Parameters.Add(new SqlParameter("@Str_Pnumber", SqlDbType.NVarChar, 15));
cmd.Parameters["@Str_Pnumber"].Value = _Pnumber;
cmd.Parameters.Add(new SqlParameter("@Pk_Id", SqlDbType.Int, 4));
cmd.Parameters["@Pk_Id"].Direction = ParameterDirection.Output;
int d = cmd.ExecuteNonQuery();
int userid = (int)cmd.Parameters["@Pk_Id"].Value;
lblMsg.Text = userid.ToString();
con.Close();
}
catch (Exception ex)
{
lblMsg.Text = ex.Message.ToString();
}
}
On Store ProcedureALTER procedure sp_SIne
(
@Str_name nvarchar(50)=null,
@Str_Add nvarchar(50)=null,
@Str_Pnumber nvarchar(50)=null,
@Pk_Id int output
)
As
insert into mst_Table
(
Str_name,
Str_Add,
Str_Pnumber
)
values
(
@Str_name,
@Str_Add,
@Str_Pnumber
)
set @Pk_Id=cast(SCOPE_IDENTITY() as INT)
Hope u got it...If u have any trouble ask me...
Stay tune..
Have a nice day... 'N happy Coding :)
No comments:
Post a Comment