Hi Tim D,
Thanks for the input. I abandoned trying to access data from two table
using one Hitlist element.
I used the following code to conect to the second db table, but it is only
returning the first row of data. I think it has something to do with the
placemen tof the PreExecute tags
Dim AnalyteName, AnalysisDateAndTime, MethodCode, Result,
DetectFlagString, FailureToMeetStandardFlagString, DetectionLimit,
UnitOfMeasure, DilutionFactor, QALabCodes, DemographicID, SortGroup,
sConnSample, conn, rs, rstemp, MySQLView
'Opens the TestResults table database
set conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
sConnSample = "Provider=SQLOLEDB; Data Source=10.42.196.112,1503; Initial
Catalog=WellTestResults; Persist Security Info=true; User ID=RedDotUser;
Password=xxxxxxx"
conn.Open sConnSample
'Retrieves the test results data from the TestResults table for each record
in the Demographic table by referencing the DemographicID field
MySQLView="select AnalyteName, AnalysisDateAndTime, MethodCode, Result,
DetectFlagString, FailureToMeetStandardFlagString, DetectionLimit,
UnitOfMeasure, DilutionFactor, QALabCodes from vwTestResult where
DemographicID = '<%dbReference%>' ORDER BY SortGroup"
set rstemp=conn.execute(MySQLView)
'Redirects to customized exception handling page if record has been deleted
prior to automated republishing
If rstemp.eof then
Response.Redirect "http://www.dutchessny.gov/Municipalities/15991.htm";
End If
%><!/IoRangePreExecute>
<p><span class="head10ptburgbold"> Test Results</span><br>
<center><table class = "depttablepwtr" border="1" cellpadding="4"
cellspacing="0" style="bordercolor="#273b5f" width = "1300">
<tr class = "deptableheading" ><th class = "text8pt" ><br><br>Analyte
Name</th>
<th class = "text8pt"><br>Date/Time<br>of Analysis</th>
<th class = "text8pt"><br>Method<br>Code</th>
<th class = "text8pt"><br><br>Result*</th>
<th class = "text8pt"><br>Detect<br>Flag</th>
<th class = "text8pt">Failure<br>to Meet<br>Standard</th>
<th class = "text8pt"><br><br>DL</th>
<th class = "text8pt"><br>Unit of<br>Measure</th>
<th class = "text8pt"><br>Dilution<br>Factor</th>
<th class = "text8pt">QA<br>Lab<br>Codes</th>
</tr>
<!IoRangePreExecute><%
'Loops through TestResults database table and displays selected fields in
table grid
rstemp.movefirst
do while not rstemp.eof
%><!/IoRangePreExecute>
<tr>
<td class = "text8pt" width = "15%"
><!IoRangePreExecute><%=rstemp("AnalyteName")%>
<!/IoRangePreExecute> </td>
<td align="center" class = "text8pt" width = "10%"><!IoRangePreExecute
><%=rstemp("AnalysisDateAndTime")%><!/IoRangePreExecute> </td>
<td align = "center" class = "text8pt" width = "10%"><!IoRangePreExecute
><%=rstemp("MethodCode")%><!/IoRangePreExecute> </td>
<td align = "right" class = "text8pt" width =
"5%"><!IoRangePreExecute><%=rstemp("Result")%>
<!/IoRangePreExecute> </td>
<td align = "center" class = "text8pt" width =
"5%"><!IoRangePreExecute><%=rstemp("DetectFlagString")%>
<!/IoRangePreExecute> </td>
<td align = "center" class = "text8pt" width =
"5%"><!IoRangePreExecute><%=rstemp("FailureToMeetStandardFlagString")%>
<!/IoRangePreExecute> </td>
<td align = "right" class = "text8pt" width =
"5%"><!IoRangePreExecute><%=rstemp("DetectionLimit")%>
<!/IoRangePreExecute> </td>
<td align = "center" class = "text8pt" width = "5%"><!IoRangePreExecute
><%=rstemp("UnitOfMeasure")%><!/IoRangePreExecute> </td>
<td align = "center" class = "text8pt" width =
"5%"><!IoRangePreExecute><%=rstemp("DilutionFactor")%>
<!/IoRangePreExecute> </td>
<td align = "center" class = "text8pt" width = "5%"><!IoRangePreExecute
><%=rstemp("QALabCodes")%><!/IoRangePreExecute> </td></tr>
<!IoRangePreExecute><%
rstemp.movenext
LOOP
%><!/IoRangePreExecute>
</table></center>
<!IoRangePreExecute><%
'Close and dispose resources
rstemp.close
set rstemp=nothing
conn.close
set conn=nothing
%><!/IoRangePreExecute>
On Friday, June 21, 2013 1:49:50 PM UTC-4, Tim D wrote:
> when you do the single table do you use the '.' notation as well? I'd
> guess not. Perhaps its a matching problem and if you do
>
> Select table.field as fieldalias .... it will work for you.
>
> http://msdn.microsoft.com/en-us/library/ms187731.aspx
>
>
> On Thursday, June 20, 2013 9:44:10 AM UTC-4, trei wrote:
>>
>> Hi, Has anyone used the HitList element to connect to a database and
>> query two tables? Do you need a HitList element for both tables. The
>> following sql statement tests successful in Edit Database Query, but when I
>> attempt to preview the page, a PrepareTemplateVariantElement Error#0 is
>> returned (works when querying one table):
>>
>> SELECT vwDemographic.DemographicID, vwDemographic.ResultSubmissionID,
>> vwDemographic.MunicipalityValue, vwDemographic.LocationAddress,
>> vwDemographic.SectionNo, vwDemographic.BlockNo, vwDemographic.LotNo,
>> vwDemographic.Latitude, vwDemographic.Longitude,
>> vwDemographic.SamplingPoint, vwDemographic.WellPermitNo,
>> vwDemographic.IsTreatedSampleString, vwTestResult. DemographicID,
>> vwTestResult.AnalyteName, vwTestResult.MethodCode from vwDemographic,
>> vwTestResult WHERE vwDemographic.MunicipalityValue = '135689' AND
>> vwDemographic.ProjectValue = 'Town' ORDER BY
>> vwDemographic.MunicipalityValue, vwDemographic.SectionNo,
>> vwDemographic.BlockNo, vwDemographic.LotNo
>>
>>
>
--
You received this message because you are subscribed to the Google Groups
"RedDot CMS Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/reddot-cms-users.
For more options, visit https://groups.google.com/groups/opt_out.
