Return Value from Stored Procedure
-
Hello, I have a asp.net website and a C# web page and I am calling a stored procedure to return the email address of the user that is logged in. I have debugged my code and am presented with the following error message. Cannot implicitly convert type 'System.Data.SqlClient.SqlParameter' to 'string' Any assistance you can provide would be much appreciated. My code for the web page class and my code for the stored procedure are below. Thank you, Allison EmailTo Class
public class EmailTo { public string getEmailTo() { // Command - specify as StoredProcedure using (System.Data.SqlClient.SqlConnection myConnection = new System.Data.SqlClient.SqlConnection(ConfigurationManager. ConnectionStrings["ConnectionString2"].ConnectionString)) { System.Data.SqlClient.SqlCommand myCommand2 = new System.Data.SqlClient.SqlCommand(); myCommand2.CommandText = "usp_get_email_to"; myCommand2.CommandType = CommandType.StoredProcedure; myCommand2.Connection = myConnection; // Return value as parameter SqlParameter returnEmail = new SqlParameter("Email", SqlDbType.NVarChar); returnEmail.Direction = ParameterDirection.ReturnValue; myCommand2.Parameters.Add(returnEmail); // Execute the stored procedure myConnection.Open(); myCommand2.ExecuteNonQuery(); myConnection.Close(); return returnEmail; } } }Stored ProcedureCREATE PROCEDURE dbo.usp_get_email_to @Email nvarchar(256), @UserId uniqueidentifier AS SET @Email = (SELECT Email FROM aspnet_Membership WHERE UserId=@UserId) RETURN @Email GO -
Hello, I have a asp.net website and a C# web page and I am calling a stored procedure to return the email address of the user that is logged in. I have debugged my code and am presented with the following error message. Cannot implicitly convert type 'System.Data.SqlClient.SqlParameter' to 'string' Any assistance you can provide would be much appreciated. My code for the web page class and my code for the stored procedure are below. Thank you, Allison EmailTo Class
public class EmailTo { public string getEmailTo() { // Command - specify as StoredProcedure using (System.Data.SqlClient.SqlConnection myConnection = new System.Data.SqlClient.SqlConnection(ConfigurationManager. ConnectionStrings["ConnectionString2"].ConnectionString)) { System.Data.SqlClient.SqlCommand myCommand2 = new System.Data.SqlClient.SqlCommand(); myCommand2.CommandText = "usp_get_email_to"; myCommand2.CommandType = CommandType.StoredProcedure; myCommand2.Connection = myConnection; // Return value as parameter SqlParameter returnEmail = new SqlParameter("Email", SqlDbType.NVarChar); returnEmail.Direction = ParameterDirection.ReturnValue; myCommand2.Parameters.Add(returnEmail); // Execute the stored procedure myConnection.Open(); myCommand2.ExecuteNonQuery(); myConnection.Close(); return returnEmail; } } }Stored ProcedureCREATE PROCEDURE dbo.usp_get_email_to @Email nvarchar(256), @UserId uniqueidentifier AS SET @Email = (SELECT Email FROM aspnet_Membership WHERE UserId=@UserId) RETURN @Email GOahayw01 wrote:
SqlParameter returnEmail = new SqlParameter("Email", SqlDbType.NVarChar); returnEmail.Direction = ParameterDirection.ReturnValue; myCommand2.Parameters.Add(returnEmail);
1. parameter should start with '@'
SqlParameter returnEmail = new SqlParameter("@Email", SqlDbType.NVarChar);
2. parameter value is missing - add this below line before adding the parameter to myCommand2
returnEmail.Value = "abc@codeproject.com"
-
ahayw01 wrote:
SqlParameter returnEmail = new SqlParameter("Email", SqlDbType.NVarChar); returnEmail.Direction = ParameterDirection.ReturnValue; myCommand2.Parameters.Add(returnEmail);
1. parameter should start with '@'
SqlParameter returnEmail = new SqlParameter("@Email", SqlDbType.NVarChar);
2. parameter value is missing - add this below line before adding the parameter to myCommand2
returnEmail.Value = "abc@codeproject.com"
Sandeep, Thank you so much for your quick response. I am a little confused about step 2. I think that I may not have been clear in my initial post. The returnEmail.Value should be pulling from the value that is stored in the sql table. However, I had thought that the existing code will account for that
returnEmail.Direction = ParameterDirection.ReturnValue; myCommand2.Parameters.Add(returnEmail);Any additional suggestions would be appreciated. My apologies for any confusion. Allison -
Sandeep, Thank you so much for your quick response. I am a little confused about step 2. I think that I may not have been clear in my initial post. The returnEmail.Value should be pulling from the value that is stored in the sql table. However, I had thought that the existing code will account for that
returnEmail.Direction = ParameterDirection.ReturnValue; myCommand2.Parameters.Add(returnEmail);Any additional suggestions would be appreciated. My apologies for any confusion. AllisonDid you tried adding '@' before the parameter Email?
-
Did you tried adding '@' before the parameter Email?
Furtherm, Where from your @UserID parameter is supplied? Shouldn't that too be added in the Command Parameters?
-
Did you tried adding '@' before the parameter Email?
-
Hello, Thanks yes, I did apply step 1. And was still receiving the same error message. However, just tried and tested the following and now it works
return returnEmail.Value as String;Thanks again for your help, Allison:thumbsup: