Solved

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

Posted on 2014-04-03
19
213 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
  • 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now