Syntax error converting datetime from character string.

Hi All,

Im in the process of building a call loggin system and its finally been put on the server, but i keep getting the above error "Title" the first stage it goes to be authorised and when the authoriser auths it...it updates the table with the date they authorised it. which is fine, the next stage is the Business Analyst where they check all the content of the task and then approve it but for some reason unknown to me it keeps falling over on the .dateAuthorised

My Sql Column to hold the date authorised is a Datetime datatype and im converting a string to a data time within the procedure this is my Code behind page

Code:

---

  Dim StatusNew As Integer = 53
                    Dim CurrentStage As Integer = 51
                    ITUpdateTaskTable.Add("@spWorkID", Replace(TskNumLab.Text, "LC", ""))
                    ITUpdateTaskTable.Add("@spTitle", txtTitleContent.Text)
                    ITUpdateTaskTable.Add("@spDescription", txtDescription.Text)
                    ITUpdateTaskTable.Add("@spJustification", txtJustification.Text)
                    ITUpdateTaskTable.Add("@spRequiredBy", txtDateRequired.Text)
                    ITUpdateTaskTable.Add("@spCurrentStage", CurrentStage)
                    ITUpdateTaskTable.Add("@spDateAuthorised", Format(.DateAuthorised, "dd/MM/yyyy"))
                    ITUpdateTaskTable.Add("@spTeamName", "BA Approval")
                    ITUpdateTaskTable.Add("@spComment", txtITValComments.Text)
                    ITUpdateTaskTable.Add("@spITLevelEstimate", txtITValLevelEstimate.Text)
                    ITUpdateTaskTable.Add("@spCategoryID", cmbCategory.SelectedValue)
                    ITUpdateTaskTable.Add("@spBusinessAreaID", cmbBusinessArea.SelectedValue)
                    ITUpdateTaskTable.Add("@spPriorityID", cmbPriority.SelectedValue)
                    ITUpdateTaskTable.Add("@spTeamID", cmbBAAllocatedTeam.SelectedValue)
                    ITUpdateTaskTable.Add("@spAssignUserID", BAAssingToAUser.Value)
                    ITUpdateTaskTable.Add("@spDeptPriority", .DeptPriority)
                    ITUpdateTaskTable.Add("@spRaisedFor", .RaisedFor)
                    ITUpdateTaskTable.Add("@spUserID", UserID)
                    ITUpdateTaskTable.Add("@spOldTeam", OldTeamID)
                    ITUpdateTaskTable.Add("@spUserRef", .UserReference)
                    ITUpdateTaskTable.Add("@spAssignUserType", "User")
                    ChangeClass.UpdateRecord(ITUpdateTaskTable, StatusNew)
                    EmailSend.SendAssignmentUser("TaskTracker", Replace(TskNumLab.Text, "LC", ""), txtTitleContent.Text, BAAssingToAUser.Value, )
                    EmailSend.Send()
                    Response.Redirect("MyWorkRequest.aspx?Update=" & message)

---

The field in error is ITUpdateTaskTable.Add("@spDateAuthorised", Format(.DateAuthorised, "dd/MM/yyyy"))

the formats i have tried are as follows .DateAuthorised, DateAuthorised.tostring and one you can see above here is my proc

Code:

---

UPDATE wrp_Work
  SET chrWorkDesc = @spDescription
        , chrWorkTitle = @spTitle
        , intStatus = @spStatusID
        , intAssignTo = @spAssignUserID
        , chrAssignToType = @spAssignUserType
        , intModBy = @spUserID
        , datModified = @CurrentDate
        , chrJustification = @spJustification
        , chrUserRef = @spUserRef
        , chrEstimateHigh = @spEstimateHigh
        , chrEstimateLow = @spEstimateLow
        , datAuthorised = convert(datetime,@spDateAuthorised, 103)
        , chrActualWork = @spActualWork
        , intCategoryID = @spCategoryID
        , intPriority = @spPriorityID
        , datRequired = convert(datetime,@spRequiredBy, 103)
            , intRaisedFor = @spRaisedFor
        , intBusinessArea = @spBusinessAreaID
        , intSignedOffByID = @spintSignedOffByID
      , intDeptPriority = @spDeptPriority
        , intITValLvEst = @spITLevelEstimate
        , intDevelopmentLvEst = @spDevelopmentLevelEstimate
                , intDeveloperLvEst = @spDeveloperLevelEstimate
        , chrAldonTaskNum = @spAldonTaskNumber
        , intBALvEst = @spBATestEstimate
        , intBAUserTestEst = @spBAUserTestEstimate
        , intReleaseNum = @spReleaseNumber
                , intTeamID = @spTeamID
WHERE intWorkID = @spWorkID

---

Im banging my head at this so please any advice would be highly appreciated im not doing anything with the field it self just passing it back in to SQL and yes this is very irriatating but i didnt design the date base nor do i have time tto re-design it so if i field is missing it wont load the Task

Make sure that the date string in the SQL is enclosed in single-quotes: I.E.

datRequired = '11/18/2011'. I'm not sure what the "convert" function above returns but it probably does not include the enclosed quotation marks.

-Max

If you are just starting then you might consider storing dates as dates and not strings.

Also, is this application going to be used in different timezones? If so what time will the end-user see?

Just some things to think about.

Hi Max Peck,

Thanks for the info, i have checked this and all is correct.....All i wont to do is pass the same value i pulled back from the load back in to table, other wise if i dont the value will be over written with a null value...again i didnt design this data base and its very difficult working with something that has been badly designed!

When the authoriser his authorises this is how i declare the date

Code:

---

Dim DateToday As String = Format(Today.Date, "dd/MM/yyyy")

---

and from there its referred to as .DateAuthorised

:(

hi dbasnett, no it will only be used within the company i just cant see why there would be a problem retrieving a value not changing anything and passing it back in......

but you're not passing back what you got in the first place... you're formatting and messing with it ....

FROM the DATABASE side... what is the field type? Is it a date/datetime or a string? Dates should never be strings... it just causes all kinds of problems... and if the field is a date/datetime field... stop storing it as a string in your code, and stop formatting it when saving it to the database.

-tg

Hi techgnome, the field type in SQL is Date Time, but im only learning this so from what i have been told by senior developers is use a string and then convert it in SQL iv just tried changing the .Net code so instead of looking like

Code:

---

Dim DateToday As String = Format(Today.Date, "dd/MM/yyyy")

---

it now looks like

Code:

---

Dim DateToday As date = Today()

---

i also changed the proc as it was expecting a string iv now changed that to a datetime data type, as well as the Get Set property that again was a string iv changed it to a date, but yet i still get the same erorr. could this be due to a server issue? again im taking a stab in the dark here iv googled and googled and found many solutions but yet none come with a fix......and some other articles i have read are mentioning the server..........

That error is obviously coming from the database so you must still be passing a string to the database and expecting it to convert that to a date. There's apparently still something wrong with your code but we don't know what your code currently looks like, so we can only conjecture.

Hi jmcilhinney, please see below the recent vb.net snippet

As mentioned above please see the declaration of the date field.

Code:

---

Dim DateToday As date = Today()

---

The code that populates the hashtable with parameters and values

Code:

---

  ITUpdateTaskTable.Add("@spWorkID", Replace(TskNumLab.Text, "LC", ""))
                    ITUpdateTaskTable.Add("@spTitle", txtTitleContent.Text)
                    ITUpdateTaskTable.Add("@spDescription", txtDescription.Text)
                    ITUpdateTaskTable.Add("@spJustification", txtJustification.Text)
                    ITUpdateTaskTable.Add("@spRequiredBy", txtDateRequired.Text)
                    ITUpdateTaskTable.Add("@spCurrentStage", CurrentStage)

                    ITUpdateTaskTable.Add("@spDateAuthorised", .DateAuthorised)

                    ITUpdateTaskTable.Add("@spTeamName", "BA Approval")
                    ITUpdateTaskTable.Add("@spComment", txtITValComments.Text)
                    ITUpdateTaskTable.Add("@spITLevelEstimate", txtITValLevelEstimate.Text)
                    ITUpdateTaskTable.Add("@spCategoryID", cmbCategory.SelectedValue)
                    ITUpdateTaskTable.Add("@spBusinessAreaID", cmbBusinessArea.SelectedValue)
                    ITUpdateTaskTable.Add("@spPriorityID", cmbPriority.SelectedValue)
                    ITUpdateTaskTable.Add("@spTeamID", cmbBAAllocatedTeam.SelectedValue)
                    ITUpdateTaskTable.Add("@spAssignUserID", BAAssingToAUser.Value)
                    ITUpdateTaskTable.Add("@spDeptPriority", .DeptPriority)
                    ITUpdateTaskTable.Add("@spRaisedFor", .RaisedFor)
                    ITUpdateTaskTable.Add("@spUserID", UserID)
                    ITUpdateTaskTable.Add("@spOldTeam", OldTeamID)
                    ITUpdateTaskTable.Add("@spUserRef", .UserReference)
                    ITUpdateTaskTable.Add("@spAssignUserType", "User")
                    ChangeClass.UpdateRecord(ITUpdateTaskTable, StatusNew)
                    EmailSend.SendAssignmentUser("TaskTracker", Replace(TskNumLab.Text, "LC", ""), txtTitleContent.Text, BAAssingToAUser.Value, )
                    EmailSend.Send()

---

Heres the Get Set Property

Code:

---

'Date Authorised
    Public Property DateAuthorised() As date
        Get
            DateAuthorised = DatAuthorised
        End Get
        Set(ByVal value As date)
            DatAuthorised = value
        End Set
    End Property

---

Heres the stored proc

Code:

---

UPDATE wrp_Work
  SET chrWorkDesc = @spDescription
        , chrWorkTitle = @spTitle
        , intStatus = @spStatusID
        , intAssignTo = @spAssignUserID
        , chrAssignToType = @spAssignUserType
        , intModBy = @spUserID
        , datModified = @CurrentDate
        , chrJustification = @spJustification
        , chrUserRef = @spUserRef
        , chrEstimateHigh = @spEstimateHigh
        , chrEstimateLow = @spEstimateLow
        , datAuthorised =  @spDateAuthorised
        , chrActualWork = @spActualWork
        , intCategoryID = @spCategoryID
        , intPriority = @spPriorityID
        , datRequired = convert(datetime,@spRequiredBy, 103)
            , intRaisedFor = @spRaisedFor
        , intBusinessArea = @spBusinessAreaID
        , intSignedOffByID = @spintSignedOffByID
        , intDeptPriority = @spDeptPriority
        , intITValLvEst = @spITLevelEstimate
        , intDevelopmentLvEst = @spDevelopmentLevelEstimate
                , intDeveloperLvEst = @spDeveloperLevelEstimate
        , chrAldonTaskNum = @spAldonTaskNumber
        , intBALvEst = @spBATestEstimate
        , intBAUserTestEst = @spBAUserTestEstimate
        , intReleaseNum = @spReleaseNumber
                , intTeamID = @spTeamID
WHERE intWorkID = @spWorkID

---

and the dateAuthorised parameter is declared as

Code:

---

@spDateAuthorised datetime = NULL,

---

and the SQL Column is a datatype of DateTime

Iv also gone through my .net code and changed it from Date to DateTime just to see if that would make a difference but yet i still get the above error

What is 'ITUpdateTaskTable' and what does 'ChangeClass.UpdateRecord(ITUpdateTaskTable, StatusNew)' do? Is it using ADO.NET parameters or is it using string concatenation?

Dim ITUpdateTaskTable As Hashtable = New Hashtable
'ITUpdateTaskTable' is the name of a hashtable

'ChangeClass.UpdateRecord(ITUpdateTaskTable, StatusNew)
it passed the hashtable "ITUpdateTaskTable" and the new status in to a update method which passes the parameters that are inside the hashtable

heres the UpdateRecord function

Code:

---

  Public Function UpdateRecord(ByVal HtTaskData As Hashtable, ByVal NewStatus As Integer) As Boolean
      Dim SpParameter As DictionaryEntry
        Dim cmd As New SqlCommand

        cmd.CommandText = "wrp_sp_workrequest"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter("@spProcess", "Update"))
        cmd.Parameters.Add(New SqlParameter("@spStatusID", NewStatus))

        For Each SpParameter In HtTaskData
            cmd.Parameters.Add(New SqlParameter(SpParameter.Key, SpParameter.Value.ToString))
        Next
        Dim m_Connection As New SQLAccess
        Try
            If m_Connection.EstablishConnection = True Then
                cmd.Connection = m_Connection.SQLSVRConnection
                cmd.ExecuteNonQuery()
                Return True
     
            Else
        Return False
            End If
        Catch ex As Exception
            Return ex.ToString
        End Try
    End Function

---

Iv just seen the issue code eyes.................

cmd.Parameters.Add(New SqlParameter(SpParameter.Key, SpParameter.Value.ToString))

its got .tostring on the end

DOH!!!!!!!!!!!

Iv tried modding the line of code to this

Code:

---

  For Each SpParameter In HtTaskData
            cmd.Parameters.Add(New SqlParameter(SpParameter.Key, SpParameter.Value))
        Next

---

but i get "overload resolution failed because no accessible 'New' can be called without narrowing the conversion"

??

As i have mentioned before the database was created by someone which im only guessing and from what i have been told didnt know what he was doing, would there be any harm me changing the DateAuthorised column to a nvarchar which at the moment is a datetime as i believe 6 months down the line we will be re-designing the data base anyway....or could i condition the Update Function to look for DateAuthorised? Thanks in advance jmcilhinney