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

Question

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  

Answer 1

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.

Answer 2

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.