i want to retrieve values from database using stored procedure,compare those values to the parameter supplied in the query string...if the value retrieved from the database using stored procedure equals the parameter supplied in the query string an updation should take place and reflect that changes to my database.......
i have 2 columns named activated and user id in my database,using stored procedure user id should be retrieved and compare that value with parameter supplied in query string and if the comparison is successful an updation of column "activated" should be done......... how the whole thing gets done using stored procedure
Loading
Nilanka DharmadasaPosted Dec 22, 2009, 11:37 PM
Hi kiran,
According to my stroed procedure,
- user id is passed as a parameter.
-If that userid exists, activated column is updated to 1 and return 1. (If you want, you can pass the value for activated column also as a parameter.)
-If userid does not exist, it returns -1.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mysp]
@user_id int
AS
BEGIN
DECLARE @l_count INT
SET @l_count = (SELECT COUNT (user_id_column_name)
FROM tabelname
WHERE (user_id_column_name = @user_id))
if(@l_count)>0
Begin
UPDATE tabelname
SET
activated = 1
WHERE user_id_column_name = @user_id
return 1;
End
return -1;
GO
If you find my answer useful, please tick 'Do you like this answer' checkbox.
Nilanka DharmadasaPosted Dec 23, 2009, 4:27 AM
Here is the code.
int user_id = 145;//This is the user_id
SqlConnection sqlConn = new SqlConnection("your connectionstring here");
sqlConn.Open();
SqlCommand sqlCommand = new SqlCommand("sp_name", sqlConn);
sqlCommand.CommandType=CommandType.StoredProcedure;
SqlParameter sp1 = new SqlParameter("@result", 0);
sp1.Direction=ParameterDirection.Output;
sqlCommand.Parameters.Add(sp1);
sqlCommand.Parameters.Add(new SqlParameter("@user_id",user_id));
sqlCommand.CommandTimeout = 0;
sqlCommand.ExecuteNonQuery();
int result = Int32.Parse(sp1.Value.ToString());
if(result == 1)
{
MessageBox.Show("Successful");
}
else
MessageBox.Show("Error");
sqlConn.Close();
kiran kumarPosted Dec 23, 2009, 2:32 AM
your answer is appreciated, you are very helpful to me........
so i ll try execute this code now....one more doubt regarding my c# program.....how should i pass parameters from my program....just give any rough idea or if there is any article with you please post here
thank you my friend
kiran kumarPosted Dec 22, 2009, 11:23 PM
i am using sqlserver
kiran kumarPosted Dec 22, 2009, 11:22 PM
i am using sqlserver
Nilanka DharmadasaPosted Dec 22, 2009, 11:17 PM
What's your database?
SQL Server? Oracle ? Or something else?
Mention that. Ill help you.