?
Solved

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

Posted on 2006-07-06
11
Medium Priority
?
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 1

Author Comment

by:bschwarting
ID: 17053470
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17054011
>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
ID: 17054084
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 17054132
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
ID: 17054164
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17054215
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
ID: 17054281
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 17054440
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
ID: 17054660
how is best to do that?
0
 
LVL 25

Assisted Solution

by:clockwatcher
clockwatcher earned 400 total points
ID: 17056527
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
ID: 17063500
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

752 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