search bar problem?

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • j0rdanf1
    New Member
    • Oct 2006
    • 35
    #1

    search bar problem?

    hey, I have just ventured into the fun world of creating a search bar, so far i have a page which you type in a name of a band or album you like, the next page is meant to connect to the sql database and find matches that are LIKE the text entered. However I get an error instead.

    ERROR code
    Code:
    Microsoft OLE DB Provider for ODBC Drivers error '80004005' 

[Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended 

page/basic search.asp, line 16
    here is the results page

    Code:
    <!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=iso-8859-1" />
<%@LANGUAGE="VBSCRIPT"%>

<%
Dim strSQL, objConn, objRs 

strbasicsearch = Request.QueryString ("basicsearch")


strsql = "select * from band and album where basicsearch like '"& strbasicsearch & "%'"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "dsn=Oracle;uid=username;pwd=password"
Set objRs=objConn.execute(strSQL)
%>

<HTML>
<HEAD>
<TITLE> band Results Page</TITLE>
<HEAD>
<BODY>
<TABLE>
<TR>
<TD>band name</TD>
<TD>location </TD>
<TD>genre</TD>
<td>album</td>
</TR>

<%
Do While Not (objRs.EOF)
%>
<TR>
<TD><%=objRs("band_name")%></TD>
<TD><%=objRs("location")%></TD>
<TD><%=objRs("genre")%></TD>
<TD><%=objRs("album_name")%></TD>

</TR>

<%
objRs.MoveNext
Loop
objRs.Close
Set objRs=Nothing
objConn.Close
Set objConn=Nothing
%>
</TABLE>

</BODY>
</HTML>
    I have a feeling the strsql string is incorrect but im unsure
    Tags: None
    • CroCrew
      Recognized Expert Contributor
      • Jan 2008
      • 564
      #2
      Microsoft SQL Server or Oracle?

        Comment

        • j0rdanf1
          New Member
          • Oct 2006
          • 35
          #3
          Originally posted by CroCrew
          Microsoft SQL Server or Oracle?
          im using oracle mate

            Comment

            • CroCrew
              Recognized Expert Contributor
              • Jan 2008
              • 564
              #4
              Hello j0rdanf1,

              All you need to do is change the Database Provider to point to your Oracle DSN and all should work.

              In the example below the relationship between the two tables is the BandID.

              Example:

              [CODE=asp]
              <%
              LacalBandName = replace(Request .QueryString("B andName"),"'"," ''")
              Set Conn = Server.CreateOb ject("ADODB.Con nection")
              Conn.Open("dsn= xxxxx;uid=xxxxx ;pwd=xxxxx;")
              SQL = "SELECT a.*, b.* FROM band a Join album b ON (a.BandID = b.BandID) where a.BandName LIKE '%" & LacalBandName & "%'"
              Set myRS=Server.Cre ateObject("ADOD B.Recordset")
              myRS.Open SQL, Conn

              Response.write( myRS("FirstName ").value)
              Do Until (myRS.EOF)
              Response.write( myRS("AlbumName ").value)
              myRS.MoveNext
              LOOP
              %>

              [/CODE]


              Hope that helps~

                Comment

                Previous template Next
                Working...