Link to home
Start Free TrialLog in
Avatar of akohan
akohan

asked on

How to call a stored procedure?


Hello,

I have a stored procedure and I call it from C# code. However, I get an error as:

Could not find stored procedure 'xxx'

Below is the code and also run it from sql server itself using

exec dbo.proc_LogIn  @Username = 'user1', @Password = 'password1', @OutRes=1;

Can be permission issue from C# side?

Any help is appreciated.

Thanks.




public int Validate_Login(String strUserName, String strPassword)
        {
            SqlConnection con = new SqlConnection(@"server=myMachine\SQLEXPRESS;integrated security=true");
            SqlCommand cmdselect = new SqlCommand();
            cmdselect.CommandType = CommandType.StoredProcedure;
            cmdselect.CommandText =  "[dbo].[proc_LogIn]";
            cmdselect.Parameters.Add("@Username", SqlDbType.VarChar, 50).Value = strUserName;
            cmdselect.Parameters.Add("@UPassword", SqlDbType.VarChar, 50).Value = strPassword;
            cmdselect.Parameters.Add("@OutRes", SqlDbType.Int, 4);
            cmdselect.Parameters["@OutRes"].Direction = ParameterDirection.Output;
            cmdselect.Connection = con;
            int Results = 0;
            try
            {
                con.Open();
                cmdselect.ExecuteNonQuery();  //THIS LINE THROWS ERROR
                Results = (int)cmdselect.Parameters["@OutRes"].Value;
            }
           catch (SqlException ex)
            {
                lblMessage.Text = ex.Message;
            }
            finally
            {
                cmdselect.Dispose();
                if (con != null)
                {
                    con.Close();
                }
            }
            return Results;
        }

Open in new window

SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of anjos
anjos

add "Initial Catalog=FAQ" to the connection string (SqlConnection con)

and in the cmdselect.CommandText write the name of the procedure, ie:
cmdselect.CommandText = "proc_LogIn"
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of akohan

ASKER


Thank you to all for the help.

Regards.
Avatar of akohan

ASKER

Thanks.