Ross-C
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
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
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
This is how you can retrieve the value for return parameter:
Label1.Text = cmd.Parameters["@returnfla g"].Value. ToString() ;
Note: you do have to set ---> returnflag.Direction = ParameterDirection.Output;
as mentioned in code by jinal:
Label1.Text = cmd.Parameters["@returnfla
Note: you do have to set ---> returnflag.Direction = ParameterDirection.Output;
as mentioned in code by jinal:
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:
----> This is how you can retrieve the value for return parameter:
To
--->This is how you can retrieve the value for OUTPUT parameter:
ASKER
thanks for the responses, i have just got home and will try them shortly.
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
Procedure 'CheckValid' expects parameter '@returnflag', which was not supplied.
any other suggestions much appreciated thanks
how about posting the final code you have ...to see whats going on...
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();
}
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=17cr aig");
Connection1.Open();
SqlCommand cmd = new SqlCommand("CheckValid", Connection1);
cmd.CommandType = CommandType.StoredProcedur e;
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(returnf lag);
SqlDataReader reader = cmd.ExecuteReader();
Label1.Text = cmd.Parameters["@returnfla g"].Value. ToString() ;
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection Connection1 = new SqlConnection("Data Source=FMSQLSERVER;Initial
Connection1.Open();
SqlCommand cmd = new SqlCommand("CheckValid", Connection1);
cmd.CommandType = CommandType.StoredProcedur
cmd.Parameters.Add(new SqlParameter("@Username", txtUser.Text));
cmd.Parameters.Add(new SqlParameter("@Password", txtPass.Text));
SqlParameter returnflag = new SqlParameter("@returnflag"
returnflag.Direction = ParameterDirection.OutPut;
cmd.Parameters.Add(returnf
SqlDataReader reader = cmd.ExecuteReader();
Label1.Text = cmd.Parameters["@returnfla
}
ASKER
sorry i don't understand?
Your code:
//returnflag.Direction = ParameterDirection.ReturnV alue;
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=17cr aig");
Connection1.Open();
SqlCommand cmd = new SqlCommand("CheckValid", Connection1);
cmd.CommandType = CommandType.StoredProcedur e;
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(returnf lag);
SqlDataReader reader = cmd.ExecuteReader();
Label1.Text = cmd.Parameters["@returnfla g"].Value. ToString() ;
}
//returnflag.Direction = ParameterDirection.ReturnV
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
Connection1.Open();
SqlCommand cmd = new SqlCommand("CheckValid", Connection1);
cmd.CommandType = CommandType.StoredProcedur
cmd.Parameters.Add(new SqlParameter("@Username", txtUser.Text));
cmd.Parameters.Add(new SqlParameter("@Password", txtPass.Text));
SqlParameter returnflag = new SqlParameter("@returnflag"
returnflag.Direction = ParameterDirection.Output;
cmd.Parameters.Add(returnf
SqlDataReader reader = cmd.ExecuteReader();
Label1.Text = cmd.Parameters["@returnfla
}
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
object reference not set to an instance of an object
i really appreciate your help on this.
did you use the exact code above? compare it line by line...or post what you have again here
ASKER
Thankyou very much, youve been a great help i must have mistyped your solution.
Open in new window