Sukesh Shukla
asked on
Problem in reteriving data from database
While retreiving data from database , i'm checking 3 conditions together, but the data is not getting displayed in gridview...
protected void Page_Load(object sender, EventArgs e)
{
GetResults();
}
private void GetResults()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HousingConnectionString"].ConnectionString);
conn.Open();
string query;
SqlCommand com;
SqlDataReader reader;
SqlDataAdapter adapter = new SqlDataAdapter();
query = "SELECT Flat ,First_Name , Middle_Name , Last_Name , Mobile_No , Email, DOB , Age , Education , Office_Add , Native_Add , PAN_Card , Aadhar_Card , Religion , Business_Job , Married_Unmarried , No_Of_Members , Joining_Date ,Leaving_Date from Ex_Primary_Member where Flat='" + Request.QueryString["fnum"] + "' and Joining_Date='" + Request.QueryString["jdate"] + "' and Leaving_Date='" + Request.QueryString["ldate"] + "'";
com = new SqlCommand(query, conn);
adapter.SelectCommand = new SqlCommand(query, conn);
reader = com.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind();
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Pick 3 common values that you expect to give a result and run the query straight on the database (in a query window in SSMS). Do you get the expected results? (yes, I _know_ that seems a silly question, but you wouldn't believe how often one writes code and testing fails ... because the right data isn't present!)
Try putting the query into a stored procedure and invoking that from your ASP instead of a direct query.
In T-SQL that would be
It'll be more secure (look up "SQL Code Injection") and also you'll be able to test it very easily with all sorts of edge cases (e.g. null for all three input parameters!) before you attach it to your front-end code. This should make it much easier for you to see where your problem is.
hth
Mike
Try putting the query into a stored procedure and invoking that from your ASP instead of a direct query.
In T-SQL that would be
create procedure MyTest
(
@intFlatNum int,
@daJoiningDate date,
@daLeavingDate date
)
as
Begin
set nocount on
SELECT Flat ,First_Name , Middle_Name , Last_Name , Mobile_No , Email, DOB , Age , Education ,
Office_Add , Native_Add , PAN_Card , Aadhar_Card , Religion , Business_Job ,
Married_Unmarried , No_Of_Members , Joining_Date ,Leaving_Date
from dbo.Ex_Primary_Member
where Flat = @intFlatNum
and Joining_Date = @daJoiningDate
and Leaving_Date = @daLeavingDate
End
It'll be more secure (look up "SQL Code Injection") and also you'll be able to test it very easily with all sorts of edge cases (e.g. null for all three input parameters!) before you attach it to your front-end code. This should make it much easier for you to see where your problem is.
hth
Mike
It might not be that the data is not displayed, it can simply be that there is no data. First thing is to make sure that you are retrieving something.
Check the DataReader HasRows property is True. If not, then the problem is with your SQL. Try what was suggested by previous experts.
Check the DataReader HasRows property is True. If not, then the problem is with your SQL. Try what was suggested by previous experts.
e.g.
if a stored date includes time like this 2015:10:10 12:13:14.12345
then
2015-10-10 12:13:14.12345 <> 2015-10-10
What happens if you omit the 2 date filters?
just for reference:
Open in new window