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 Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Error in my SQL 1

Status
Not open for further replies.
sera

sera

Technical User
Jun 29, 2000
360
US
Hello All,
I am new to ASP. I have an SQL statement that works if I only want to get information from one table, but the second that I try to get information from two tables using a join...I get an error. I know that the SQL statement is correct. I am thinking that my problem may be some parameter in the connection. The line that the error is at
(supposedly) is ...

rs.Open sql, conn, 0, 1

Is there something that I am not doing that is preventing me from querying two tables?

Thanks,
Sera


 
Sort by date Sort by votes
It will be easier to see what the problem could be if you post more of the code, including the SQL statement that you are trying to run.
 
Upvote 0 Downvote
Okay, I am not sure it is the code because it runs with just one table in the query but what do I know....
here you go...


<html>
<title>Testing searching ak2000</title>
<%'Sera White learning asp%>

<body>
<%
Dim keyword

keyword = Request(&quot;SelectKeyword&quot;)
if keyword = &quot;&quot; then
response.write &quot;An keyword is required before query can complete.&quot;
else

Session.timeout = 1

Set conn = Server.CreateObject(&quot;ADODB.Connection&quot;)
conn.ConnectionString = &quot;DSN=alaska;mode=7&quot;
conn.open

dim sess_name,timestr
timestr = time
timestr = mid(timestr, 1, InStr(1,timestr, &quot; &quot;))

sess_name = timestr & &quot;conn&quot;
Set Session(sess_name) = conn

dim sql

sql = &quot;SELECT Bibliography.TITLE, Author.name,
Bibliography.Paper_location &quot;
sql = sql + &quot;FROM Bibliography, Author WHERE Author.authorID =
Bibliography.authorID&quot;
sql = sql + &quot; AND Bibliography.KEYWORDS Like&quot; + Chr(34)+ keyword
+ &quot;*&quot; + Chr(34)

response.write sql + &quot;<p>&quot;
dim rs
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot;)

rs.Open sql, conn, 0, 1
' rs.Open
if rs.BOF then
Response.Write &quot;No records match the keyword.&quot;
end if

response.write &quot;<table border = 1 bgcolor='#CFDBE3'>&quot;
response.write &quot;<font size=+2> Keyword: &quot;+keyword+&quot;
Information</font><p><tr>&quot;

Response.Write &quot;<tr><td>Title</td></tr>&quot;
response.write &quot;<tr><td>Title</td><td><a href = Name></a></td><td>&quot;
response.write &quot;Paper Location</td></tr>&quot;

Do Until rs.eof

For Each Item in rs.Fields

response.write &quot;<td>&quot;

response.write Item

response.write &quot;</td>&quot;


Next
rs.MoveNext



response.write &quot;</tr>&quot;
Loop

response.write &quot;</table>&quot;
rs.close
set rs=nothing
conn.close
set conn=nothing
session.abandon

end if

%>

</body>
</html>


Thanks,
Sera



 
Upvote 0 Downvote
When you response.write your SQL string, does it run in your database directly?

When you were using only one table, did you still add the variable &quot;keyword&quot; into your SQL statement? I am just wondering if &quot;keyword&quot; might be a reserved word... if that is the case, change the name of the variable to varKeyword or something and see if that works.
 
Upvote 0 Downvote
You know...I just tested it with something other than keyword and I am still getting the same problem. I do think that it is hanging up at the end of the SQL statement...I am going to try a straightforward query across two tables and see if it will do them!

....

It is an error in the SQL somewhere...it works across two tables
sql = &quot;SELECT Bibliography.TITLE, Author.name,
Bibliography.Paper_location &quot;
sql = sql + &quot;FROM Bibliography, Author WHERE
Author.authorID = Bibliography.authorID&quot;

sql = sql + &quot; AND Bibliography.KEYWORDS Like&quot; + Chr(34)+
keyword + &quot;*&quot; + Chr(34)


I am pretty sure the problem lies in this statement here. I changed the variable named keyword and it did nothing.
perplexing eh?

Thank You,
Sera
 
Upvote 0 Downvote
This is the error that I am getting...
It is a common query error...I am doing something wrong, but that is blaringly obvious. :)

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1

Sera
 
Upvote 0 Downvote
I think I found it, there was no space between your LIKE and your first single quote.

See if this works:

sql = &quot;SELECT B.TITLE, A.name, B.Paper_location&quot;
sql = sql & &quot; FROM Bibliography B, Author A&quot;
sql = sql & &quot; WHERE A.authorID = B.authorID&quot;
sql = sql & &quot; AND B.KEYWORDS Like '&quot; & keyword & &quot;*'&quot;

 
Upvote 0 Downvote
Sweet! That works...If I could give you a million stars I would...but I can only give you one. Those stupid quotation marks. ARRRRRGH!

Thanks so much JuanitaC
Sera
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Code does not run in Microsoft Edge - it does in Google Chrome
Replies
3
Views
748
leifoet
leifoet
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
403
leifoet
leifoet
Krus1972
  • Locked
  • Question
msxml3.dll error '80072f7d' - An error occurred in the secure Channel support
Replies
7
Views
661
Krus1972
Krus1972
leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
568
leifoet
leifoet
Swi
  • Locked
  • Question
PDF Interrogation
Replies
0
Views
232
Swi
Swi

Part and Inventory Search

Sponsor

Back
Top