CODEWITHSUNDEEP
c#winformsvisual-studiot-sql
Knightwisp
Knightwisp

Reputation: 325

Why does calling Stored Procedure in c# give InvalidOperationException when runs fine in SSMS?

I have the following method calling an SP from C# app:

public bool CheckEmail(string email)
    {
        bool success = false;
        string name;


            using (SqlConnection con = new SqlConnection(Internal_Audit_Capture.Properties.Settings.Default.Intenal_AuditDB))
            {
                con.Open(); 
                try
                {
                    using (SqlCommand command = new SqlCommand("IntAudit.p_checkEmail", con))
                    {


                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(new SqlParameter("@email", maskedTextBox1.Text.Trim()));
                        command.Parameters.Add("@success", SqlDbType.Bit).Direction = ParameterDirection.Output;
                        command.Parameters.Add("@name", SqlDbType.VarChar).Direction = ParameterDirection.Output;
                        command.ExecuteNonQuery();
                        success = (bool)command.Parameters["@success"].Value;
                        name = (string)command.Parameters["@name"].Value;
                    }

                    return success;
                }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                    return success;
                }
            }
    }

p_checkEmail queries the following table:

[IntAudit].[LOGINDETAILS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](30) NOT NULL,
[PASSWORD] [nvarchar](30) NOT NULL,
[ADMIN] [bit] NULL,
[EMAIL] [varchar](50) NOT NULL,PRIMARY KEY CLUSTERED ([ID] ASC))

And it's code is:

create procedure IntAudit.p_checkEmail
@email varchar(50),
@success bit OUTPUT,
@name varchar(30) OUTPUT
as

set nocount on;

if(exists(select 1 from [IntAudit].[LOGINDETAILS] where [EMAIL] = @email))
 begin
  select @name = [NAME] from [IntAudit].[LOGINDETAILS] where [EMAIL] = @email
  set @success = 1
  return
 end
else
 begin
  set @name = null
  set @success = 0
  return
 end

go

Running the checkEmail method gives a System.InvalidOperationException: String[2]: size property has invalid size of 0 error. Executing the procedure in SSMS runs fine though. The method is mean't to check for an email address in the table and return the username, if the address entered by the user exists. I have tried changing the data types but I still get the same error. Is there anything I am missing?

EDIT: Exception Details:

System.InvalidOperationException: String[2]: the Size property has an invalid size of 0.

at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)

at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)

at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Internal_Audit_Capture.ForgotPassword.CheckEmail(String email) in C:\Internal Audit\Internal Audit Capture\Internal Audit Capture\ForgotPassword.cs:line 41

Upvotes: 1

Views: 1341

Answers (2)

mh__
mh__

Reputation: 376

You need to specify the size of the @name parameter.

command.Parameters.Add("@name", SqlDbType.VarChar, 30).Direction = ParameterDirection.Output;

Upvotes: 6

Sam
Sam

Reputation: 2917

Try this 

command.Parameters.Add("@email", SqlDbType.VarChar).Value = maskedTextBox1.Text.Trim();

instead of 

command.Parameters.Add(new SqlParameter("@email", maskedTextBox1.Text.Trim()));

Upvotes: 0

Related Questions