Using stored procedure with asp.net c#

Ross-C
Ross-C used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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

Commented:

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

Top Expert 2013

Commented:
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:
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Top Expert 2013

Commented:
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:

Author

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

Author

Commented:
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
Top Expert 2013

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

Author

Commented:
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

Top Expert 2013

Commented:
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();
        }

Author

Commented:
sorry i don't understand?
Top Expert 2013

Commented:
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();
        }

Author

Commented:
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
Top Expert 2013
Commented:
aha....need to set size attribute like below:

returnflag.Direction = ParameterDirection.Output;
-----> returnflag.Size = 1;
cmd.Parameters.Add(returnflag);

Ok I think this is the final code and should work:

protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection Connection1 = new SqlConnection("constring");
            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;
        returnflag.Size = 1; //1 as you are returning 'T' or 'F'
        cmd.Parameters.Add(returnflag);
        SqlDataReader reader = cmd.ExecuteReader();
        reader.Close();
        Label1.Text = cmd.Parameters["@returnflag"].Value.ToString();
        }


Now I am not sure about deleting the connectionstring...you might want to contact the moderator.

Author

Commented:
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.
Top Expert 2013

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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial