Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
string strQuery = "SELECT * FROM Order_Transaction, Order_Purchased WHERE Date_Purchased >='" + startdate + "' AND Date_Purchased <='" + enddate + "'";

Is this possible?


Other help: Suggest some reporting technique that I can use in my Inventory System. :)

Thank you for your help. :)
Posted
Comments
Kornfeld Eliyahu Peter 2-Sep-14 4:06am    
Please learn SQL - go and search for JOIN in SQL...
[no name] 2-Sep-14 4:25am    
why not?
select columns both of your tables and bind with gridview

1 solution

it is sort of possible!

Assuming your two order tables both contain a column called 'Order_number'

SQL
select * 
from Order_Transaction AS OT
  join Order_Purchased as OP 
    ON OT.Order_number = OP.Order_number
WHERE
OT.datePurchased >= @startDate AND OT.datePurchased <= @endDate



Use parameters (the @startDate and @endDate rather than concatenating strings, as doing string concatenation can lead to problems (imagine if someone typed "; drop table Order_Transaction" as one of the dates?)

You don't have to use the join syntax - you could do

SQL
select *
from Order_Transaction AS OT,
  Order_Purchased as OP
    WHERE OT.Order_number = OP.Order_number


which is equally as valid, but tends to be less widely used
 
Share this answer
 
Comments
Kornfeld Eliyahu Peter 2-Sep-14 4:29am    
The problem with your last sample is not valid or invalid, but that in the engine it runs as a cross-join (in MS-SQL at least), so it will create numerous records and can be extremely slow...
_Maxxx_ 2-Sep-14 4:47am    
It produces identical results to a join and (I believe - been a long time since I bothered to look for myself) an identical execution plan.
it is not a cross-join because the Where clause effectively joins the two tables
Kornfeld Eliyahu Peter 2-Sep-14 5:05am    
Don't get me wrong - it is a solution (not too good as you surely understand). I only try to explain why it 'tends to be less widely used'...
And even with the where clause in place (and event those columns are indexes) it is a cross join with index scan (and not index seek)...
_Maxxx_ 2-Sep-14 5:36am    
But I'm sorry, you are wrong. The two queries perform and work identically.
XCode2010 2-Sep-14 4:33am    
protected void Button2_Click(object sender, EventArgs e)
{
string startdate = ddlDay.Text + "/" + ddlMonth.Text + "/" + ddlYear.Text;
string enddate = ddlDay2.Text + "/" + ddlMonth2.Text + "/" + ddlYear2.Text;
SqlCommand cmd = new SqlCommand("SELECT Customer_Transaction.Order_Number, Customer_Purchased.ProductID, Customer_Purchased.productName, Customer_Transaction.Branch, Customer_Purchased.quantity, Customer_Purchased.Date_Report FROM Customer_Purchased INNER JOIN Customer_Transaction ON Customer_Purchased.Item_Purchased_Number = Customer_Transaction.Item_Purchased_Number WHERE Date_Report BETWEEEN'" + startdate + "'AND'" + enddate + "'");
DataTable dt = GetData(cmd);
GridView1.DataSource = dt;
GridView1.DataBind();
}

This is my code and I got an error "An expression of non-boolean type specified in a context where a condition is expected, near 'BETWEEEN'." Can you help me sir MAXXX?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900