Date errors while calling data from sql table in aspx page

Baiju EP 141 Reputation points

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)  
        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  
    End Sub  
A set of technologies in the .NET Framework for building web applications and XML web services.
3,243 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lan Huang-MSFT 25,386 Reputation points Microsoft Vendor

    Hi @Baiju EP ,
    I tested your code, you just need to change datepicker's dateFormat to: 'mm-dd-yy'.
    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Michael Taylor 47,711 Reputation points

    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  
           ' 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.