Return Variable From Stored Procedure

Question

I need to get an employees start date. What I am trying to do is when a check box is checked make a call to the database get the date and return it into my C# code. I think I have everything set-up close to perfect, but my call procedure returns an error. Can someone assist me with setting up syntax so that when a check box is checked it will make a call to the database, run a stored procedure, and return the result (as a datetime) from the stored procedure back into my C# syntax?

EDIT --- The error being thrown is: cannot implicitly convert type 'System.DateTime' to 'string'

//Calling Procedure
this.txtStartDate.Text = getHireDate(Constants.Database, employeename);

//Actual Procedure
private static string getHireDate(string Database, string employeename)
{
  SqlConnection connection = new SqlConnection(Database);
  SqlCommand command = new SqlCommand("_uspGetHireDate", connection);
  command.CommandType = CommandType.StoredProcedure;
  SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
  returnValue.Direction = ParameterDirection.ReturnValue;
  command.Parameters.Add(returnValue);
  connection.Open();
  command.ExecuteNonQuery();
connection.Close();

  //This line throws an error of can not implicitly convert 'System.DateTime' to 'string'
  return Convert.ToDateTime(returnValue.Value);
}


//Stored Procedure Being Called
alter procedure [dbo].[_uspGetHireDate]
(
  @employeename varchar(100)
)

as

  declare @StartDate datetime
  set NOCOUNT ON

  Set @StartDate = (SELECT CONVERT(VARCHAR(10), HireDate, 101) 
                   FROM tbl_employeeinformation
                   where hiredate is not null
                   AND active = 1
                   AND employeename = @employeename

  return @StartDate

Why are you taking HireDate (which I assume is a datetime column) and converting it to varchar(10) before setting it into a datetime variable?? — freefaller
– freefaller, Commented May 19, 2015 at 12:10
You have defined your parameter to be an Int SqlParameter("returnVal", SqlDbType.Int). But your SP is returning string SELECT CONVERT(VARCHAR(10), HireDate, 101) — wonderbell
– wonderbell, Commented May 19, 2015 at 12:10
@aSharma - actually, it's returning a datetime but your observation of the SqlParameter type is spot on — freefaller
– freefaller, Commented May 19, 2015 at 12:15

Giorgi Nakeuri · Accepted Answer · 2015-05-19 15:01:43Z

3

Your solution is totally messy. Why are you converting DATE to VARCHAR, then return it as an INT, then converting it as a DateTime and return as STRING???

First of all change your stored procedure to return value standard way. Return DATE, not INT with return:

alter procedure [dbo].[_uspGetHireDate]
@employeename varchar(100)
as
  set NOCOUNT ON

  SELECT HireDate
  FROM tbl_employeeinformation
  where hiredate is not null
        AND active = 1
        AND employeename = @employeename

Then change your method to return DateTime:

private static DateTime getHireDate(string Database, string employeename)
{
  SqlConnection connection = new SqlConnection(Database);
  SqlCommand command = new SqlCommand("_uspGetHireDate", connection);
  command.CommandType = CommandType.StoredProcedure;

  connection.Open();
  var result = Convert.ToDateTime(command.ExecuteScalar());
  connection.Close();

  return result;
}

Thanks to @petelids for pointing this out. Change your presentation layer to:

this.txtStartDate.Text = getHireDate(Constants.Database, employeename).ToString("yyyy-MM-dd");

or whatever format that is appropriate. Visualizing data is a work of a presentation layer and not of a business layer. Here you are storing connection string in presentation layer and this is a bit of strange.

Also put your connections, commands etc in using blocks and use try-catch blocks. Also I have noticed that you are not passing @employeename parameter to your stored procedure.

edited May 19, 2015 at 15:01

answered May 19, 2015 at 12:35

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

petelids Over a year ago

One small addition, if you change the return type of getHireDate (which I agree you should (+1)) will you need to change the assignment on the textbox - e.g. this.txtStartDate.Text = getHireDate(Constants.Database, employeename).ToString("MM/dd/yyyy");

Giorgi Nakeuri Over a year ago

@petelids, of course it is such obvious, business should provide data and it is up to presentation how to show this data.

petelids Over a year ago

I completely agree, I just thought with the confusion over types in the question it was worth pointing out the conversion error will move to the textbox assignment.

Rajeev Kumar · Accepted Answer · 2015-05-19 12:10:40Z

1

You are returning DateTime instead of string as intended in function return type

private static string getHireDate(string Database, string employeename)

{

----

 return Convert.ToDateTime(returnValue.Value); // Here you are returning datetime but your return type should be string as your function return type is string
}

answered May 19, 2015 at 12:10

Rajeev Kumar

4,9739 gold badges53 silver badges91 bronze badges

Collectives™ on Stack Overflow

Return Variable From Stored Procedure

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related