Solved

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

Posted on 2014-04-03
19
221 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 500 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Read oracle BLOB in vb.net 2 24
Intermittent OleDbConnection Error 20 54
vb.net searchandselect 12 20
SQL Server View  - Timeout Expired 5 21
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

710 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