asked on

Using stored procedure with asp.net c#

I am new to .net. I know about the membership provider but just as a learning exercise i am trying to create a basic login using a stored procedure and hoped somebody could give my code a once over. The error i am getting is that the sproc expects a value for @returnflag.

Many Thanks in advance

SqlConnection Connection1 = new SqlConnection("xxxdeleted for securityxxxx");
            Connection1.Open();
            SqlCommand cmd = new SqlCommand("CheckValid", Connection1);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@Username",txtUser.Text));
            cmd.Parameters.Add(new SqlParameter("@Password",txtPass.Text));
            SqlParameter returnflag = new SqlParameter("@returnflag", SqlDbType.Char); 
            returnflag.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(returnflag);
            SqlDataReader reader = cmd.ExecuteReader();
            string returnvalue;
            returnvalue = returnflag.ToString();
            Label1.Text = returnvalue;
 
this is my sproc below:
@Username nvarchar(15),
@Password nvarchar(15),
@returnflag nvarchar output
as
 
 
 
select * from dbo.contact where username=@Username and password=@Password
 
 
if @@rowcount>=1
Begin
     set @returnflag='T'
      
end
 
else
 
Begin
      set @returnflag='F'
end

Open in new window

jinal

returnflag.Direction = ParameterDirection.Output;

SqlConnection Connection1 = new SqlConnection("xxxdeleted for securityxxxx");
            Connection1.Open();
            SqlCommand cmd = new SqlCommand("CheckValid", Connection1);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@Username",txtUser.Text));
            cmd.Parameters.Add(new SqlParameter("@Password",txtPass.Text));
            SqlParameter returnflag = new SqlParameter("@returnflag", SqlDbType.Char); 
            returnflag.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(returnflag);
            SqlDataReader reader = cmd.ExecuteReader();
            string returnvalue;
            returnvalue = returnflag.ToString();
            Label1.Text = returnvalue;
 
this is my sproc below:
@Username nvarchar(15),
@Password nvarchar(15),
@returnflag nvarchar output
as
 
 
 
select * from dbo.contact where username=@Username and password=@Password
 
 
if @@rowcount>=1
Begin
     set @returnflag='T'
      
end
 
else
 
Begin
      set @returnflag='F'
end

Open in new window

jinal

SqlConnection Connection1 = new SqlConnection("xxxdeleted for securityxxxx");
            Connection1.Open();
            SqlCommand cmd = new SqlCommand("CheckValid", Connection1);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@Username",txtUser.Text));
            cmd.Parameters.Add(new SqlParameter("@Password",txtPass.Text));
            SqlParameter returnflag = new SqlParameter("@returnflag", SqlDbType.Char); 
            returnflag.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(returnflag);
            SqlDataReader reader = cmd.ExecuteReader();
            reader.Close();   
            string returnvalue;
          
            returnvalue = returnflag.Value.ToString();
            Label1.Text = returnvalue;
 
this is my sproc below:
@Username nvarchar(15),
@Password nvarchar(15),
@returnflag nvarchar output
as
 
 
 
select * from dbo.contact where username=@Username and password=@Password
 
 
if @@rowcount>=1
Begin
     set @returnflag='T'
      
end
 
else
 
Begin
      set @returnflag='F'
end

Open in new window

guru_sami

This is how you can retrieve the value for return parameter:

Label1.Text = cmd.Parameters["@returnflag"].Value.ToString();

Note: you do have to set ---> returnflag.Direction = ParameterDirection.Output;
as mentioned in code by jinal:

guru_sami

I would modify my statement:
----> This is how you can retrieve the value for return parameter:
To
--->This is how you can retrieve the value for OUTPUT parameter:

Ross-C

ASKER

thanks for the responses, i have just got home and will try them shortly.

Ross-C

ASKER

I have tried the suggestions but im still getting the same error

Procedure 'CheckValid' expects parameter '@returnflag', which was not supplied.

any other suggestions much appreciated thanks

guru_sami

how about posting the final code you have ...to see whats going on...

Ross-C

ASKER

thanks, i dont mind re-writing it if i need to.

       protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection Connection1 = new SqlConnection("Data Source=FMSQLSERVER;Initial Catalog=ClearAims;Persist Security Info=True;User ID=r.calvert;Password=17craig");
            Connection1.Open();
            SqlCommand cmd = new SqlCommand("CheckValid", Connection1);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@Username", txtUser.Text));
            cmd.Parameters.Add(new SqlParameter("@Password", txtPass.Text));
            SqlParameter returnflag = new SqlParameter("@returnflag", SqlDbType.Char);
            //returnflag.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(returnflag);
            SqlDataReader reader = cmd.ExecuteReader();
            string returnvalue;
            //returnvalue = returnflag.ToString();
            Label1.Text = cmd.Parameters["@returnflag"].Value.ToString(); 
        }

Open in new window

guru_sami

You did not mention the ParameterDirection.OutPut

protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection Connection1 = new SqlConnection("Data Source=FMSQLSERVER;Initial Catalog=ClearAims;Persist Security Info=True;User ID=r.calvert;Password=17craig");
Connection1.Open();
SqlCommand cmd = new SqlCommand("CheckValid", Connection1);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Username", txtUser.Text));
cmd.Parameters.Add(new SqlParameter("@Password", txtPass.Text));
SqlParameter returnflag = new SqlParameter("@returnflag", SqlDbType.Char);
returnflag.Direction = ParameterDirection.OutPut;
cmd.Parameters.Add(returnflag);
SqlDataReader reader = cmd.ExecuteReader();
Label1.Text = cmd.Parameters["@returnflag"].Value.ToString();
}

Ross-C

ASKER

sorry i don't understand?

guru_sami

Your code:
//returnflag.Direction = ParameterDirection.ReturnValue;
chaged to:
returnflag.Direction = ParameterDirection.Output;

Try this one:
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection Connection1 = new SqlConnection("Data Source=FMSQLSERVER;Initial Catalog=ClearAims;Persist Security Info=True;User ID=r.calvert;Password=17craig");
Connection1.Open();
SqlCommand cmd = new SqlCommand("CheckValid", Connection1);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Username", txtUser.Text));
cmd.Parameters.Add(new SqlParameter("@Password", txtPass.Text));
SqlParameter returnflag = new SqlParameter("@returnflag", SqlDbType.Char);
returnflag.Direction = ParameterDirection.Output;
cmd.Parameters.Add(returnflag);
SqlDataReader reader = cmd.ExecuteReader();
Label1.Text = cmd.Parameters["@returnflag"].Value.ToString();
}

Ross-C

ASKER

thanks for all your help, i now get the error

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

PS is there anyway i can remove the connection string from the above posts i know there's no fqdn but i didn't mean to post it.

Cheers

ASKER CERTIFIED SOLUTION

guru_sami

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ross-C

ASKER

I think we are nearly there i now get

object reference not set to an instance of an object

i really appreciate your help on this.

guru_sami

did you use the exact code above? compare it line by line...or post what you have again here

Ross-C

ASKER

Thankyou very much, youve been a great help i must have mistyped your solution.