Retrieving data using SQL stored procedure in C# application

[smartin]

Hi,

I'm having problem retrieving data from an SQL stored procedure. I tried debugging but it wont give a the reason for the error. it just throws an exception after executing cmd.ExecuteNonQuery without any details. Can anyone please help me.. Im stuck on it since 2 days

Thanks



Stored Procedure

CREATE PROCEDURE dbo.sp_GetInformation
(
@Username VARCHAR(50) = NULL
)

AS
SELECT Address, Phone, Emergency, Email
FROM Pinfo
WHERE Username = @Username





Background C# Code
SqlConnection conn = new SqlConnection(@"Data Source=R\SQLEXPRESS;Initial Catalog=Stud;Integrated Security=True");
SqlCommand cmd = new SqlCommand("sp_GetInformation", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter usr_param = cmd.Parameters.Add("@Username", SqlDbType.VarChar);
SqlParameter addr_param = cmd.Parameters.Add("@Address", SqlDbType.VarChar);
SqlParameter phone_param = cmd.Parameters.Add("@Phone", SqlDbType.Decimal);
SqlParameter mail_param = cmd.Parameters.Add("@Email", SqlDbType.VarChar);
SqlParameter emer_param = cmd.Parameters.Add("@Emergency", SqlDbType.Decimal);

usr_param.Direction = ParameterDirection.Input;
addr_param.Direction = ParameterDirection.Output;
phone_param.Direction = ParameterDirection.Output;
mail_param.Direction = ParameterDirection.Output;
emer_param.Direction = ParameterDirection.Output;

usr_param.Value = Piname.Text;

if (conn.State.Equals(ConnectionState.Closed))
{
conn.Open();
cmd.ExecuteNonQuery();
}
Piaddr.Text = addr_param.Value.ToString();
Piemer.Text = emer_param.Value.ToString();
Pimail.Text = mail_param.Value.ToString();
Piphone.Text = phone_param.Value.ToString();
conn.Close();

[nateraaaa]

If you add a try catch finally block around your code you will have an easier time finding out what the problem is.

try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
string message = ex.Message;
}
finally
{
conn.Close();
}

Nathan

[Plater]

Your stored procedure does not contain all of those parameters.
It only contains one input parameter.
You will need to correct your stored procedure to have those output parameters if you want to retreive all those values.

[nateraaaa]

Your stored procedure does not contain all of those parameters.
It only contains one input parameter.
You will need to correct your stored procedure to have those output parameters if you want to retreive all those values.

I agree with Plater. However if you are only concerned with the parameter used in the stored procedure you will need to modify your code to only pass a single parameter via the SqlCommand object.

nathan

[Plater]

It should also be noted, that since you are in fact executing a query, using ExecuteNonQuery() is probably not the correct call. You are mixing two styles together.

If you want to go with a stored procedure with only one parameter, like you are now:
You could use a DataTable and an SqlDataAdapter in your backend code to retreive the data.

If you want to go with a stored procedure that just returns parameters, like your backend code indicates:
You need to correct your SQL procedure to have the correct output parameters and change the query statement to set the parameters inside the SELECT section.