Solved

SQL Syntax and Logic Help - Match Merge Data in two tables

Posted on 2006-07-06
11
290 Views
Last Modified: 2012-05-05
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.Connection")
Con.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Inetpub\wwwroot\Intranet\bill\database\bills.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")

%>
0
Comment
Question by:bschwarting
  • 6
  • 4
11 Comments
 
LVL 1

Author Comment

by:bschwarting
Comment Utility
FYI, here is the error i get on my syntax.

Microsoft VBScript compilation  error '800a0401'
Expected end of statement
/bill/cgi-bin/search_member_sep_electric.asp, line 28
mySQL4 = "select SVCADDR from CONSDB_LOCINFODETL where rs("LOCATION") = rs4("LOCATION") AND MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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") & "%'"

0
 
LVL 1

Author Comment

by:bschwarting
Comment Utility
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_member_sep_electric.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") & "%'"
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
Comment Utility
Obviously I missed the "ACCESS Database" information...

Access requires to write INNER JOIN (and not simply JOIN):

mySQL4 = "select a.SVCADDR from CONSDB_LOCINFODETL a INNER JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION and a.MBRSEP = b.MBRSEP where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
0
 
LVL 1

Author Comment

by:bschwarting
Comment Utility
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_member_sep_electric.asp, line 34
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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") & "%'"
0
 
LVL 1

Author Comment

by:bschwarting
Comment Utility
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_member_sep_electric.asp, line 34
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
Comment Utility
could you take the sql generated and run that directly on access, and tell us what he prompts you for?
0
 
LVL 1

Author Comment

by:bschwarting
Comment Utility
how is best to do that?
0
 
LVL 25

Assisted Solution

by:clockwatcher
clockwatcher earned 100 total points
Comment Utility
You said:

  CONSDB_LOCINFODETL has the LOCATION field but NOT the MBRSEP field.  

based on that give this a try:

  select a.SVCADDR from CONSDB_LOCINFODETL a INNER JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION where b.MBRSEP LIKE '%" & replace(Request.Form("MBRSEP"),"'","''") & "%'"

and you may really be after this:

  select distinct a.SVCADDR from CONSDB_LOCINFODETL a INNER JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION where b.MBRSEP LIKE '%" & replace(Request.Form("MBRSEP"),"'","''") & "%'"

If your history detail has multiple records per mbrsep and you only want a the distinct svcaddr-- not sure what you're really after nor clear on your data structure but based on the fact you're only pulling SVCADDR the distinct clause probably makes more sense.
0
 
LVL 1

Author Comment

by:bschwarting
Comment Utility
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.TELEPHONE, CONSDB_MBRHISTDETL.LOCATION, CONSDB_MEMBERDETL.METER, CONSDB_MBRHISTDETL.READDATE, CONSDB_MBRHISTDETL.READDATE, CONSDB_MBRHISTDETL.NBRDAYSSVC, CONSDB_BILLHISTDETL.PREVMTRREAD, CONSDB_BILLHISTDETL.METERREAD, 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.LOCATION = CONSDB_LOCINFODETL.LOCATION) ON CONSDB_BILLHISTDETL.MBRSEP = CONSDB_MBRHISTDETL.MBRSEP) ON CONSDB_MEMBERDETL.MBRSEP = CONSDB_MBRSEPMSTR.MBRSEP

WHERE (((CONSDB_MEMBERDETL.MBRSEP)=330));

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now