CODEWITHSUNDEEP
c#asp.netstored-procedures
Chee mun Low
Chee mun Low

Reputation: 107

Could not find stored procedure on update

what i want to do is while user click the link, the clicks will increase by 1 but i failed to to do=(

output

Could not find stored procedure 'updateMovieClicks'How to win in share?','64'

my stored procedure

ALTER PROCEDURE updateMovieClicks
(
@movieTitle varchar(50),
@movieClicks int
)
AS
update MovieListTable set movieClicks=@movieClicks where movieTitle=@movieTitle;

my programs code on page load

conn.Open();
SqlCommand cmdIncreaseMovieClicks = new SqlCommand("updateMovieClicks'" +
    Session["videoName"].ToString() + "','" + clicksIncrease + "'", conn);
SqlParameter movieTitle = new SqlParameter();
SqlParameter movieClicks = new SqlParameter();
cmdIncreaseMovieClicks.CommandType = CommandType.StoredProcedure;
cmdIncreaseMovieClicks.Parameters.Add("@movieClick", SqlDbType.Int).Value 
= clicksIncrease+1;
cmdIncreaseMovieClicks.ExecuteNonQuery();
conn.Close();

Upvotes: 0

Views: 1833

Answers (2)

Curtis
Curtis

Reputation: 103348

Your error is ocurring because the system thinks you are trying to find a stored procedure called 'updateMovieClicks'How to win in share?','64'. This is because you have concatenated your parameters in a string with your stored procedure name.

When specifying that a command is of type StoredProcedure then you add your parameters seperately to the command text. Therefore only the stored procedure name goes in the command text, and the parameters are added as seperate objects to the SqlCommand.

ALTER PROCEDURE updateMovieClicks
(
@movieTitle varchar(50)
)
AS
update MovieListTable set movieClicks=(movieClicks+1) where movieTitle=@movieTitle;


SqlCommand cmdIncreaseMovieClicks = new SqlCommand("updateMovieClicks", conn);
cmdIncreaseMovieClicks.CommandType = CommandType.StoredProcedure;
cmdIncreaseMovieClicks.Parameters.Add("@movieTitle", SqlDbType.nvarchar).Value 
= session["videoName"].tostring();
conn.Open();
cmdIncreaseMovieClicks.ExecuteNonQuery();
conn.Close();

Here I've also improved your Stored Procedure query so that the movieClicks is calculated at SQL level.

Upvotes: 2

p.campbell
p.campbell

Reputation: 100567

You only need to specify the stored proc name in your SqlCommand's constructor.

var cmd = new SqlCommand("updateMovieClicks",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@movieTitle", Session["videoName"].ToString() );
cmd.Parameters.AddWithValue("@movieClicks", int.Parse(clicksIncrease));

Upvotes: 0

Related Questions