create table tbl_user
(
userID int Primary Key identity (1,1),
userName varchar(20),
pass varchar(30),
email varchar(50),
userRole int,
userPhoto varchar(100),
userStatus int,
);
//my store procedure is
create PROCEDURE spCreateUser
@userName varchar(20),
@pass varchar(30),
@email varchar(50),
@userRole int,
@userPhoto varchar(100),
@userStatus int,
@result int output
AS
BEGIN
IF EXISTS(SELECT * FROM tbl_user WHERE userName= @userName)
SET @result = 1
ELSE
BEGIN
SET @result = 0
INSERT INTO tbl_user(userName,pass,email,userRole,userPhoto,userStatus)
VALUES(@userName,@pass,@email,@userRole,@userPhoto,@userStatus)
END
return @result
END
my asp.net c# code protected void btn_insert_Click(object sender, EventArgs e) { string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; string path = string.Concat(Server.MapPath("~/UploadFile/" + fu_photo.FileName)); fu_photo.SaveAs(path); using (SqlConnection con = new SqlConnection(CS)) { SqlCommand cmd = new SqlCommand("spCreateUser", con); cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("userName", txt_username.Text); cmd.Parameters.AddWithValue("pass", txt_pass.Text); cmd.Parameters.AddWithValue("email", txt_email.Text); cmd.Parameters.AddWithValue("userRole", ddl_role.SelectedValue); cmd.Parameters.AddWithValue("userPhoto", path); cmd.Parameters.AddWithValue("userStatus", ddl_status.SelectedValue); var result = cmd.Parameters.Add("@result", SqlDbType.Int); //cmd.Parameters.AddWithValue("output", result); result.Direction = ParameterDirection.ReturnValue; con.Open(); int k = cmd.ExecuteNonQuery(); if (k != 0) { lblMsg.Text = "Record Inserted Succesfully into the Database"; lblMsg.ForeColor = System.Drawing.Color.CornflowerBlue; }
con.Close(); } }
This code work perfectly but problem is that how to print message when not insert value in database message show that user already exists in table.
using (SqlConnection con = new SqlConnection("CONNECTION_STRING_HERE"))
{ con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "spCreateUser";
cmd.CommandType = CommandType.StoredProcedure;
// OTHER PARAMS HERE
cmd.Parameters.Add("@result", SqlDbType.Int).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery();
if (cmd.Parameters["@result"].Value != null)
{
var result = (int)cmd.Parameters["@result"].Value;
if (result == 1)
{
//print something
}
else
{
//print something else
}
}
//-- NOTE: when you use the using statement you don't have to close the connection explicitly.
// The using statement ensures that Dispose is called even if an exception occurs
}
}
None
0 Points
5 Posts
How to insert & edit unique value using store procedure
Apr 09, 2020 10:10 PM|umeshdaiya@gmail.com|LINK
my sql server database and store procedure
Contributor
4232 Points
1147 Posts
Re: How to insert & edit unique value using store procedure
Apr 10, 2020 07:23 AM|Kulrom|LINK
HTH
My website: ASP.NET Custom Software Development
None
0 Points
5 Posts
Re: How to insert & edit unique value using store procedure
Apr 10, 2020 11:28 AM|umeshdaiya@gmail.com|LINK
Thanks for reply, as per directed by you code is not work plz tell me what is missing by me.
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
string path = string.Concat(Server.MapPath("~/UploadFile/" + fu_photo.FileName));
fu_photo.SaveAs(path);
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("spCreateUser1", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("userName", txt_username.Text);
cmd.Parameters.AddWithValue("pass", txt_pass.Text);
cmd.Parameters.AddWithValue("email", txt_email.Text);
cmd.Parameters.AddWithValue("userRole", ddl_role.SelectedValue);
cmd.Parameters.AddWithValue("userPhoto", path);
cmd.Parameters.AddWithValue("userStatus", ddl_status.SelectedValue);
cmd.Parameters.Add("@result", SqlDbType.Int).Direction = ParameterDirection.Output;
if (cmd.Parameters["@result"].Value != null)
{
var result = (bool)cmd.Parameters["@result"].Value;
if (result == true)
{
lblMsg.Text = "Record Inserted Succesfully into the Database";
lblMsg.ForeColor = System.Drawing.Color.CornflowerBlue;
}
else
{
lblMsg.Text = "Record Inserted Succesfully into the Database";
lblMsg.ForeColor = System.Drawing.Color.CornflowerBlue;
}
}
Thanks & Regards
Contributor
4232 Points
1147 Posts
Re: How to insert & edit unique value using store procedure
Apr 10, 2020 11:51 AM|Kulrom|LINK
what is not working?
From what I see here you have the same code in the if and else. It should be like the following:
in addition you do not open the connection. add con.Open(); after the using statement
My website: ASP.NET Custom Software Development
None
0 Points
5 Posts
Re: How to insert & edit unique value using store procedure
Apr 10, 2020 11:58 AM|umeshdaiya@gmail.com|LINK
but code not reached at this if else statement and not error found. i changed message but still not work.
None
0 Points
5 Posts
Re: How to insert & edit unique value using store procedure
Apr 10, 2020 11:59 AM|umeshdaiya@gmail.com|LINK
is there no need to execute scalar or other statement which execute store procedure.
Thanks and regards
Contributor
4232 Points
1147 Posts
Re: How to insert & edit unique value using store procedure
Apr 11, 2020 08:28 AM|Kulrom|LINK
OMG, I totally forgot that. Please add the following just bellow the params adding portion:
cmd.ExecuteNonQuery();
P.S. I edited my original reply. Just add the other params and test it AS IS
My website: ASP.NET Custom Software Development