0

I'm working on a vb.net application (I'm very new to vb.net), and have just added a DatePicker to a field on a form- PostDate (the field was previously just a text box).

The date picker appears to work visually- when the user clicks inside the form field, the date picker calendar appears, and they can select a date from the current month, or switch months to select a date from another month. Once the user has selected a date, the calendar disappears, and the newly selected date is displayed in the field in the format mm/dd/yyyy.

However, while debugging my application, when I reach the UpdateButton_Click() function that is handling the updates to fields in the form (i.e. checking to see if any of their values have changed, and updating them in the database if they have), I see that in the line I have added to check for changes to the date field that I have just added the DatePicker to, the date variable still holds the previous value (i.e. the original date, rather than the newly selected date).

The UpdateButton_Click() function is defined with:

Protected Sub UpdateButton_Click(sender As Object, e As EventArgs) Handles UpdateButton.Click
    If Page.IsPostBack Then
        ...
        Dim mypostdate As String = HttpUtility.HtmlEncode(PostDate.Text & " " & theTime.Text)
        ...
        C.updateCaseByCaseID(... mypostdate, ...)
        ...
        Next
    End If
End Sub

If I put a break point on this function, and step through it, I can see that the value of PostDate.Text is always its original value (i.e. it has a previously saved value when the page loads). Even if I change the value of the field to a new date, using the date picker, although the field shows the new date in the browser, when I step through this function, the value of PostDate.Text is always the original date value.

The call to C.updateCaseByCaseID(...) appears to be what is handling the database updates, and was originally defined in cases.vb with:

Public Function updateCaseByCaseID(ByVal Caseid As String, ...) As String
    Dim con As New SqlConnection(connectionString)
    Dim sql As String = "UPDATE tableName SET "
    sql = sql & " ReportNumber=@ReportNumber, ... "

    If status = "" Then
    Else
        sql = sql & " ,status=status "
    End If

    ...
    If PostDate = "" Then
    Else
        sql = sql & ", PostDate=@PostDate"
    End If

    ' Other similar If Else statements for the other parameters '

    ...
    Dim cmd As New SqlCommand(sql, con)
    cmd.Parameters.AddWithValue("@CID", Cid)
    ...
    cmd.Parameters.AddWithValue("@PostDate", PostDate)
    ' Other similar AddWithValue statements '
    ...

    If state = "" Then
    Else
        cmd.Parameters.AddWithValue("@State", State)
    End If

    ' Other similar If statements for the other parameters '
    ...

    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()
    Dim myaction As String = "Done"
    Return myaction

End Function

I added the postdate parameter to the sql variable by changing that line to:

sql = sql & " ReportNumber=@ReportNumber, ..., PostDate=@PostDate, ...

However, although this does now update the value of the post date field, it appears to update it to today's date, rather than the date selected by the DatePicker. This seems to be because although the PostDate field displays the newly selected date in the browser, in the UpdateButton_Click(...) function, in the line

Dim mypostDate As String = HttpUtility.HtmlEncode(PostDate.Text + " " + theTime.Text)

the variable PostDate.Text, i.e. the form field that is displaying the newly selected date in the browser, appears to have the value of today's date... I don't understand why this is happening...? Surely it should contain the value of the date selected in the DatePicker? Especially given that that is what's being displayed in the browser...

Anyone have any suggestions what I'm doing wrong with this?

Edit

As mentioned in the comments, I have updated the UpdateButton_Click() function as follows:

Protected Sub UpdateButton_Click(sender As Object, e As EventArgs) Hanles UpdateButton.Click
    If Page.IsPostBack Then
        ...
        Dim mypostdate As String = HttpUtility.HtmlEncode(PostDateTxt.Text & " " & theTime.Text)
        Dim postDateAndTimeParts = mypostdate.Split("/")
        Dim postTime = postDateAndTimeParts(2).Split("/")
        Dim postDay = postDateAndTimeParts(1)
        Dim postMonth = postDateAndTimeParts(0)
        Dim postYear = postDateAndTimeParts(2).Split(" ")(0)
        Dim postHour = postTime.Split(":")(0)
        Dim postMin = PostTime.Split(":")(1)
        DateTime PostDate = New DateTime(postYear, postMonth, postDay, postHour, postMin, 0);
        ...
        C.updateCaseByCaseID(...)
    End If
End Sub

My intention is to pass the new PostDate DateTime object that I'm reconstructing from the String into the C.updateCaseByCaseId(...) call, however on the line where I'm constructing that object, I'm currently getting a compile error that says:

Method arguments must be enclosed in parentheses

I'm not sure why this is? I can't see where I'm missing any parentheses...

Noble-Surfer
  • 3,052
  • 11
  • 73
  • 118
  • I've been debugging it a bit further, and although I thought I had just changed the type of the text box to a `DatePicker`, it seems what I have actually done is drawn a `DatePicker` over the top of the text box that was previously there... It seems I had also forgotten to update the calls to the `updateCaseByCaseID(...)` function to pass the date value to them in order to update the field in the database. – Noble-Surfer Apr 02 '19 at 12:02
  • I have now updated these function calls, but get the exception: `System.Data.SqlClient.SqlException: 'The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The statement has terminated.'. I'm not sure how I should handle this error, or rewrite the function call to ensure it's not thrown... Any suggestions? – Noble-Surfer Apr 02 '19 at 12:02
  • Thanks, I hadn't realised that `&` was what was required for concatenation, so have changed that. I've also edited my post to show the code for where I'm adding `PostDate` to the SQL. Changing the `+` to `&` appears to have fixed the issue regarding the field displaying today's date rather than the selected date- it does now show the selected date- so thanks very much! – Noble-Surfer Apr 02 '19 at 13:18
  • However, as I step through the code, when it reaches the line `cmd.ExecuteNonQuery()` in `updateCaseByCaseID()`, an exception is thrown that says: "System.Data.SqlClient.SqlException: 'The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated." As I understand, this seems to be a cast error? But I don't think I've ever declared the `PostDate` variable as an nvarchar- it should only be declared/ used as a DateTime.... Any ideas what I might be doing wrong with that? – Noble-Surfer Apr 02 '19 at 13:19
  • The answer at https://stackoverflow.com/questions/5466100/conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in-an-out-of appears to indicate that the issue is to do with the format of the date, but I'm not sure what the correct format is, or how I would address that? – Noble-Surfer Apr 02 '19 at 13:23
  • What is happening is that @PostDate is being passed as a string. The database sees *that* as an nvarchar string and tries to parse it to a date, and fails. What you need to do to add the @PostDate parameter is `cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@PostDate", .SqlDbType = SqlDbType.DateTime, .Value = x})` where `x` is the string postdate parsed as a DateTime. – Andrew Morton Apr 02 '19 at 14:08
  • ... Also, make sure to use [Option Strict On](https://stackoverflow.com/a/5076966/1115360) so that VS can tell you about variable type mismatches, and [set it on for future projects](https://stackoverflow.com/a/29985039/1115360). – Andrew Morton Apr 02 '19 at 14:08
  • Ah, brilliant, thanks. I had just got to the point where I'd seen that the `PostDate` was being passed as a String but needed to be a DateTime. I think I've now sorted that everywhere except where it's being added to the SQL command- I have update the `cmd.Parameters.Add(...)` line like you mentioned (using `.Add` rather than `.AddWithValue`, with the parameters you mentioned). – Noble-Surfer Apr 02 '19 at 15:05
  • However, when the `updateCaseByCaseId()` function is called (which is where the `cmd.Parameter.Add()` line is run), in the `UpdateButton_Click()` function, I get the error: `System.InvalidCastException: 'Conversion from string "03/29/2019 10:56" to type 'Date' is not valid, so I'm trying to construct a new `DateTime` object from the String, to pass that into the `updateCaseByCaseId()` function, rather than the String object, but I'm getting an error when trying to do that- I'll update my OP now to show what I'm doing. – Noble-Surfer Apr 02 '19 at 15:07
  • OP updated to show what I'm now doing, and the error that is now being given. – Noble-Surfer Apr 02 '19 at 15:21
  • I wouldn't concatenate two values and call HtmlEncode on the result when I need the two values separately - I guess that got left in from a previous version of the code. – Andrew Morton Apr 03 '19 at 20:02

0 Answers0