Solved

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

Posted on 2006-07-06
11
296 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
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 142

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 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 142

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 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 100 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

786 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