1

I have created a view in my database to get the desired result. The query is as follows,

Select * 
from SignInEmployeeDetails 
where EmployeeID = 1 
  and CONVERT(DATE, LateComingDate) >= CONVERT(DATE, '04/02/2015')

I tried running it in SQL Server Management Studio and it executes fine.

But when I include this query in my ASP.Net applicaiton I get no result but when I debug and see the query it is same as the above. My ASP.Net code is as follows,

Protected Sub BindSignInDetails()
    Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionStringName").ToString())
    Dim comm As New SqlCommand(
       "Select * from SignInEmployeeDetails where EmployeeID = " + 
         EmployeeID.ToString() + 
       " and CONVERT(DATE, LateComingDate) >= CONVERT(DATE, '" + 
         txtLateComingDate.Text.ToString() + "')", con)
    con.Open()
    Dim dr As SqlDataReader = comm.ExecuteReader()
    If dr.Read() Then
        txtFirstTimeIn.Text = dr("FirstTimeIn").ToString()
        txtLateTimeDuration.Text = dr("LateTimeDuration").ToString()
    End If

    dr.Close()

    con.Close()
End Sub

What can be the issue? Could anyone help me on this?

Edited:

I have updated my code to include parameterized query as follows,

cmd.Connection = con
    cmd.CommandType = CommandType.Text
    'representing type of command

    cmd.CommandText = "Select * from SignInEmployeeDetails where EmployeeID = @EmployeeID and LateComingDate = @LateComingDate"

    'adding parameters with value
    cmd.Parameters.AddWithValue("@EmployeeID", EmployeeID.ToString())
    cmd.Parameters.AddWithValue("@LateComingDate", txtLateComingDate.Text.ToString())

    con.Open()
    Dim dr As SqlDataReader = cmd.ExecuteReader()
    If dr.Read() Then
        txtFirstTimeIn.Text = dr("FirstTimeIn").ToString()
        txtLateTimeDuration.Text = dr("LateTimeDuration").ToString()
    End If

    dr.Close()

    con.Close()

But still I don't get any results. What is the issue?

Improve this question
4
  • 1
    How are you getting the value for EmployeeID.ToString() Commented Feb 5, 2015 at 4:20
  • 1
    EmployeeID is a global variable that I am using to assign EmployeeID from Session variable. That is not the problem. As I have mentioned I tried debugging my code to see what the query is and I get the same as the one that I run in SQL Management Studio but I can't understand why it does not return any value in asp.net application. Commented Feb 5, 2015 at 4:23
  • 2
    I hope you know that you should be using SQL Parameters instead of concatenating SQL strings. This code is vulnerable to SQL injection attacks. owasp.org/index.php/SQL_Injection Commented Feb 5, 2015 at 4:29
  • 1
    Will I get the results if I modify my sql query. I tried removing date comparision in query and it returned the result but that is not what I want. I want the date to also be included. Commented Feb 5, 2015 at 4:35

4 Answers 4

Reset to default
1

The problem could be of globalization or you can say date format. You can set it in the web.config file or individual file. Following are the examples

  • en-US: M/d/yyyy (e.g. 3/14/2012)
  • en-GB: dd/MM/yyyy (e.g. 14/03/2012)

In web.config

<globalization requestEncoding="UTF-8" responseEncoding="UTF-8" uiCulture="en-GB" culture="en-GB" />
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I tried adding globalization in my web.config but still no success.
@Javed try to convert this txtLateComingDate.Text.ToString() to date in your code and use break point and see what is your query becomes copy that text and try to run it on sql server.
1

In spite of this code :

 If dr.Read() Then
       txtFirstTimeIn.Text = dr("FirstTimeIn").ToString()
       txtLateTimeDuration.Text = dr("LateTimeDuration").ToString()
   End If

use this code :

if (dr.HasRows)
    {
        while (dr.Read())
        {
         txtFirstTimeIn.Text = dr["FirstTimeIn"].ToString();
         txtLateTimeDuration.Text = dr["LateTimeDuration"].ToString();

        }
    }
Improve this answer

6 Comments

I have checked dr.HasRows while debugging it returns false. So I don't think it will be of use. The problem is why it doesn't have any rows when there is a row showing in sql management studio
could you share LateComingDate datatype in database ?
datatype of LateComingDate is nvarchar
Dear Javed , this is mismatch, your datatype is nvarchar but, you want to filter-nation on the basis of date
Dear Kumar, I tried running query in sql management studio and it returns a row but why is it not returning any row in the code? I even tried converting LateComingDate to Datetime as you can see in my query but no success.
|
0

I think there is no problem in your query. Check the database results for the exact date. And be sure you are not working with local as well as server database in sql management studio. That will make you confusing in knowing the results.

Improve this answer

1 Comment

Yes you are right. I was actually seeing the results of server database.
0

This is nothing, but your datetime format issue. By-default SQL Server work with mm/dd/yyyy format.

It seems that your pc's datetime format is dd/mm/yyyy, you just change it and try again.

You can try as above. But I solved by this setting.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.