newbie question

A

Auddog

First, let me thank you for any help that you mgiht be able to offer.

I have a sql query that I'm trying to get the variable for and I keep
getting the same error:

Item cannot be found in the collection corresponding to the requested name
or ordinal.

Here is my database layout:

Database layout (2 tables)

Table 1

Projects (Table name)
id (primary key)
projectid
projectname

Table 2

ShopLog (Table Name)
id (primary key)
fk_id (foreign key to match projects - id)
sd_title
receivedfrom


Here is my query:

strSQL = "SELECT ShopLog.id, ShopLog.fk_projectid, ShopLog.sd_title,
ShopLog.receivedfrom, Projects.id, Projects.projectname FROM ShopLog,
Projects WHERE ShopLog.fk_projectid="& pid &" and Projects.id = "& pid &""

I'm just not sure how to set the variables for this type of sql call.


Here is my page code:

<%@ language="vbscript" %>
<% Option Explicit %>
<% Response.Buffer=True %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<!-- #include file="dbConn.asp" -->
<!-- #include file="adovbs.inc" -->

<%
Dim adoDB, adoRS, strSQL, iRecordCount, pid, projectname
'id variable passed from previous page set to new variable pid
pid = Request.QueryString("id")
' Create the Database Object, and connect to it
Set adoDB = Server.CreateObject("ADODB.Connection")
adoDB.Open strConnection
' Create the Recordset Object
Set adoRS = Server.CreateObject("ADODB.RecordSet")
' Create a SQL string, to open the recordset
strSQL = "SELECT ShopLog.id, ShopLog.fk_projectid, ShopLog.sd_title,
ShopLog.receivedfrom, Projects.id, Projects.projectname FROM ShopLog,
Projects WHERE ShopLog.fk_projectid="& pid &" and Projects.id = "& pid &""

adoRS.Open strSQL, adoDB, adOpenDynamic, adLockReadOnly, adCmdText
%>

<head>
<title>Shop Log</title>
</head>

<body>
<div align="center">
<table border=1>
<tr>
<td colspan=4><% Response.Write adoRS("projectname") %></td>
</tr>
<tr>
<td>Shop Log Title</td>
<td>From</td>
<td>CheckIn Date</td>
<td>Returned</td>
</tr>
<tr>
<%
'iRecordCount = 0
DO WHILE NOT adoRS.EOF
'IF iRecordCount Mod 2 = 0 THEN
%>
<td><a href="edit_project.asp?id=<% = adoRS("id") %>"><% Response.Write
adoRS("sd_title") %></a></td>
<td><% Response.Write adoRS("receivedfrom") %></td>
<td><% Response.Write adoRS("checkindate") %></td>
<td><% Response.Write adoRS("returned") %></td>
</tr>
</tr>
<% adoRS.MoveNext
Loop

' Tidy up afterwards
adoRS.Close
Set adoRS = Nothing
adoDB.Close
Set adoDB = Nothing
%>
</tr>
</table>
</div>
</body>
</html>


A
 
M

Mark Schupp

I would have expected a different error.

You have 2 columns named "id" in your selection list try renaming them using
"AS"
strSQL = "SELECT ShopLog.id as shoplogid, ShopLog.fk_projectid,
ShopLog.sd_title, ShopLog.receivedfrom, Projects.id as projectsid,
Projects.projectname FROM ShopLog, Projects WHERE ShopLog.fk_projectid="&
pid &" and Projects.id = "& pid &""
Click to expand...

use the new name to reference the column when retrieving the data.
 
D

Dthmtlgod

You get that error when it can't find a field name.

ShopLog.fk_projectid ( I don't see this listed as a field)
 
A

Auddog

I tried changing the query to use the AS, but that didn't solve the problem.
I still get the same error.

As for the second post here is what I take from it.

From what you are saying, I should really set the Response.Write to read

<% Response.Write adoRS("ShopLog.receivedfrom") %>

But when I do that I still get the error.

I did notice the same error in the query string and I also adjusted that.
But still no go.

I have ran out of ideas now - thanks for the help that you have provided.

A
 
B

Bob Lehmann

You are using fk_projectid in your query, but in the table it's named fk_id.

What was using "AS" supposed to accomplish?

Bob Lehmann
 
A

Auddog

OK, I changed the query string to (I cut it done to the basic):

strSQL = "SELECT ShopLog.id, ShopLog.fk_id, ShopLog.sd_title, Projects.id,
Projects.projectname from ShopLog, Projects where ShopLog.fk_projectid="&
pid &" and Projects.id = "& pid &""

When I ran the page, I still get the same error. Here is the part that I
don't understand. If I comment out the <% Response.Write
adoRS("projectname") %>. All the other field come in. I still just don't
get it.

A
 
M

Morris

Following on from Bob's post, see inline comments

Bob said:
You are using fk_projectid in your query, but in the table it's named fk_id.

What was using "AS" supposed to accomplish?

Bob Lehmann
Click to expand...

You original error was as a result of querying a field named
fk_projectid which doesn't exist in your database. You called it fk_id.
Your strSQL should look like this:

strSQL = "SELECT ShopLog.id as shoplogid, ShopLog.fk_id,
ShopLog.sd_title, ShopLog.receivedfrom, Projects.id as projectid,
Projects.projectname FROM Projects INNER JOIN ShopLog ON Projects.id =
ShopLog.fk_id WHERE Projects.id=" & pid



The second problem with your query is that two fields have the same
name- "id" - even though they are in different tables: Shoplog.id and
Projects.id. This will cause another error. To prevent this, you
should give these field names an alias using the AS keyword. So you
would "SELECT ShopLog.id as shoplogid....", and then use response.write
adoRS("shoplogid").

You can not response.write field names by putting their table names in
front eg response.write("tablename.fieldname") is invalid.

Morris
 
A

Auddog

Ok, I gave that a try also, but still no luck. I actually just copied the
query into my code, made sure that everything matched up and saved the page.
Still nothing. I know that I should be able to do this - do you think that
I could be missing anything else?

A
 
M

Morris

Auddog said:
Ok, I gave that a try also, but still no luck. I actually just copied the
query into my code, made sure that everything matched up and saved the page.
Still nothing. I know that I should be able to do this - do you think that
I could be missing anything else?

A
Click to expand...

What's your error message?

Morris
 
M

Mark Schupp

re-post the exact SQL statement you are using and also the code you are
using to access the columns. indicate exactly which statement is returning
the error message.
 
A

Auddog

Thanks again for the help.

First here is the error that I get:

a.. Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name
or ordinal.
/tcep/shoplog_list.asp, line 47



Database Layout

Table 1 - Projects

id
projectid
projectname

Table 2 - ShopLog

id
fk_projectid
sd_title
receivedfrom


<%@ language="vbscript" %>
<% Option Explicit %>
<% Response.Buffer=True %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<!-- #include file="dbConn.asp" -->
<!-- #include file="adovbs.inc" -->

<%
Dim adoDB, adoRS, strSQL, iRecordCount, pid, projectname
'id variable passed from previous page set to new variable pid
pid = Request.QueryString("id")
' Create the Database Object, and connect to it
Set adoDB = Server.CreateObject("ADODB.Connection")
adoDB.Open strConnection
' Create the Recordset Object
Set adoRS = Server.CreateObject("ADODB.RecordSet")
' Create a SQL string, to open the recordset
strSQL = "SELECT ShopLog.id as shoplogid, ShopLog.fk_projectid,
ShopLog.sd_title, ShopLog.receivedfrom, Projects.id as projectid,
Projects.projectname FROM Projects INNER JOIN ShopLog ON Projects.id =
ShopLog.fk_projectid WHERE Projects.id=" & pid & ""


adoRS.Open strSQL, adoDB, adOpenDynamic, adLockReadOnly, adCmdText
%>

<head>
<title>Shop Log Depot</title>
</head>

<body>
<div align="center">
<table border=1>
<tr>
<td colspan=4><% Response.Write adoRS("Projects.projectname") %></td>
</tr>
<tr>
<td>Shop Log Title</td>
<td>From</td>
<td>CheckIn Date</td>
<td>Returned</td>
</tr>
<tr>
<%
DO WHILE NOT adoRS.EOF
%>
<td><a href="edit_project.asp?id=<% = adoRS("pid") %>"><% Response.Write
adoRS("ShopLog.sd_title") %></a></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</tr>
<% adoRS.MoveNext
Loop

' Tidy up afterwards
adoRS.Close
Set adoRS = Nothing
adoDB.Close
Set adoDB = Nothing
%>
</tr>
</table>
</div>
</body>
</html>
 
M

Morris

Auddog said:
Thanks again for the help.

First here is the error that I get:

a.. Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name
or ordinal.
/tcep/shoplog_list.asp, line 47



Database Layout

Table 1 - Projects

id
projectid
projectname

Table 2 - ShopLog

id
fk_projectid
sd_title
receivedfrom


<%@ language="vbscript" %>
<% Option Explicit %>
<% Response.Buffer=True %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<!-- #include file="dbConn.asp" -->
<!-- #include file="adovbs.inc" -->

<%
Dim adoDB, adoRS, strSQL, iRecordCount, pid, projectname
'id variable passed from previous page set to new variable pid
pid = Request.QueryString("id")
' Create the Database Object, and connect to it
Set adoDB = Server.CreateObject("ADODB.Connection")
adoDB.Open strConnection
' Create the Recordset Object
Set adoRS = Server.CreateObject("ADODB.RecordSet")
' Create a SQL string, to open the recordset
strSQL = "SELECT ShopLog.id as shoplogid, ShopLog.fk_projectid,
ShopLog.sd_title, ShopLog.receivedfrom, Projects.id as projectid,
Projects.projectname FROM Projects INNER JOIN ShopLog ON Projects.id =
ShopLog.fk_projectid WHERE Projects.id=" & pid & ""


adoRS.Open strSQL, adoDB, adOpenDynamic, adLockReadOnly, adCmdText
%>

<head>
<title>Shop Log Depot</title>
</head>

<body>
Click to expand...

Change this:
<td colspan=4><% Response.Write adoRS("Projects.projectname") %></td>
Click to expand...

to this:
<td colspan=4><% Response.Write adoRS("projectname") %></td>

Morris
 
M

Morris

Auddog said:
Thanks again for the help.

First here is the error that I get:

a.. Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name
or ordinal.
/tcep/shoplog_list.asp, line 47



Database Layout

Table 1 - Projects

id
projectid
projectname

Table 2 - ShopLog

id
fk_projectid
sd_title
receivedfrom


<%@ language="vbscript" %>
<% Option Explicit %>
<% Response.Buffer=True %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<!-- #include file="dbConn.asp" -->
<!-- #include file="adovbs.inc" -->

<%
Dim adoDB, adoRS, strSQL, iRecordCount, pid, projectname
'id variable passed from previous page set to new variable pid
pid = Request.QueryString("id")
' Create the Database Object, and connect to it
Set adoDB = Server.CreateObject("ADODB.Connection")
adoDB.Open strConnection
' Create the Recordset Object
Set adoRS = Server.CreateObject("ADODB.RecordSet")
' Create a SQL string, to open the recordset
strSQL = "SELECT ShopLog.id as shoplogid, ShopLog.fk_projectid,
ShopLog.sd_title, ShopLog.receivedfrom, Projects.id as projectid,
Projects.projectname FROM Projects INNER JOIN ShopLog ON Projects.id =
ShopLog.fk_projectid WHERE Projects.id=" & pid & ""


adoRS.Open strSQL, adoDB, adOpenDynamic, adLockReadOnly, adCmdText
%>

<head>
Click to expand...

and this:
<% Response.Write adoRS("ShopLog.sd_title") %>
to this:

<% Response.Write adoRS("sd_title") %>

Damn. Should have spotted that before posting inital response.

Morris
 
M

Morris

Auddog said:
Thanks again for the help.

First here is the error that I get:

a.. Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name
or ordinal.
/tcep/shoplog_list.asp, line 47



Database Layout

Table 1 - Projects

id
projectid
projectname

Table 2 - ShopLog

id
fk_projectid
sd_title
receivedfrom


<%@ language="vbscript" %>
<% Option Explicit %>
<% Response.Buffer=True %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<!-- #include file="dbConn.asp" -->
<!-- #include file="adovbs.inc" -->

<%
Dim adoDB, adoRS, strSQL, iRecordCount, pid, projectname
'id variable passed from previous page set to new variable pid
pid = Request.QueryString("id")
' Create the Database Object, and connect to it
Set adoDB = Server.CreateObject("ADODB.Connection")
adoDB.Open strConnection
' Create the Recordset Object
Set adoRS = Server.CreateObject("ADODB.RecordSet")
' Create a SQL string, to open the recordset
strSQL = "SELECT ShopLog.id as shoplogid, ShopLog.fk_projectid,
ShopLog.sd_title, ShopLog.receivedfrom, Projects.id as projectid,
Projects.projectname FROM Projects INNER JOIN ShopLog ON Projects.id =
ShopLog.fk_projectid WHERE Projects.id=" & pid & ""


adoRS.Open strSQL, adoDB, adOpenDynamic, adLockReadOnly, adCmdText
%>

<head>
<title>Shop Log Depot</title>
</head>

<body>
<div align="center">
<table border=1>
<tr>
<td colspan=4><% Response.Write adoRS("Projects.projectname") %></td>
</tr>
<tr>
<td>Shop Log Title</td>
Click to expand...

And one final one:
<a href="edit_project.asp?id=<% = adoRS("pid") %>">
Click to expand...

should read:
<a href="edit_project.asp?id=<% = adoRS("projectid") %>">

Morris
 
A

Auddog

OK - that works and I think that I found the error. Here is the code that I
have:

<%
DO WHILE NOT adoRS.EOF
%>
<td><a href="edit_project.asp?id=<% = adoRS("id") %>"><% Response.Write
adoRS("sd_title") %></a></td>
<td><% Response.Write adoRS("receivedfrom") %>&nbsp;</td>
<td><% 'Response.Write adoRS("checkindate") %>&nbsp;</td>
<td><% 'Response.Write adoRS("returned") %>&nbsp;</td>
</tr>
</tr>
<% adoRS.MoveNext
Loop

the part that I removed was the <a href="edit_project.asp?id=<% =
adoRS("id") %>">, I guess that I will have to rework that line. Thanks
everyone for all your help.

A
 
M

Mark Schupp

you'll need to change it to either:

<a href="edit_project.asp?id=<% = adoRS("projectid") %>">

or

<a href="edit_project.asp?id=<% = adoRS("shoplogid") %>">

depending on which "id" column you wanted.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sort by number of characters 1
Script to send email not working 1
Loop through record in datareader in aspx for textbox other item 1
newbie question 4
Only one table shows up with the information 2
multiple query on one page 2
Checking dynamically populated data using ajax with user entered value 5
I need advice re mysqli dropdown 0

Members online

No members online now.
Total: 21 (members: 0, guests: 21)
Robots: 198

Forum statistics

Threads
473,774
Messages
2,569,596
Members
45,139
Latest member
JamaalCald

Latest Threads

Top