2
Answers

problem with output parameter of stored procedure

I am getting error
System.Data.SqlClient.SqlException: Procedure or Function 'sp_login' expects parameter '@Results', which was not supplied. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at login.signin_Click(Object sender, ImageClickEventArgs e) in d:\Careforindiafinal\login.aspx.cs:line 40


my C# code is
SqlConnection myConnection;
            connection dbconnection = new connection();
            myConnection = dbconnection.connectiondb();
            SqlCommand myCommand = new SqlCommand("[careforindia].[dbo].[sp_login]", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Connection = myConnection;
            myCommand.CommandText = "sp_login";
            myCommand.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar,255, "username"));
            myCommand.Parameters.Add(new SqlParameter("@password", SqlDbType.VarChar,255, "password"));
            myCommand.Parameters["@username"].Value = username.Value;
            myCommand.Parameters["@password"].Value = password.Value;
           // myCommand.Parameters.Add(new SqlParameter("@Result", SqlDbType.Int, 5, ParameterDirection.Output, false, 0, 50, "Result", DataRowVersion.Default, null));
            SqlParameter Result = myCommand.Parameters.Add("@Result", SqlDbType.Int, 5);
            Result.Direction = ParameterDirection.Output;
           int res=1;
            myCommand.ExecuteNonQuery();
           res = (int)myCommand.Parameters["@Result"].Value;

My Procedure is
ALTER PROCEDURE [dbo].[sp_login]
    -- Add the parameters for the stored procedure here
    @username varchar ,
    @password varchar ,
    @Results int Output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
Set @Results = (Select count(*) from [dbo].[user] where username = @username and password = password )

END


Can anyone help me to remove the error
for creating database connection i have created seprate class

Answers (2)