bschwarting
asked on
SQL Syntax and Logic Help - Match Merge Data in two tables
rs through rs3 works fine. rs4 is where i am having an issue.
here is the short version of what i want to do:
the database search starts from a text box, query's CONSDB_MBRHISTDETL by MBRSEP. CONSDB_MBRHISTDETL has the MBRSEP and LOCATION fields. CONSDB_LOCINFODETL has the LOCATION field but NOT the MBRSEP field. how do i match the LOCATION fields on both tables to be able to pull the SVCADDR field from CONSDB_LOCINFODETL based on my MBRSEP query?
i made my attempt with rs4, but i think the logic is wrong, along with the syntax. any help would be appreciated.
<%
Dim Con
Dim rs
Dim rs2
Dim rs3
Dim rs4
Set rs = Server.CreateObject ("ADODB.Recordset")
Set rs2 = Server.CreateObject ("ADODB.Recordset")
Set rs3 = Server.CreateObject ("ADODB.Recordset")
Set rs4 = Server.CreateObject ("ADODB.Recordset")
Set Con = Server.CreateObject("ADODB .Connectio n")
Con.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Inetpub\ww wroot\Intr anet\bill\ database\b ills.mdb"
mySQL1 = "select * from CONSDB_MBRHISTDETL where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
mySQL2 = "select * from CONSDB_MBRSEPMSTR where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
mySQL3 = "select * from CONSDB_MEMBERDETL where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
mySQL4 = "select SVCADDR from CONSDB_LOCINFODETL where rs("LOCATION") = rs4("LOCATION") AND MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
Set rs = Con.Execute(mySQL1)
Set rs2 = Con.Execute(mySQL2)
Set rs3 = Con.Execute(mySQL3)
Set rs4 = Con.Execute(mySQL4)
Con.Execute mySQL1
Con.Execute mySQL2
Con.Execute mySQL3
Con.Execute mySQL4
%>
<%
IF rs.EOF Then
Response.Redirect ("../nodata.html")
end if
do while not rs.eof
MBRSEP = rs("MBRSEP")
DUEDATE = rs("DUEDATE")
KWH = rs("KWH")
NAME = rs2("NAME")
TELEPHONE = rs3("TELEPHONE")
ADDR1 = rs2("ADDR1")
CYCLE = rs3("CYCLE")
RATE = rs3("RATE")
LOCATION1 = rs("LOCATION")
LOCATION2 = rs4("LOCATION")
%>
here is the short version of what i want to do:
the database search starts from a text box, query's CONSDB_MBRHISTDETL by MBRSEP. CONSDB_MBRHISTDETL has the MBRSEP and LOCATION fields. CONSDB_LOCINFODETL has the LOCATION field but NOT the MBRSEP field. how do i match the LOCATION fields on both tables to be able to pull the SVCADDR field from CONSDB_LOCINFODETL based on my MBRSEP query?
i made my attempt with rs4, but i think the logic is wrong, along with the syntax. any help would be appreciated.
<%
Dim Con
Dim rs
Dim rs2
Dim rs3
Dim rs4
Set rs = Server.CreateObject ("ADODB.Recordset")
Set rs2 = Server.CreateObject ("ADODB.Recordset")
Set rs3 = Server.CreateObject ("ADODB.Recordset")
Set rs4 = Server.CreateObject ("ADODB.Recordset")
Set Con = Server.CreateObject("ADODB
Con.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Inetpub\ww
mySQL1 = "select * from CONSDB_MBRHISTDETL where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
mySQL2 = "select * from CONSDB_MBRSEPMSTR where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
mySQL3 = "select * from CONSDB_MEMBERDETL where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
mySQL4 = "select SVCADDR from CONSDB_LOCINFODETL where rs("LOCATION") = rs4("LOCATION") AND MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
Set rs = Con.Execute(mySQL1)
Set rs2 = Con.Execute(mySQL2)
Set rs3 = Con.Execute(mySQL3)
Set rs4 = Con.Execute(mySQL4)
Con.Execute mySQL1
Con.Execute mySQL2
Con.Execute mySQL3
Con.Execute mySQL4
%>
<%
IF rs.EOF Then
Response.Redirect ("../nodata.html")
end if
do while not rs.eof
MBRSEP = rs("MBRSEP")
DUEDATE = rs("DUEDATE")
KWH = rs("KWH")
NAME = rs2("NAME")
TELEPHONE = rs3("TELEPHONE")
ADDR1 = rs2("ADDR1")
CYCLE = rs3("CYCLE")
RATE = rs3("RATE")
LOCATION1 = rs("LOCATION")
LOCATION2 = rs4("LOCATION")
%>
>mySQL4 = "select SVCADDR from CONSDB_LOCINFODETL where rs("LOCATION") = rs4("LOCATION") AND MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
this cannot work like this, but this will:
mySQL4 = "select a.SVCADDR from CONSDB_LOCINFODETL a JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION and a.MBRSEP = b.MBRSEP where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
this cannot work like this, but this will:
mySQL4 = "select a.SVCADDR from CONSDB_LOCINFODETL a JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION and a.MBRSEP = b.MBRSEP where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
ASKER
ok, i tried that, here is what i got:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
/bill/cgi-bin/search_membe r_sep_elec tric.asp, line 34
line 34 --> Set rs4 = Con.Execute(mySQL4)
mySQL4 = "select a.SVCADDR from CONSDB_LOCINFODETL a JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION and a.MBRSEP = b.MBRSEP where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
/bill/cgi-bin/search_membe
line 34 --> Set rs4 = Con.Execute(mySQL4)
mySQL4 = "select a.SVCADDR from CONSDB_LOCINFODETL a JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION and a.MBRSEP = b.MBRSEP where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, now i get this:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/bill/cgi-bin/search_membe r_sep_elec tric.asp, line 34
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/bill/cgi-bin/search_membe
any better with this:
mySQL4 = "select a.SVCADDR from CONSDB_LOCINFODETL a INNER JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION and a.MBRSEP = b.MBRSEP where a.MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
mySQL4 = "select a.SVCADDR from CONSDB_LOCINFODETL a INNER JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION and a.MBRSEP = b.MBRSEP where a.MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
ASKER
same thing:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/bill/cgi-bin/search_membe r_sep_elec tric.asp, line 34
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/bill/cgi-bin/search_membe
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
how is best to do that?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i couldn't get any of the above to work, so i'm trying another route. i've setup the query to retrieve the data in access, then tried to convert that code to work in ASP. can anyone assist?
FYI, the 330 at the end needs to be CONSDB_MEMBERDETL.MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
#################
ACCESS GENERATED SQL
#################
SELECT CONSDB_MEMBERDETL.MBRSEP, CONSDB_MBRSEPMSTR.NAME, CONSDB_MEMBERDETL.RATE, CONSDB_MEMBERDETL.CLASS, CONSDB_MEMBERDETL.TELEPHON E, CONSDB_MBRHISTDETL.LOCATIO N, CONSDB_MEMBERDETL.METER, CONSDB_MBRHISTDETL.READDAT E, CONSDB_MBRHISTDETL.READDAT E, CONSDB_MBRHISTDETL.NBRDAYS SVC, CONSDB_BILLHISTDETL.PREVMT RREAD, CONSDB_BILLHISTDETL.METERR EAD, CONSDB_MBRHISTDETL.KWH, CONSDB_MBRHISTDETL.FUEL2, CONSDB_MEMBERDETL.APPLNBR
FROM CONSDB_MEMBERDETL INNER JOIN ((CONSDB_MBRSEPMSTR INNER JOIN CONSDB_BILLHISTDETL ON CONSDB_MBRSEPMSTR.MBRSEP = CONSDB_BILLHISTDETL.MBRSEP ) INNER JOIN (CONSDB_MBRHISTDETL INNER JOIN CONSDB_LOCINFODETL ON CONSDB_MBRHISTDETL.LOCATIO N = CONSDB_LOCINFODETL.LOCATIO N) ON CONSDB_BILLHISTDETL.MBRSEP = CONSDB_MBRHISTDETL.MBRSEP) ON CONSDB_MEMBERDETL.MBRSEP = CONSDB_MBRSEPMSTR.MBRSEP
WHERE (((CONSDB_MEMBERDETL.MBRSE P)=330));
FYI, the 330 at the end needs to be CONSDB_MEMBERDETL.MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
#################
ACCESS GENERATED SQL
#################
SELECT CONSDB_MEMBERDETL.MBRSEP, CONSDB_MBRSEPMSTR.NAME, CONSDB_MEMBERDETL.RATE, CONSDB_MEMBERDETL.CLASS, CONSDB_MEMBERDETL.TELEPHON
FROM CONSDB_MEMBERDETL INNER JOIN ((CONSDB_MBRSEPMSTR INNER JOIN CONSDB_BILLHISTDETL ON CONSDB_MBRSEPMSTR.MBRSEP = CONSDB_BILLHISTDETL.MBRSEP
WHERE (((CONSDB_MEMBERDETL.MBRSE
ASKER
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/bill/cgi-bin/search_membe
mySQL4 = "select SVCADDR from CONSDB_LOCINFODETL where rs("LOCATION") = rs4("LOCATION") AND MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"