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)
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
Dim cmd As new SqlCommand(...)
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.
cooldadtx provided excellent feedback. Did you follow the advice and fix your shoddy VB.NET code?
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.