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.ExecuteNonQ uery without any details. Can anyone please help me.. Im stuck on it since 2 days

Thanks



Stored Procedure

CREATE PROCEDURE dbo.sp_GetInfor mation
(
@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\SQLEXP RESS;Initial Catalog=Stud;In tegrated Security=True") ;
SqlCommand cmd = new SqlCommand("sp_ GetInformation" , conn);
cmd.CommandType = CommandType.Sto redProcedure;

SqlParameter usr_param = cmd.Parameters. Add("@Username" , SqlDbType.VarCh ar);
SqlParameter addr_param = cmd.Parameters. Add("@Address", SqlDbType.VarCh ar);
SqlParameter phone_param = cmd.Parameters. Add("@Phone", SqlDbType.Decim al);
SqlParameter mail_param = cmd.Parameters. Add("@Email", SqlDbType.VarCh ar);
SqlParameter emer_param = cmd.Parameters. Add("@Emergency ", SqlDbType.Decim al);

usr_param.Direc tion = ParameterDirect ion.Input;
addr_param.Dire ction = ParameterDirect ion.Output;
phone_param.Dir ection = ParameterDirect ion.Output;
mail_param.Dire ction = ParameterDirect ion.Output;
emer_param.Dire ction = ParameterDirect ion.Output;

usr_param.Value = Piname.Text;

if (conn.State.Equ als(ConnectionS tate.Closed))
{
conn.Open();
cmd.ExecuteNonQ uery();
}
Piaddr.Text = addr_param.Valu e.ToString();
Piemer.Text = emer_param.Valu e.ToString();
Pimail.Text = mail_param.Valu e.ToString();
Piphone.Text = phone_param.Val ue.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.ExecuteNonQ uery();
}
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.