Click here to Skip to main content
15,169,285 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I set the start and end dates of the month on two labels in a project. I want to use it as a parameter.
But I'm giving the wrong message the way I'm doing. I would be benefited if an experienced person could solve my problem.
Thanks in advance.


What I have tried:

  Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles DateTimePicker1.ValueChanged
          Dim d As DateTime = DateTimePicker1.Value
        Dim nd As New Date(d.Year, d.Month, 1)
        Label1.Text = nd.ToString("yyyy-MM-dd")
        Label2.Text = nd.AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd")
        
    End Sub



  Private Sub Button1_Click_2(sender As Object, e As EventArgs) Handles Button1.Click
   Try
            con.Open()
            Dim cmd As New SqlCommand("select * from [dbo].[tbl_sales] 
where date between @d1 and @d2", con)

            Dim da As New SqlDataAdapter(cmd)
            cmd.Parameters.Add("@d1", SqlDbType.Date).Value = Label1.Text 
            cmd.Parameters.Add("@d2", SqlDbType.Date).Value = Label2.Text 
            Dim dt As New DataTable
            dt.Clear()
            da.Fill(dt)
            DataGridView1.DataSource = dt
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If (con.State = ConnectionState.Open) Then
                con.Close()
            End If
        End Try

    End Sub
Posted
Updated 25-Aug-21 7:48am
Comments
Richard MacCutchan 25-Aug-21 12:00pm
   
Do not use text strings to store dates in databases, use proper DateTime types.

1 solution

If I'm looking your code correctly the columns in the database are defined as date, at least you define the parameters as dates, that's good.

However, you cannot rely on data type conversion to happen implicitly when you set the value for the parameter. At the moment you set a string value in format yyyy-MM-dd and rely that someone else converts it to a proper date. That could happen, depending on different settings (in several places) but the chances are that it fails.

So instead you should convert the string in your UI to a date before you set it as a value of a parameter.

If you know that the format for the date in the label is always the same you can do an exact parse when setting the parameter. Something like
...
cmd.Parameters.Add("@d1", SqlDbType.Date).Value = DateTime.ParseExact(Label1.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture)
cmd.Parameters.Add("@d2", SqlDbType.Date).Value = DateTime.ParseExact(Label2.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture)
...
   
Comments
jewel serniabad 26-Aug-21 0:02am
   
Thank you for your important time solving my problem.
But a message is still coming.

string 'label1' was not recognized as a valid DateTime.

I have seen the time format reversed in different ways
Wendelius 26-Aug-21 5:16am
   
Using the debugger, on what line you get this error? Can you post the current code your using?
jewel serniabad 26-Aug-21 12:14pm
   
cmd.Parameters.Add("@d1", SqlDbType.Date).Value = DateTime.ParseExact(Label1.Text, "yyyy-mm-dd", CultureInfo.InvariantCulture)
cmd.Parameters.Add("@d2", SqlDbType.Date).Value = DateTime.ParseExact(Label2.Text, "yyyy-mm-dd", CultureInfo.InvariantCulture)
Wendelius 26-Aug-21 14:28pm
   
When you run the code, are you sure that label1 really contains a date? Based on the error message the text in the label seems to be "label1".

Place a breakpoint on that line and investigate the text property of label1. What does it contain, could it be that you haven't yet used the date time picker and the label1 text still contains the default value...
jewel serniabad 28-Aug-21 7:00am
   
Sorry, there was a mistake for my mistake.
Thank you so much for your cooperation.
Wendelius 28-Aug-21 12:32pm
   
Glad you got it solved.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900