Stored procedure problem

Hi everyone,

I urgently need your help with the stored procedure. I intend to read in a number of values (time and day format), format it to the uk standard and then pass on.
Through the code below I carry submission date and current date as strings and then attempt to insert into the DB in relevant fields (that are datetime format, the us style).

I am getting the following error that is very straightforward, however I cant put my head ot it. Further comlication is my relative uneasy with the us/uk date format struggle.
Please, feel free to suggest constructive solutions.

--------------------------------------------------------------------------------------------------
Server Error in '/GT' Application.
String was not recognized as a valid DateTime.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: String was not recognized as a valid DateTime.

Source Error:

Line 70: cmd.Parameters.Add(up2)
Line 71:
Line 72: Session("RequestID") = CType(cmd.ExecuteScalar, String)
Line 73: lblOrderID.Text = CStr(Session("RequestID"))
Line 74: cnn.Close()

------------------------------------------------------------------------------------------------------

thanx

blogg

CODE:

-----------------------------------------------------------------------------
Calendar1.SelectedDate = Calendar1.TodaysDate

Dim ST, SD, SDST As String
ST = Format(TimeOfDay.ToLocalTime)
SD = Format(Calendar1.TodaysDate, "dd/MM/yyyy,")
SDST = SD & Space(1) & ST
Session("ASDate") = SDST

Session("ADT") = txtDate.Text & Space(1) & txtTime.Text

---------------------------next page ---------------------------------------------------

lblADT.Text = CStr(Session("ADT"))

HiddenField1.Value = CStr(Session("ASDate"))

------------------------- following page ----------------------------------------------

Dim cnn As SqlConnection = _
New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;User Instance=True;
Integrated Security=True; Trusted_Connection = Yes;")

Dim cmd As New SqlCommand
With cmd
.Connection = cnn
.CommandText = "insNewRequest"
.CommandType = CommandType.StoredProcedure
End With
cnn.Open()

Dim up1 As New SqlParameter("@SubmissionDate", SqlDbType.DateTime)
up1.Direction = ParameterDirection.Input
up1.Value = CType(Session("ASDate"), String)
cmd.Parameters.Add(up1)

Dim up2 As New SqlParameter("@RequiredDate", SqlDbType.DateTime)
up2.Direction = ParameterDirection.Input
up2.Value = CType(Session("ADT"), String)
cmd.Parameters.Add(up2)

Session("RequestID") = CType(cmd.ExecuteScalar, String)
lblOrderID.Text = CStr(Session("RequestID"))
cnn.Close()

---------------------------------- the insNewRequest stored procedure -------------------------------------------

ALTER PROCEDURE insNewRequest

@SubmissionDate datetime,
@RequiredDate datetime,

AS
Insert INTO [Requests](SubmissionDate,RequiredDate )
VALUES (@SubmissionDate, @RequiredDate)
SELECT @@IDENTITY

Answer this question

Stored procedure problem

SQL McOLAP
Probably, this is the cause:

CType(Session("ADT"), String)

ADT has a format that cannot be converted to DateTime. Grab it's value as a string and make sure you can convert it using DateTime.ParseExact

Then, use it (the converted DateTime) to set the value of the parameters, instead of assigning the string directly.

--VV [MS]
vascov@microsoft.com
ger001
In my experience of using dates with a web based application and SQL Server, I have post them as "yyyymmdd hh:mm:ss" and have had no problems with conversion from a string to datetime.
Stored procedure problem

Answer this question