I cannot get this inner join to work in asp

the chad · Aug 8, 2003

<title>Bug Status Board</title>

<%

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog =
techsupportBT; User Id = sa; Password="
Set rs = server.CreateObject("ADODB.Recordset")

sSQL="SELECT T.BugIndex, T.[Date Entered], T.DESCRIPTION,"
sSQL= ssql &" T.Company, T.Technician, T.Developer, T.PROGRAM, "
sSQL= ssql &" T.Status, T.PRIORITY, T.[Version Fix], n.noc"
sSQL= ssql &"FROM TSHIGHISSUES INNER JOIN noc ON T.BugIndex =
n.bugindex "

set rs=conn.Execute(ssql)

and this is the error message that I get
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Incorrect syntax near the keyword 'INNER'

I cannot find an example anywhere that helps with this situation.
This works fine in SQL, but for some reason asp is being a pain.

Manohar Kamath [MVP] · Aug 8, 2003

You forgot to alias the tables:

FROM TSHIGHISSUES T INNER JOIN noc n ON

Pappas Mike · Aug 9, 2003

Try this
you should say
" from tableone inner join tabletwo on tableone.field=tabletwo.field;"

sSQL="SELECT [T].[BugIndex], [T].[Date Entered], [T].[DESCRIPTION],"
sSQL= ssql &" [T].[Company], [T].[Technician], [T].[Developer],
[T].[PROGRAM], "
sSQL= ssql &" [T].[Status], [T].[PRIORITY], [T].[Version Fix], [n].[noc]"
sSQL= ssql &" FROM [T] INNER JOIN [n] ON [T].[BugIndex] = [n].[bugindex];"

The Chad · Aug 11, 2003

Thank you very much for all of your help. But you know how when you
think you are done with a project, you think of seomthing else that
would be nice to add? Well I thought of one that I cannot figure out.

This web page returns a result into another page called noc,when you
select the bug# from the primary page.The noc page Then displays the
bug# with a description and the noc(number of calls). I would like to be
able to update the noc from the noc page. This is my page as it stands
right now.

<title>Number of Calls</title>
<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog =
techsupportBT; User Id = sa; Password="
Set rsNOC = server.CreateObject("ADODB.Recordset")
strBugIndex = trim(Request.QueryString("bindex"))
strSql = "Select n.bugindex, t.description, n.noc "
strSql = strSql & "from NOC n, tshighissues t "
strSql = strSql & "where n.BugIndex = " & strBugIndex & " "
strSql = strSql & "And t.BugIndex = n.BugIndex "
set rsNOC=conn.Execute(strSql)
%><body bgcolor="#E8E8EA"><div align="left">
<p>
<a href="bug2.asp">
<img border="0" src="hdr_logo.jpg" width="240" height="37"></a> </p>
<p align="left">
<u><font size="6"
color="#94B2C6"><b><i>        &n
bsp;           &n
bsp;           &n
bsp;           &n
bsp;           &n
bsp;          
Bug Status
Board           &
nbsp;           &
nbsp;           &
nbsp;           &
nbsp;          
</i></b></font></u></p>
 <Table bordercolorlight="#94B2C6" bordercolordark="#000080"
border="2" bordercolor="#F3F3F3" align="left" width="100%">
<tr>
<th height="28">Bug #</th>
<th height="28">Description</th>
<th height="28">NOC</th>

</tr>
<%
While not rsNOC.EOF
%>
<tr>
<td><%=rsNOC.fields(0)%></td>
<td><%=rsNOC.fields(1)%></td>
<td><%=rsNOC.fields(2)%></td>

</tr>
</div>

What do you think would be the best way to add an update script into
this page to be able to update the noc column?

<%
rsNOC.MoveNext()
Wend
%><Close recrordsets and connection></body></html>

Syntax for wildcard query in ASP 3.0	4	Mar 24, 2007
OLEDB Connection String	2	Dec 16, 2005
Using the like command in SQL	2	Nov 3, 2005
split array twice and do a multiple insert	1	Dec 20, 2006
How do I do this with ADO and an Access DB in ASP 3.0?	3	Sep 9, 2006
SQL 2005/ ASP : ADODB.Connection error '800a0e7a' Provider cannot befound. It may not be properly in	1	Mar 25, 2010
Database into an array	2	Dec 15, 2005
Inserting Random Passwords into Database	14	Dec 4, 2003

I cannot get this inner join to work in asp

the chad

Manohar Kamath [MVP]

Pappas Mike

The Chad

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads