CODEWITHSUNDEEP
c#asp.netsql-server-2008stored-procedures
Big Pimpin
Big Pimpin

Reputation: 437

Return Variable From Stored Procedure

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

Upvotes: 0

Views: 872

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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.

Upvotes: 3

Rajeev Kumar
Rajeev Kumar

Reputation: 4963

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
}

Upvotes: 1

Related Questions