question

BaijuEP-6339 avatar image
0 Votes"
BaijuEP-6339 asked cooldadtx commented

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

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


dotnet-aspnet-webforms
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LanHuang-MSFT avatar image
0 Votes"
LanHuang-MSFT answered

Hi @BaijuEP-6339,
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.



image.png (11.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered cooldadtx commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am using a date picker for the`enter code here` date field

 <script type="text/javascript">
     $(document).ready(function () {
         $('#<%= dttxt.ClientID%>').datepicker({
               dateFormat: 'dd-mm-yy',
               changeMonth: true,
               changeYear: true
           });
       });
 </script>


0 Votes 0 ·

I am using a date picker for the`enter code here` date field

cooldadtx provided excellent feedback. Did you follow the advice and fix your shoddy VB.NET code?

0 Votes 0 ·

Your date format that you're using in the UI doesn't match the date format that SQL is expecting (check the SQL collation). Since they don't match (and shouldn't have to) you can convert to DateTime in your C# code and then pass that to SQL and it'll handle the conversion properly. You have a formatting issue here. Avoid passing strings to the DB when they are other types to prevent this.

0 Votes 0 ·