Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedure error. Please help!!!

Status
Not open for further replies.
d1004

d1004

Programmer
May 9, 2002
78
US
I don’t have any IT people where I work, and it is frustrating to figure these codes out. So thank you for helping me.
Here is the situation: I used 3 tables for qryRep, which is the stored procedure for the following ASP page. However, when I run it, I generate the following error:

"[Microsoft][ODBC Microsoft Access Driver] The specified field '[RepLeaderID]' could refer to more than one table listed in the FROM clause of your SQL statement. "

Can somebody tell me why I have this error, and what can I do.
<%
Option Explicit
Dim strConnect
%>
<!-- METADATA TYPE = &quot;typelib&quot; FILE = &quot;C:\Program Files\Common Files\System\ado\msado15.dll&quot; -->
<html>
<head>
<title> Query the database </title>
</head>
<body>

<%
Dim objRS, objComm, objParam, strRepLeader
Dim adCmdStoredProc
adCmdStoredProc = 4
Set objComm = Server.CreateObject(&quot;ADODB.Command&quot;)

strConnect = &quot;Driver={Microsoft Access Driver (*.mdb)}; DBQ=&quot; & Server.MapPath(&quot;/db/report.mdb&quot;)&&quot;;&quot; &_
&quot;Persist Security Info = False&quot;

objComm.ActiveConnection = strConnect
objComm.CommandText = &quot;qryRep&quot;
objComm.CommandType = adCmdStoredProc

Set objParam = objComm.CreateParameter(&quot;tblRepLeader.RepLeaderID&quot;, adVarChar, adParamInput, 10)
objComm.Parameters.Append objParam

strRepLeader = &quot;VLN&quot;
objComm.Parameters(&quot;tblRepLeader.RepLeaderID&quot;) = strRepLeader

Set objRS = objComm.Execute

Set objComm = Nothing
Set objParam = Nothing

objRS.MoveFirst
Response.write objRS(&quot;RepLeaderID&quot;) & &quot;, and Category is &quot; & objRS(&quot;Category&quot;) & &quot;<BR>&quot;

objRS.Close
Set objRS = Nothing
%>
</BODY>
</HTML>
 
Sort by date Sort by votes
The error is most likely coming from inside your stored procedure, not your ASP code. In your select statement in the stored procedure, you are probably asking for a column that exists in more than one table and just not telling which table you want to get the value from:

Select ID from tblThis, tblThat WHERE ...

will produce the same type of error if both tables have a column named &quot;ID&quot;

To fix it, just use aliases for your tables:

Select a.ID FROM tblThis a, tblThat b WHERE ...

Post your Select statement and table definitions if you need more help.
 
Upvote 0 Downvote
As a good pratice I'd suggest you to avoid using ID as a field name, try always CustID or SomethingID.
ID, for most database engines is a reserved word and can raise a lot of errors hard to find...
This is not the case right here, Juanita's answer shall fix your problem.

 
Upvote 0 Downvote
I’m sorry, but I’m a little bit confuse on this…my stored procedure is a query that has already been done in Access database. What I am trying to do is pass a criteria from a form to that query, and then return the result back in a recordset. So by reading your posting, I am to assume that you want me to use SQL statement to generate a query not yet existed in my Access database, and use adCmdText instead of adCmdStoredProc? And thank you for the tip about the ID and thank you for your help.
 
Upvote 0 Downvote
when doing simple inserts or selects with stored procedures, i almost always use the Execute method of the connection object and don't use the command object at all. in my opinion, it makes your code alot easier to read, write, and debug, and i've read many opinions as well as tested it myself that the performance difference is negligible. so in this case, why do in 15 lines of code what you can do in 3? (forgive my exagerrating here)

see for a really good overview as well as advice on using the connection object versus the command object.

as far as the problem you are describing goes, JuanitaC's post sounds like the explanation. in your case, you describe three tables in &quot;qryRep&quot; -- and i am guessing two or more of these tables have fields named &quot;RepLeaderID&quot; in them. this is what causes the ambiguity that causes your error.

in your query, you simply need to prefix the &quot;RepLeaderID&quot; with its table name. this will eliminate the ambiguity. for example, change RepLeaderID to myTableOne.RepLeaderID or to myTableTwo.RepLeaderID, etc., depending on the actual underlying table.

good luck!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Krus1972
  • Locked
  • Question
msxml3.dll error '80072f7d' - An error occurred in the secure Channel support
Replies
7
Views
355
Krus1972
Krus1972
leifoet
  • Locked
  • Question
Using date picker gives an error message, only at application start
Replies
1
Views
203
MarkSweetland
MarkSweetland
chikwendu
  • Locked
  • Question
OraOLEDB error '80040e4b'
Replies
1
Views
352
xwb
xwb
Sai259
  • Locked
  • Question
Hello, Could you please provide any
Replies
2
Views
102
ChrisHirst
ChrisHirst
RonMc
  • Locked
  • Question
classic asp - getting dropdown selection and reposting - help
Replies
1
Views
175
ChrisHirst
ChrisHirst

Part and Inventory Search

Sponsor

Back
Top