How to insert & edit unique value using store procedure

Question

0.00/5 (No votes)

See more:

my table is like this

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

asp.net c# code like this

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);
             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();  
         }
    }

What I have tried:

i would like to save unique username and password in table using storeprocedture please also tell update opration too. how i use this sp with in my code.
Thanks

Posted 9-Apr-20 0:39am

UDTWS

Updated 9-Apr-20 1:24am

Add a Solution

Comments

CHill60 9-Apr-20 6:45am

You are already using the sp in your code. Do not store passwords on the database.

UDTWS 9-Apr-20 6:52am

Thanks for reply,
but this code is not working due to not know how exact i use this sp. got error message you not pass result.

CHill60 9-Apr-20 7:20am

You need to quote the exact error message

UDTWS 9-Apr-20 9:53am

Procedure or function 'spCreateUser' expects parameter '@result', which was not supplied.

UDTWS 9-Apr-20 6:53am

i will store my password with encryption. and if not save in database than what should i do.

CHill60 9-Apr-20 7:20am

See this CP article - Salted Password Hashing - Doing it Right[^]

UDTWS 9-Apr-20 10:16am

i change in my store procedure as below
alter PROCEDURE spCreateUser
@userName varchar(20),
@pass varchar(30),
@email varchar(50),
@userRole int,
@userPhoto varchar(100),
@userStatus int

AS
IF (SELECT COUNT(*) FROM tbl_user WHERE userName= @userName) < 1
BEGIN
INSERT INTO tbl_user(userName,pass,email,userRole,userPhoto,userStatus)
VALUES(@userName,@pass,@email,@userRole,@userPhoto,@userStatus)
END

it works but if not insert value due to duplicate how mange error message in C# any method

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2020-04-09T01:24:00

Solution 1

Your stored procedure is trying to return parameter @result but you have not set it up. Do this

C#

. . .
cmd.Parameters.AddWithValue("userStatus", ddl_status.SelectedValue);

var result = cmd.Parameters.Add("@result", SqlDbType.Int);
    result.Direction = ParameterDirection.ReturnValue;

con.Open();
. . .

To handle passwords properly see this CodeProject article Salted Password Hashing - Doing it Right[^]

Posted 9-Apr-20 1:24am

CHill60

Updated 9-Apr-20 6:47am

v2

Comments

UDTWS 9-Apr-20 9:57am

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);

returnParameter.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();
}

now error is :-
The name 'returnParameter' does not exists in the current context.

CHill60 9-Apr-20 12:47pm

Sorry - my bad, I renamed my example to use your parameter names and missed that one. I've updated my solution - that should have read

result.Direction = ParameterDirection.ReturnValue;

UDTWS 9-Apr-20 18:33pm

Thanks for reply but still Problem not solved. Procedure or function 'spCreateUser' expects parameter '@result', which was not supplied. and i given another sp than duplicate user not entered but how message show that user already exists.

UDTWS 10-Apr-20 9:50am

from two days i am try different different method to solve problem but still problem not solved. please check once again my code and help to resolve issue.
Thanks & Regards

CHill60 16-Apr-20 3:59am

If you've tried a different method then you will need to update your question with the new code or raise a new question