Hi. I'm running MySQL on a Windows 2003 server and connecting using
classic ASP.
I'm trying to connect and query two databases using a statement like:
strSQLCombo = "select sfproducts.products.sku as psku,
sfproducts.products.sortName,
sum(mcomorderentry.order_items.quantity-mcomorderentry.order_items.unitsCredited)
as netUnits,
sum((mcomorderentry.order_items.quantity-mcomorderentry.order_items.unitsCredited)*mcomorderentry.order_items.unitPrice)
as tDollars from sfproducts.products, mcomorderentry.order_items where
sfproducts.products.sku like 'ADIS%' and sfproducts.products.sku =
mcomorderentry.order_items.sku and mcomorderentry.order_items.EntryDate
between '2006-07-01' and '2006-07-31' or sfproducts.products.sku like
'BKT%' and sfproducts.products.sku = mcomorderentry.order_items.sku and
mcomorderentry.order_items.EntryDate between '2006-07-01' and
'2006-07-31' group by mcomorderentry.order_items.sku asc;"
I've tested the statement through MySQL Front and it works fine. But how
do I connect to two DBs using ASP? My code typically would look like:
dim myConnProd
dim strConnProd
dim myConnSales
dim strConnSales
dim strSQLCombo
dim rsCombo
dim adCmdText
strConnProd = "sfProducts"
set myConnProd = server.createObject("ADODB.Connection")
myConnProd.Open strConnProd
strConnSales = "mComOrderEntry"
set myConnSales = server.createObject("ADODB.Connection")
myConnSales.Open strConnSales
set rsCombo = server.createObject("ADODB.Recordset")
rsCombo.cursorLocation = 3
rsCombo.open strSQLCombo, *-->what syntax do I use here?<--*, adCmdText
because typically, I'd use: rsCombo.open strSQLCombo, *myConnProd*,
adCmdText
Or am I just hosed on this one and need to create a temporary table or
query products and then loop through to get the sales?
TIA,
Brent