Returning date from Stored procedure in ASP.Net/VB.Net

Question

Hi,

I want to execute a method on VB.Net to return a date which is in the stored procedure. I tried using ExecuteScalar but it doesnt work it retruns error

'Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query'

Any help would be much appreciated please?

thank you

below is the code

  Public Function GetHolidaydate(ByVal struserID as String) As DateTime

        Dim objArgs1 As New clsSQLStoredProcedureParams

        objArgs1.Add("@userID", Me.Tag)
        objArgs1.Add("@Date", 0, 0, ParameterDirection.Output)

        Return (CDate(ExecuteScalar(clsLibrary.MyStoredProcedure.GetHolidayDate, objArgs1)))

End Function

Answer 1

I think that your problem is here:

objArgs1.Add("@Date", 0, 0, ParameterDirection.Output)

You are adding 0's where they should be typeOf DateTime.

EDIT

Public Function GetHolidayDate(ByVal struserID as String) AS DateTime
    Dim con As New SqlConnection(yourSQLConnectionString)
    Dim cmd As New SqlCommand
    Dim date As DateTime    

    con.Open()
    cmd.Connection = con
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "yourStoredProcedureName"
    cmd.Parameters.Add("@userID", Me.Tag)
    cmd.Parameters("@Date").Direction = ParameterDirection.Output
    cmd.ExecuteScalar()
    date = cmd.Parameters("@Date").Value
    con.Close()

    Return date
End Function

Answer 2

Looks like the error you are getting is a SQL error not a VB.Net error. Are you trying to convert a datetime to an int somewhere? You could you try running the ExecuteNonQuery() method to see if you get the same error.

You could also run SQLProfiler and see exactly what SQL is being run. You could then try running this SQL is SQL Server Management Studio

Answer 3

You add two parameters - but you said it should return the later one ?

So it must be

date DateTime = cmd.ExecuteScalar()
REM date = cmd.Parameters("@Date").Value
con.Close()