some error i cant figure

noamsmadja · #1 (**permalink**) 01-14-08, 07:31

i'll just put the error code, and my code:

Error Code

Code:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
/Results.asp, line 63

My Code

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="Connections/KerenDB.asp" -->
<%

'=================================
'insert queryStrings into variables
'==================================

Dim Search__textSearch
Search__textSearch = "%"
If (Request.QueryString("txtSearch") = "") Then ' In case user entered no text in the search box
    Search__textSearch = "none"
 else 
   Search__textSearch = Request.QueryString("txtSearch")  
End If

Dim Search__topicSearch
Search__topicSearch = "%"
If (Request.QueryString("Topic") <> "") Then 'from the topic dropdown list
  Search__topicSearch = Request.QueryString("Topic")
End If

Dim Search__langSearch
Search__langSearch = "%"
If (Request.QueryString("Language") <> "") Then 'from the language dropdown list
  Search__langSearch = Request.QueryString("Language")
End If

'==============================
'Create a recordset extracting appropriate books for the search
'==============================

Dim Search
Dim Search_numRows
Dim Temp_SearchSource
Dim DoesWhereExist
DoesWhereExist = 0
Set Search = Server.CreateObject("ADODB.Recordset")
Search.ActiveConnection = MM_KerenDB_STRING
Search.Source = "SELECT * FROM Book_Full_Properties"
 
'=======================================================================
' if user did not enter text in the search box it will return all books.
'=======================================================================
   if (Request.QueryString("txtSearch") <> "none") Then
     DoesWhereExist = 1 
     Search.Source = Search.Source + "WHERE (Bknm_Hebrew LIKE '%" + Replace(Search__textSearch, "'", "") + "%' OR Author_Hebrew LIKE '%" +     Replace(Search__textSearch, "'", "") + "%')" 
   End If

'=======================================================================
' excluding books that are from other topics
'--------------------------------------------
' if only this one is set, will return all books in certain Topic
'=======================================================================

   If (Request.QueryString("Topic") <> "18") Then 
	 If (DoesWhereExist = 0) Then
	  Search.Source = Search.Source + " WHERE " 
	 else
	  Search.Source = Search.Source + " AND " 
	  DoesWhereExist = 1
	 End If
	Search.Source = Search.Source + " (Topic=" + Search__topicSearch + ")"      
' LINE 63 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   End If

'=======================================================================
' excluding books that are from other languages  
'-------------------------------------------------
' if only this one is set, will return all books in certain language
'=======================================================================
   
   If (Request.QueryString("Language") <> "3") Then
	 If (DoesWhereExist = 0) Then
	  Search.Source = Search.Source + " WHERE " 
	 else
	  Search.Source = Search.Source + " AND " 
	 End If
	 Search.Source = Search.Source + " (Lang=" + Search__langSearch + ")"
   End If

'=======================================================================
' order
'=======================================================================

Search.Source = Search.Source + " ORDER BY Bknm_Hebrew ASC"

Search.CursorType = 0
Search.CursorLocation = 2
Search.LockType = 1
Search.Open()

Search_numRows = 0
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 100
Repeat1__index = 0
Search_numRows = Search_numRows + Repeat1__numRows

'=====================================
' Declaring Image (for the book) related variables
'=====================================

Dim Image_Name
Dim Folder

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Keren Ahvah Meshihit2</title>
<link href="css.css" rel="stylesheet" type="text/css" />
</head>
<body>
<%=Search.Source%>
<table border="1" cellpadding="1" cellspacing="1">
  <% While ((Repeat1__numRows <> 0) AND (NOT Search.EOF))
' image is the english book name w/o spaces, adding .jpg at the end
     Image_Name = Search.Fields.Item("Bknm_English").Value
	 Image_Name = replace(Image_Name, " ", "")
' image is is stored according to its language
	 Folder = Search.Fields.Item("Lang_English").Value 
  %>
      <tr>
      <td rowspan="3"><img size="30%" src="images/<%=Folder%>/<%=Image_Name%>.jpg"></td>
      <td colspan="3">
        <div class="bkttlheb"><%=(Search.Fields.Item("Bknm_Hebrew").Value)%></div>
        <div class="bkatheb"><%=(Search.Fields.Item("Author_Hebrew").Value)%></div></td>
    </tr>
    <tr>
      <td colspan="3">
        <div class="bkttleng"><%=(Search.Fields.Item("Bknm_English").Value)%></div>
        <div class="bkatleng"><%=(Search.Fields.Item("Author_English").Value)%></div></td>
    </tr>
    <tr>
      <td colspan="3">
        <div class="bknote"><%=(Search.Fields.Item("Note").Value)%></div></td>
    </tr>
    <tr>
      <td>
	    <div class="bktpkheb"><%=(Search.Fields.Item("Topic_Hebrew").Value)%></div>
		<div class="bktpkeng"><%=(Search.Fields.Item("Topic_English").Value)%></div>
	  </td>
      <td>
        <div><%=(Search.Fields.Item("Pages").Value)%></div>
        <div><%=(Search.Fields.Item("Size").Value)%></div>
        <div><%=(Search.Fields.Item("Cvrtyp_hebrew").Value)%></div>
      </td>
      <td>
        <div><%=(Search.Fields.Item("ISBN").Value)%></div>
        <div><%=(Search.Fields.Item("Lang_Hebrew").Value)%></div></td>
      <td>
        <div><%=(Search.Fields.Item("PUSD").Value)%></div></td>
    </tr>
 
    <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Search.MoveNext()
Wend
%>
</table>
<% If Repeat1__index = 0 Then %> <!-- no books found -->
 לא נמצאו תוצאות עבור החיפוש שביצעת
<% End If %>
</body>
</html>
<%
Search.Close()
Set Search = Nothing
%>

gvee · #2 (**permalink**) 01-14-08, 07:41

Can you highlight line 63 please?
If I paste your code into notepad, line 63 is blank

gvee · #3 (**permalink**) 01-14-08, 07:42

Oh and before we go ANY further, remove the

Code:

SELECT *

from EVERY one of your statements. List the columns expresssly.

Trust me

noamsmadja · #4 (**permalink**) 01-14-08, 07:46

the script worked until i added those nested IFs.

y should i remove * ?? i have almost 20 fields...

Marvels · #5 (**permalink**) 01-14-08, 07:58

Quote:

Originally Posted by noamsmadja

Code:

'=======================================================================
' if user did not enter text in the search box it will return all books.
'=======================================================================
   if (Request.QueryString("txtSearch") <> "none") Then
     DoesWhereExist = 1 
     Search.Source = Search.Source + "WHERE (Bknm_Hebrew LIKE '%" + Replace(Search__textSearch, "'", "") + "%' OR Author_Hebrew LIKE '%" +     Replace(Search__textSearch, "'", "") + "%')" 
   End If

Think you didn't put in a search, that time, did ya?
Search.Source = Search.Source + "WHERE (Bknm_Hebrew LIKE '%" +

misses a space before the WHERE

noamsmadja · #6 (**permalink**) 01-14-08, 08:31

Thanks mate, it fixed it... that was so stupid of my part...

i think this forum should add a "problem solved" button. and when u press it, it gives the one who solved it points. being able to spend thease points on some stuff. like buying an FTP or etc...

u ppl are doing a great job here.

gvee · #7 (**permalink**) 01-14-08, 08:38

noamsmadja,

Imagine you had a table of people which had the columns firstname, lastname, dateOfBirth, address, postcode etc...

If you do a SELECT * you return ALL of the information about that person, when on your page you may only want to display the persons name.

By using the * you are returning redundant data, which is using up resources and memory (not good!).

"But what if I do want all the rows of a table" I hear you cry!
Well, imagine you change your table structure; add or remove a column. Heck, even if you rename it...

What happens then?
Well, depending on your coding it could either break something (ASP errors), show the wrong information in the wrong place or you could just be caught in that loop of returning redundant data.

Hope this helps (and presuades you to correct your mistakes)!
If you have any questions, just post them back here

noamsmadja · #8 (**permalink**) 01-14-08, 08:52

ok, i understand. you have a point.

but this "table" i am looking into is already an SQL query (created in access). so all of its raws are needed. i am constantly adding and removing raws as the script grows.

wt do u think about making those queries in SQL... having less code in ASP? is it good or bad?

p.s
a week ago i had no idea how ASP works, now i am starting to get the feel of it... as u can see i still do silly mistakes, hopefully this will stop with time.

btw,
this forum has an IRC channel?

gvee · #9 (**permalink**) 01-14-08, 09:25

I'm not aware of any IRC channel for this forum, sorry!
If you think it would be a good idea to add one then why not pop a post in the suggestions topic of the forum?

Generally you would use stored procedures to return data to an application, but as you mention that you use Access, I guess that would be the better way of doing things (the less "hard coded" you can get, the better!).

I still argue it'd be better to amend your mistakes now (with regards to the *) before you get much further; it's easier to do it right now then it is to do it later