|
 |
asp_databases thread: Still struggling with my movie database: sql query
Message #1 by "Frode Strømme" <fstroemm@o...> on Thu, 26 Apr 2001 20:29:31
|
|
Working further on my access 2000 movie database:
I'm now displaying title after a search. I want to display the genre too
in another table colum (no hyperlink on this one) after the movie title
name.
I'm currently taking information from table tblmovies.
I suspect I have to do an INNER JOIN to take the genre from my table
tblgenres
How do I write the SQL query and later the Response.Write to do this?
My code:
<%
Dim searchStr, MyConn, RS, i
searchStr = Replace(Request.Form("strSearch"), "'", "''")
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\datastores\moviebase.mdb"
SQL = "SELECT * FROM tblmovies WHERE tblmovies.Title"
SQL = SQL & "LIKE '%"&searchStr&"%' "
Set RS = MyConn.Execute(SQL)
If searchStr <> "" Then
If RS.BOF AND RS.EOF Then
Response.Write "<center>No Records matches your search.</center>"
Else
Response.Write "</tr>"
While Not RS.EOF
Response.Write "<tr><td>"
Response.Write "<a href='display.asp?ID=" & _
Server.URLEncode(RS("MovieID")) & "'>" & Server.HTMLEncode(RS("Title"))
& "</a><br>"
Response.Write "</td></tr>"
RS.MoveNext
WEND
Response.Write "</table>"
End If
Else
Response.Write "<center>Please fill in form.</center>"
End IF
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>
Message #2 by cmensah@m... on Sun, 29 Apr 2001 19:52:50
|
|
Hey buddy,
Here's what an inner join looks like:
SELECT * FROM tblmovies INNER JOIN tblgenres ON tblmovies.id =
tblgenres.id WHERE LIKE '%" & searchStr &"%'"
And When you want to display your data, simply use
RS("myGenre") or whatever you want to display.
Happy Coding
Caesar Mensah
> Working further on my access 2000 movie database:
> I'm now displaying title after a search. I want to display the genre too
> in another table colum (no hyperlink on this one) after the movie title
> name.
> I'm currently taking information from table tblmovies.
> I suspect I have to do an INNER JOIN to take the genre from my table
> tblgenres
>
> How do I write the SQL query and later the Response.Write to do this?
>
> My code:
>
>
> <%
> Dim searchStr, MyConn, RS, i
>
> searchStr = Replace(Request.Form("strSearch"), "'", "''")
>
> Set MyConn=Server.CreateObject("ADODB.Connection")
>
> MyConn.Open "Driver={Microsoft Access Driver (*.mdb)};
> DBQ=C:\datastores\moviebase.mdb"
>
> SQL = "SELECT * FROM tblmovies WHERE tblmovies.Title"
> SQL = SQL & "LIKE '%"&searchStr&"%' "
>
> Set RS = MyConn.Execute(SQL)
>
> If searchStr <> "" Then
> If RS.BOF AND RS.EOF Then
> Response.Write "<center>No Records matches your search.</center>"
> Else
> Response.Write "</tr>"
>
> While Not RS.EOF
> Response.Write "<tr><td>"
> Response.Write "<a href='display.asp?ID=" & _
> Server.URLEncode(RS("MovieID")) & "'>" & Server.HTMLEncode(RS("Title"))
> & "</a><br>"
> Response.Write "</td></tr>"
> RS.MoveNext
> WEND
>
> Response.Write "</table>"
> End If
>
> Else
> Response.Write "<center>Please fill in form.</center>"
>
> End IF
>
> RS.Close
> MyConn.Close
> Set RS = Nothing
> Set MyConn = Nothing
> %>
Message #3 by "Frode Strømme" <fstroemm@o...> on Mon, 30 Apr 2001 13:15:14
|
|
Tried two proposals that didn't work.
First one gave me:
Error Type:
Microsoft VBScript compilation (0x800A03FD)
Expected 'Case'
<%
Dim searchStr, MyConn, RS, i
searchStr = Replace(Request.Form("strSearch"), "'", "''")
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\datastores\moviebase.mdb"
' Did not work #1
'SELECT * FROM tblmovies INNER JOIN tblgenres ON tblmovies.MovieID =
tblgenres.MovieID WHERE LIKE '%" & searchStr &"%'"
' Did not work #2
'SQL = SELECT tblmovies.Title, tblgenres.Genre from tblmovies, tblgenres
WHERE tblmovies.MovieID = tblgenres.MovieID
'SQL = SQL & "LIKE '%"&searchStr&"%' "
' Original with just one of the tables. Worked.
SQL = "SELECT * FROM tblmovies WHERE tblmovies.Title"
SQL = SQL & "LIKE '%"&searchStr&"%' "
Set RS = MyConn.Execute(SQL)
If searchStr <> "" Then
If RS.BOF AND RS.EOF Then
Response.Write "<center>No Records matches your search.</center>"
Else
Response.Write "</tr>"
While Not RS.EOF
Response.Write "<tr><td>"
Response.Write "<a href='display.asp?ID=" & _
Server.URLEncode(RS("MovieID")) & "'>" & Server.HTMLEncode(RS("Title"))
& "</a><br>"
Response.Write "</td></tr>"
Response.Write RS("Genre")
Response.Write "</td></tr>"
RS.MoveNext
WEND
Response.Write "</table>"
End If
Else
Response.Write "<center>Please fill in form.</center>"
End IF
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>
Message #4 by "Ken Schaefer" <ken@a...> on Mon, 30 Apr 2001 22:57:17 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Tried two proposals that didn't work.
: First one gave me:
: Error Type:
: Microsoft VBScript compilation (0x800A03FD)
: Expected 'Case'
<snip>
: ' Did not work #1
: 'SELECT * FROM tblmovies INNER JOIN tblgenres ON tblmovies.MovieID
: tblgenres.MovieID WHERE LIKE '%" & searchStr &"%'"
:
: ' Did not work #2
: 'SQL = SELECT tblmovies.Title, tblgenres.Genre from tblmovies, tblgenres
: WHERE tblmovies.MovieID = tblgenres.MovieID
: 'SQL = SQL & "LIKE '%"&searchStr&"%' "
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
It does not work because you do not have an opening " when building your
SELECT string:
strSQL = "SELECT * FROM tblmovies..."
not
SELECT * FROM tblmovies...
Cheers
Ken
Message #5 by "Daniel O'Dorisio" <dodorisio@h...> on Mon, 30 Apr 2001 08:42:23 -0400
|
|
you need quotes. so for example:
SQL = SELECT tblmovies.Title, tblgenres.Genre from tblmovies, tblgenres
WHERE tblmovies.MovieID = tblgenres.MovieID
'SQL = SQL & "LIKE '%"&searchStr&"%' "
should be
SQL = "SELECT tblmovies.title, tblmovies.genre FROM tblmovies, tblgenres
WHERE tblmovies.movieID = tblgenres.movieid"
sql = sql & " LIKE '%" & SearchStr & "%' "
i *think* that will work.. i didnt test it.
hth
Daniel
-----Original Message-----
From: Frode Strømme [mailto:fstroemm@o...]
Sent: Monday, April 30, 2001 1:15 PM
To: ASP Databases
Subject: [asp_databases] Re: Still struggling with my movie database:
sql query
Tried two proposals that didn't work.
First one gave me:
Error Type:
Microsoft VBScript compilation (0x800A03FD)
Expected 'Case'
<%
Dim searchStr, MyConn, RS, i
searchStr = Replace(Request.Form("strSearch"), "'", "''")
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\datastores\moviebase.mdb"
' Did not work #1
'SELECT * FROM tblmovies INNER JOIN tblgenres ON tblmovies.MovieID
tblgenres.MovieID WHERE LIKE '%" & searchStr &"%'"
' Did not work #2
'SQL = SELECT tblmovies.Title, tblgenres.Genre from tblmovies, tblgenres
WHERE tblmovies.MovieID = tblgenres.MovieID
'SQL = SQL & "LIKE '%"&searchStr&"%' "
' Original with just one of the tables. Worked.
SQL = "SELECT * FROM tblmovies WHERE tblmovies.Title"
SQL = SQL & "LIKE '%"&searchStr&"%' "
Set RS = MyConn.Execute(SQL)
If searchStr <> "" Then
If RS.BOF AND RS.EOF Then
Response.Write "<center>No Records matches your search.</center>"
Else
Response.Write "</tr>"
While Not RS.EOF
Response.Write "<tr><td>"
Response.Write "<a href='display.asp?ID=" & _
Server.URLEncode(RS("MovieID")) & "'>" & Server.HTMLEncode(RS("Title"))
& "</a><br>"
Response.Write "</td></tr>"
Response.Write RS("Genre")
Response.Write "</td></tr>"
RS.MoveNext
WEND
Response.Write "</table>"
End If
Else
Response.Write "<center>Please fill in form.</center>"
End IF
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>
Message #6 by "Frode Strømme" <fstroemm@o...> on Mon, 30 Apr 2001 16:55:23
|
|
This one works, but dont give me any matches when searching for a title:
SQL = "SELECT khitfilm.Tittel, khitpris.Pris FROM khitfilm, khitpris WHERE
khitfilm.FilmID = khitpris.FilmID"
SQL = SQL & " LIKE '%" & SearchStr & "%' "
This is the original that gives me matches, without the extra table:
SQL = "SELECT * FROM khitfilm WHERE khitfilm.Tittel "
SQL = SQL & "LIKE '%"&searchStr&"%' "
Need to make the new one an inner join somehow?
(field names with my original norwegian. No need to translate I guess.)
Message #7 by "Charles Feduke" <webmaster@r...> on Mon, 30 Apr 2001 15:12:02 -0400
|
|
What field would you like to INNER JOIN on? Keep in mind an INNER JOIN
requires that all rows on both sides of the join *MUST* match up... if in doubt
(i.e. there's a chance one table will have data that does not correspond to the
joined table), use an OUTER JOIN (LEFT or RIGHT).
Here is how I would write your query:
SQL = "SELECT khitfilm.Tittel, khitpris.Pris FROM khitfilm " & _
"INNER JOIN khitpris ON " & _
"khitfilm.FilmID = khitpris.FilmID " & _
"WHERE khitfilm.Tittel LIKE '%" & SearchStr & "%'"
- Chuck
----- Original Message -----
From: "Frode Strømme" <fstroemm@o...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, April 30, 2001 4:55 PM
Subject: [asp_databases] Re: Still struggling with my movie database: sql query
> This one works, but dont give me any matches when searching for a title:
>
> SQL = "SELECT khitfilm.Tittel, khitpris.Pris FROM khitfilm, khitpris WHERE
> khitfilm.FilmID = khitpris.FilmID"
> SQL = SQL & " LIKE '%" & SearchStr & "%' "
>
>
> This is the original that gives me matches, without the extra table:
>
>
> SQL = "SELECT * FROM khitfilm WHERE khitfilm.Tittel "
> SQL = SQL & "LIKE '%"&searchStr&"%' "
>
>
> Need to make the new one an inner join somehow?
>
>
> (field names with my original norwegian. No need to translate I guess.)
> ---
> SoftArtisans helps developers build robust, scalable Web applications!
> Excel Web reports, charts: http://www.softartisans.com/excelwriter.html
> File uploads: http://www.softartisans.com/saf.html
> Transactional file management: http://www.softartisans.com/saf1.html
> Scalability: http://www.softartisans.com/saxsession.html
> ASPstudio value pack: http://www.softartisans.com/aspstudiosuite.html
|
|
|
View Cart
