Iron Speed Technical Forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
otto

Registered:
Posts: 10
Reply with quote  #1 
Hi

I've run the CCW for a stored procedure into a grid. However I'm getting a datetime/char conversion when I execute.

Here are my parameters in SQL Server
ALTER PROCEDURE [dbo].[ap_list_available_carers]
(
@client_id  int,
@task_id  int,
@the_date  datetime,
@start_time  varchar, 
@end_time  varchar,
@shift_24  bit = null,
@shift_overnight  bit = null
)
AS


Here are my parameters in Ironspeed
Dim dteBookingSelection As String = Me.BookingDate.SelectedDate.Day & "-" & MonthName(Me.BookingDate.SelectedDate.Month, True) & "-" & Me.BookingDate.SelectedDate.Year

            firstParameter = New BaseClasses.Data.StoredProcedureParameter("@client_id", CLng(Me.lstClientID.SelectedValue), System.Data.SqlDbType.Int, System.Data.ParameterDirection.Input)
            secondParameter = New BaseClasses.Data.StoredProcedureParameter("@task_id", CLng(Me.lstTaskID.SelectedValue), System.Data.SqlDbType.Int, System.Data.ParameterDirection.Input)
            thirdParameter = New BaseClasses.Data.StoredProcedureParameter("@the_date", dteBookingSelection, System.Data.SqlDbType.Date, System.Data.ParameterDirection.Input)
            fourthParameter = New BaseClasses.Data.StoredProcedureParameter("@start_time", Me.txtTimeStart.Text, System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Input)
            fifthParameter = New BaseClasses.Data.StoredProcedureParameter("@end_time", Me.txtTimeEnd.Text, System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Input)

            ' Step 2: Add the configured parameters to an array list.
            Dim parameterList(4) As BaseClasses.Data.StoredProcedureParameter
            parameterList(0) = firstParameter
            parameterList(1) = secondParameter
            parameterList(2) = thirdParameter
            parameterList(3) = fourthParameter
            parameterList(4) = fifthParameter

            Dim myStoredProcedure As BaseClasses.Data.StoredProcedure = Nothing

            'Step 3: Connect to the stored procedure 
            myStoredProcedure = New BaseClasses.Data.StoredProcedure("DatabaseCarerData1", "ap_list_available_carers", parameterList)


            ' Step 4: Run the stored procedure.    
            ' RunQuery() will return true if stored procedure successfully executed. Otherwise, it will return false.
            ' Use RunQuery or RunNonQuery.  RunQuery is used when a set of records is being returned.  RunNonQuery is used when one or more values are returned through output parameters.

' FAILS HERE
            If (myStoredProcedure.RunQuery()) Then


This is what I get when it runs
Quote:
ironspeed +        Thrown: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated. The statement has been terminated. The statement has been terminated." (System.Data.SqlClient.
SqlException)    Exception Message = "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.\r\nThe conversion of a varchar data type to a datetime data type resulted in an out-of-range value.\r\nThe conversion of a varchar data type to a datetime data type resulted in an out-of-range value.\r\nThe statement has been terminated.\r\nThe statement has been terminated.\r\nThe statement has been terminated.", Exception Type = "System.Data.SqlClient.SqlException" 




JimiJ

Avatar / Picture

MVP Developer
Registered:
Posts: 1,966
Reply with quote  #2 
How about using time data type?

@start_time  time,
System.Data.SqlDbType.Time


Hope that helps,
Jimi J

__________________

  Jaime Jegonia
[jts_logo]

Iron Speed MVP Developer
 

". . . and whoever sows generously will also reap generously" 2 Cor 9:6

otto

Registered:
Posts: 10
Reply with quote  #3 
No luck with that option either
JamesWhistler

Avatar / Picture

Registered:
Posts: 214
Reply with quote  #4 
Hi there,

It looks to me like the problem might be with the conversion you're doing with the date parameter before the call to the stored procedure rather than anything wrong with your table defintion.

Dim dteBookingSelection As String = Me.BookingDate.SelectedDate.Day & "-" & MonthName(Me.BookingDate.SelectedDate.Month, True) & "-" & Me.BookingDate.SelectedDate.Year

It looks like this would result in a date of dd-mmm-yy format, whereas SQL is probably expecting yyyy-mm-dd.

I can't be sure without investigating it further but let me know if that helps at all.


__________________

James Whistler
+44 (0) 870 067 7601
j.whistler@alkali-blue.com
http://www.alkali-blue.com

otto

Registered:
Posts: 10
Reply with quote  #5 
Above did not work either.

I tried dd-mmm-yyyy, yyyy-mmm-dd, yyyy-mm-dd - all still producing three varchar to datetime errors on executing. I thought I'd be getting one error if there was only 1 datetime parameter.
mhweiss

Avatar / Picture

MVP Consultant
Registered:
Posts: 569
Reply with quote  #6 
Hello
In your date and time parameters, can you hard code a date and/or time and test it to see if it works correctly? Conversely, you could pass them as type of varchar and do a conversion inside your stored procedure to date/time.
Best Regards,
Michael

__________________
michael@occasiosystems.com
http://www.occasiosystems.com
otto

Registered:
Posts: 10
Reply with quote  #7 
I didn't write the stored procedure so I'm a little hesitant to start changing it.

The code I was provided to run it actually sets the date format, if I run it without the date format line it doesn't work in sql server. What date format should I be using within sql server?

Here's the code
USE [CarerData]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET DATEFORMAT dmy;

GO

SET DATEFIRST 1;

GO

 

DECLARE     @return_value int

 

EXEC  @return_value = [dbo].[ap_list_available_carers]

            @client_id = 877,

            @task_id = 34,

            @the_date = N'20/5/2013',

            @start_time = N'6:30:00',

            @end_time = N'9:00:00'

 

SELECT      'Return Value' = @return_value

 

GO
Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.

Download Iron Speed Designer

Terms of Service Privacy Statement