Solved

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

Posted on 2006-07-06
11
293 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

932 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

12 Experts available now in Live!

Get 1:1 Help Now