I cannot get this inner join to work in asp

#1 July 19th, 2005, 07:29 AM

<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.

#2 July 19th, 2005, 07:29 AM

You forgot to alias the tables:

FROM TSHIGHISSUES T INNER JOIN noc n ON

--
Manohar Kamath
Editor, .netBooks
www.dotnetbooks.com

"the chad" <cswartz@stromberg.com> wrote in message
news:d5957558.0308071609.4b467b63@posting.google.c om...[color=blue]
> 
>
> <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.[/color]

#3 July 19th, 2005, 07:30 AM

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];"

#4 July 19th, 2005, 07:32 AM

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>    &nbs p;   &n
bsp;       &nbs p;   &n
bsp;       &nbs p;   &n
bsp;       &nbs p;   &n
bsp;       &nbs p;   &n
bsp;       &nbs p;  
Bug Status
Board       &nb sp;   &
nbsp;       &nb sp;   &
nbsp;       &nb sp;   &
nbsp;       &nb sp;   &
nbsp;       &nb sp;  
</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>

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

What is Bytes?

Popular Articles