Description:
Hi all,
i was construct a view using some view.
in this view is executed in mysql querybrowser well.
but when i was open the recordset using that view.
it does not a return the records. the same query is
executed as well in querybrowser. plz suggest me.
MyCode:-
<%
dim cnConn
dim rsStock
dim rsStr
set cnConn=server.CreateObject("ADODB.Connection")
set rsStock=server.CreateObject("ADODB.Recordset")
cnConn.open(Application("cnStr"))
rsStr="Select"_
& " Inward.Product_Desc_ID ,"_
& " Inward.Product_Name,"_
& " sum(Inward.Qty) as Inward,"_
& " sum(INV.Qty) as Outward"_
& " FROM view_final_prod_inward as Inward"_
& " INNER JOIN view_final_inv_products as INV"_
& " ON Inward.Product_Desc_ID=INV.Invoice_Prod_ID"_
& " GROUP BY Inward.product_Desc_ID"_
& " ORDER BY pYear asc, nmonth asc;"
rsStock.Open(rsStr),cnConn,3,3 %>
How to repeat:
/*Step-1 : view_prod_report*/
SELECT
pm.id AS id,
pm.purchase_id AS purchase_id,
pm.invoice_id AS invoice_id,
month(pm.invoice_date) AS Month,
year(pm.invoice_date) AS Year,
pm.invoice_date AS invoice_date,
count(pi.product_desc_id) AS Tot,
pi.product_desc_id AS Product_Desc_ID,
pi.product_category AS product_category,
pi.product_name AS product_name,
pi.product_brand AS product_Brand,
pi.product_configuration AS product_configuration,
sum(pi.product_qty) AS Qty,
sum(pi.product_rpu) AS RPU,
sum(pi.product_amount) AS Product_Amount
from (tbl_products PM
inner join tbl_products_items PI
on((pm.id = pi.id)))
group by pm.id,pm.purchase_id,pm.invoice_id,pm.invoice_date,pi.product_desc_id,pi.product_brand,pi.product_configuration
order by pm.invoice_date;
/*Step-2: view_prod_report*/
Select
Inward.Product_Desc_ID,
Inward.Product_Name,
sum(Inward.Qty) as Inward,
sum(INV.Qty) as Outward
FROM view_final_prod_inward as Inward
INNER JOIN view_final_inv_products as INV
ON Inward.Product_Desc_ID=INV.Invoice_Prod_ID
GROUP BY product_Desc_ID
ORDER BY Product_Name;
/*Step-3: view_final_prod_inward*/
select
monthname(invoice_date) AS pMonth,
month(invoice_date) AS nMonth,
Year AS pYear,
Product_Desc_ID AS Product_Desc_ID,
product_name AS Product_Name,
product_configuration AS Product_Configuration,
sum(Qty) AS Qty,
round(sum(Product_Amount),2) AS Amount
from view_prod_report
group by
Month,
Year,
Product_Desc_ID
order by
Year,
Month;
/*Step-4 : view_invoice_items*/
select
id.invoice_id AS invoice_id,
id.Invoice_Num_ID AS invoice_num_id,
im.Invoice_date AS Invoice_Date,
im.Invoice_tax_type AS Invoice_Tax_Type,
month(im.Invoice_date) AS iMonth,
year(im.Invoice_date) AS iYear,
id.invoice_name AS invoice_name,
id.invoice_particular AS invoice_particular,
sum(id.invoice_qty) AS qty,
sum(id.invoice_rpu) AS rpu,
sum(id.invoice_amount) AS amount,
id.invoice_prod_id AS invoice_prod_id
from (invoice_master IM
join invoice_details ID
on(((im.Invoice_id = id.invoice_id) and (im.Invoice_num_id = id.Invoice_Num_ID) and (id.Invoice_tax_type = _latin1'Sales'))))
group by id.invoice_id,id.Invoice_Num_ID,im.Invoice_date,id.invoice_prod_id
order by id.invoice_prod_id,im.Invoice_date,id.invoice_id,id.Invoice_Num_ID;
/*Step-5: view_final_inv_products*/
select
monthname(Invoice_Date) AS pMonth,
iMonth AS iMonth,
iYear AS iYear,
Invoice_Tax_Type AS invoice_tax_type,
invoice_name AS invoice_name,
invoice_prod_id AS invoice_prod_id,
sum(qty) AS Qty,
round(sum(amount),2) AS Total
from view_invoice_items
group by iMonth,iYear,invoice_prod_id
order by iYear,iMonth;
Description: Hi all, i was construct a view using some view. in this view is executed in mysql querybrowser well. but when i was open the recordset using that view. it does not a return the records. the same query is executed as well in querybrowser. plz suggest me. MyCode:- <% dim cnConn dim rsStock dim rsStr set cnConn=server.CreateObject("ADODB.Connection") set rsStock=server.CreateObject("ADODB.Recordset") cnConn.open(Application("cnStr")) rsStr="Select"_ & " Inward.Product_Desc_ID ,"_ & " Inward.Product_Name,"_ & " sum(Inward.Qty) as Inward,"_ & " sum(INV.Qty) as Outward"_ & " FROM view_final_prod_inward as Inward"_ & " INNER JOIN view_final_inv_products as INV"_ & " ON Inward.Product_Desc_ID=INV.Invoice_Prod_ID"_ & " GROUP BY Inward.product_Desc_ID"_ & " ORDER BY pYear asc, nmonth asc;" rsStock.Open(rsStr),cnConn,3,3 %> How to repeat: /*Step-1 : view_prod_report*/ SELECT pm.id AS id, pm.purchase_id AS purchase_id, pm.invoice_id AS invoice_id, month(pm.invoice_date) AS Month, year(pm.invoice_date) AS Year, pm.invoice_date AS invoice_date, count(pi.product_desc_id) AS Tot, pi.product_desc_id AS Product_Desc_ID, pi.product_category AS product_category, pi.product_name AS product_name, pi.product_brand AS product_Brand, pi.product_configuration AS product_configuration, sum(pi.product_qty) AS Qty, sum(pi.product_rpu) AS RPU, sum(pi.product_amount) AS Product_Amount from (tbl_products PM inner join tbl_products_items PI on((pm.id = pi.id))) group by pm.id,pm.purchase_id,pm.invoice_id,pm.invoice_date,pi.product_desc_id,pi.product_brand,pi.product_configuration order by pm.invoice_date; /*Step-2: view_prod_report*/ Select Inward.Product_Desc_ID, Inward.Product_Name, sum(Inward.Qty) as Inward, sum(INV.Qty) as Outward FROM view_final_prod_inward as Inward INNER JOIN view_final_inv_products as INV ON Inward.Product_Desc_ID=INV.Invoice_Prod_ID GROUP BY product_Desc_ID ORDER BY Product_Name; /*Step-3: view_final_prod_inward*/ select monthname(invoice_date) AS pMonth, month(invoice_date) AS nMonth, Year AS pYear, Product_Desc_ID AS Product_Desc_ID, product_name AS Product_Name, product_configuration AS Product_Configuration, sum(Qty) AS Qty, round(sum(Product_Amount),2) AS Amount from view_prod_report group by Month, Year, Product_Desc_ID order by Year, Month; /*Step-4 : view_invoice_items*/ select id.invoice_id AS invoice_id, id.Invoice_Num_ID AS invoice_num_id, im.Invoice_date AS Invoice_Date, im.Invoice_tax_type AS Invoice_Tax_Type, month(im.Invoice_date) AS iMonth, year(im.Invoice_date) AS iYear, id.invoice_name AS invoice_name, id.invoice_particular AS invoice_particular, sum(id.invoice_qty) AS qty, sum(id.invoice_rpu) AS rpu, sum(id.invoice_amount) AS amount, id.invoice_prod_id AS invoice_prod_id from (invoice_master IM join invoice_details ID on(((im.Invoice_id = id.invoice_id) and (im.Invoice_num_id = id.Invoice_Num_ID) and (id.Invoice_tax_type = _latin1'Sales')))) group by id.invoice_id,id.Invoice_Num_ID,im.Invoice_date,id.invoice_prod_id order by id.invoice_prod_id,im.Invoice_date,id.invoice_id,id.Invoice_Num_ID; /*Step-5: view_final_inv_products*/ select monthname(Invoice_Date) AS pMonth, iMonth AS iMonth, iYear AS iYear, Invoice_Tax_Type AS invoice_tax_type, invoice_name AS invoice_name, invoice_prod_id AS invoice_prod_id, sum(qty) AS Qty, round(sum(amount),2) AS Total from view_invoice_items group by iMonth,iYear,invoice_prod_id order by iYear,iMonth;