Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Return Date value not returning correctly on SQL stored procedure in VS

Posted on 2014-04-03
19
Medium Priority
?
223 Views
Last Modified: 2014-04-07
Hi,
I am executing a stored procedure in my Windows application and then putting the return value of the procedure into a label. The return value is a date.  For some reason instead of returning the date it is returning a time. (value should be 2014-04-02, value shows as 12:00am.

Not sure what I'm doing wrong in my code. The return value shows correctly when I run it in SQL.

Code that is relevant below:

 Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("DARTS.My.MySettings.ACS_TARSConnectionString").ConnectionString)
        Dim cmd As SqlCommand = conn.CreateCommand
        Dim procedure As String = "AutoCompleteLowAmtClaim"
        Dim maxdate As Date
        Dim maxdateparameter As New SqlParameter("@maxrundate", SqlDbType.Date)

        'Try running connecting to database and running stored procedure
        Try
            conn.Open()

            'Call stored procedure
            'Command text is set to stored procedure
            cmd.CommandText = "ExecProcedureLog_GetMaxDate"
            cmd.CommandType = CommandType.StoredProcedure

            'Add Parameters
            cmd.Parameters.Add(New SqlParameter("@storedprocedure", procedure))

            cmd.Parameters.Add("@maxrundate", SqlDbType.Date)
            cmd.Parameters("@maxrundate").Direction = ParameterDirection.Output

            cmd.ExecuteNonQuery()

            'Return Last Run Date for procedure
            Try
                maxdate = CDate((maxdateparameter.Value))
                lblrundate.Text = "Last Run Date:" & CStr(maxdate)


            Catch ex As Exception

            End Try
0
Comment
Question by:imstac73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 7
19 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39975457
Can you show us your stored procedure code?

Also, following

maxdate = CDate((maxdateparameter.Value))
                lblrundate.Text = "Last Run Date:" & CStr(maxdate)

can be

lblrundate.Text = "Last Run Date: " & maxdateparameter.Value.Tostring
0
 

Author Comment

by:imstac73
ID: 39975611
ALTER PROCEDURE [dbo].[ExecProcedureLog_GetMaxDate]
      -- Add the parameters for the stored procedure here
      @storedprocedure as nvarchar(100),
      @maxrundate as date OUTPUT

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT @maxrundate = Max(executed_date) from dbo.Exec_StoredProcedure_Log
      Where SQLProcedure = @storedprocedure
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39975684
Did you try my suggestion on code change?
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:imstac73
ID: 39975915
Yes when I changed the code to your selection nothing came up.  The text was blank.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39977752
Just to make sure, executed_date contains the full date and time?
0
 

Author Comment

by:imstac73
ID: 39977942
No executed_date is just date.  Do I need to make it datetime?
0
 

Author Comment

by:imstac73
ID: 39978078
I tried changing to DateTime and am getting the same result.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39978170
I meant the data within the table. Is it currently storing dates as well?

Does it work as expected if you run the stored procedure in SQL Management Studio?
0
 

Author Comment

by:imstac73
ID: 39978880
I changed the datatype in the table from date to datetime.  The stored procedure returns the value as expected when I run in Management Studio.  When I run it in the Windows application it returns 00/01/001.
0
 

Author Comment

by:imstac73
ID: 39978910
Codecruiser,
I also changed my code to your suggestion:
lblrundate.Text = "Last Run Date: " & maxdateparameter.Value.Tostring

This is not return anything.

I then changed my code to this:
 maxdate = CDate((maxdateparameter.Value))
                lblrundate.Text = CStr(maxdate)

And it returned  12:00:00 AM

In SQL the procedure returns 2014-04-04 10:56:42.190

Not sure where it is getting the former from.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 1500 total points
ID: 39979411
Try changing your procedure to following


ALTER PROCEDURE [dbo].[ExecProcedureLog_GetMaxDate]
      -- Add the parameters for the stored procedure here
      @storedprocedure as nvarchar(100),
      @maxrundate as Varchar(50) OUTPUT

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT @maxrundate = Convert(Varchar, Max(executed_date), 103) from dbo.Exec_StoredProcedure_Log
      Where SQLProcedure = @storedprocedure

Open in new window

0
 

Author Comment

by:imstac73
ID: 39983007
Do I change the data type in my vb code as well?
0
 

Author Comment

by:imstac73
ID: 39983147
Same result: 12:00:00 am
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39983272
Can you show a screenshot of the data in your table using SQL Management Studio?
0
 

Author Comment

by:imstac73
ID: 39983407
Table data
0
 

Author Comment

by:imstac73
ID: 39983420
Also, when I change my stored procedure to your suggestion (datetime type to varchar) I get the attached results.  The month and the day are switched in the result.  The result should be 4/7/2014.
2014-04-07-10-29-54.jpg
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39983618
Change the query to


SELECT @maxrundate = Convert(Varchar, Max(executed_date), 101) from dbo.Exec_StoredProcedure_Log
      Where SQLProcedure = @storedprocedure
0
 

Author Comment

by:imstac73
ID: 39983867
Okay, that fixed the problem with the date display.  Thanks.  Still don't understand why it's not showing correctly in the Windows app.  I've done tons of searching on the web and still can't find anything that helps.
0
 

Author Comment

by:imstac73
ID: 39984495
Okay, so I decided to just do the SQL query in my code instead of using a stored procedure for it and it worked.  It's not my ideal fix because if I need to make a change I have to make it in 3 places in my windows app but it's working now.

I appreciate all your help on this.  I will give you the points since you had several suggestions for me to try.
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question