Asp.net+VB+SQL Date errors while calling data from sql table in aspx page

Baiju EP 121 Reputation points
2022-08-22T12:48:18.883+00:00

I am using aspx+vb+sql in my web page. while calling a data from table i am getting errors as under.

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I am using the following ocde

Protected Sub dttxt_TextChanged(sender As Object, e As EventArgs) Handles dttxt.TextChanged  
        Dim con2 As New SqlConnection(Str)  
        con2.Open()  
        Dim qstr2 As String  
        qstr2 = "Select * from Clinic_Prescription where EffDate='" + dttxt.Text + "'"  
        Dim sqladp2 As New SqlDataAdapter(qstr2, con2)  
        Dim dt2 As New DataSet  
        sqladp2.Fill(dt2, "Clinic_Prescription")  
        GridView1.DataSource = dt2  
        GridView1.DataBind()  
        con2.Close()  
    End Sub  
ASP.NET Web Forms
ASP.NET Web Forms
A part of the ASP.NET web application framework that can be used to create ASP.NET web applications.
464 questions
No comments
{count} votes

Accepted answer
  1. Lan Huang-MSFT 11,821 Reputation points Microsoft Vendor
    2022-08-23T07:58:37.98+00:00

    Hi @Baiju EP ,
    I tested your code, you just need to change datepicker's dateFormat to: 'mm-dd-yy'.
    233929-image.png
    Best regards,
    Lan Huang


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

1 additional answer

Sort by: Most helpful
  1. Michael Taylor 41,191 Reputation points
    2022-08-22T14:39:02.517+00:00

    Your table Clinic_Prescription has a column EffDate that is a DATETIME. In your where clause you are passing a string value and asking SQL to convert it to a DATETIME. But it can't and you get the error. That is the problem.

    Problem 2 however, and the bigger issue, is that you are a prime candidate for a SQL Injection attack. Never, ever take any sort of input and inject it into a database query. You have effectively given a malicious user access to your DB. Imagine that I, a user, put the following value into your text box `';DELETE FROM Clinic_Prescription;SELECT * FROM Clinic_Prescription WHERE EffDate = ''. I just wiped your table.

    The solution to problem 2 is to always use parameterized queries.

       Dim cmd As New SqlCommand("SELECT * FROM Clinic_Prescription WHERE EffDate = @date", con2)  
       Dim sqladp2 As New SqlDataAdapter(cmd)  
       cmd.Parameters.AddWithValue("@date", dttxt.Text)  
    

    Now if a user tries to inject SQL the call will fail.

    With that code in place we're ready to fix the first problem. You need to ensure the value entered by the user is a valid date. The best approach is to use a masked field and convert to a DateTime. For our purposes we'll do it manually.

       Dim effectiveDate As DateTime  
       If DateTime.TryParse(dttxt.Text, effectiveDate) Then  
           ' Successfully converted to a datetime so send to DB as parameter  
       Else  
           ' Bad value, report issue to user  
       End If  
         
       Dim cmd As new SqlCommand(...)  
       cmd.Parameters.AddWithValue("@date", effectiveDate)  
    

    You attempt to convert to a DateTime value. If successful then you make your DB call passing the formatted DT value to the database. Otherwise you should report the issue to the user.