468,269 Members | 1,554 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,269 developers. It's quick & easy.

Retrieving data using SQL stored procedure in C# application

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();
Jan 27 '08 #1
4 18423
nateraaaa
663 Expert 512MB
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
Jan 28 '08 #2
Plater
7,872 Expert 4TB
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.
Jan 28 '08 #3
nateraaaa
663 Expert 512MB
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
Jan 28 '08 #4
Plater
7,872 Expert 4TB
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.
Jan 28 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.