By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,668 Members | 1,552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,668 IT Pros & Developers. It's quick & easy.

Retrieving data using SQL stored procedure in C# application

P: 1
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
Share this Question
Share on Google+
4 Replies


nateraaaa
Expert 100+
P: 663
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
Expert 5K+
P: 7,872
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
Expert 100+
P: 663
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
Expert 5K+
P: 7,872
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.