Member_2_6492660_1
asked on
ASP SQL query Help
My strSQL1 code below had = for
(dbo.Artists.artistname = '"& searchWord&"') OR (dbo.Recordings.RecordingT itle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"
changed to LIKE
(dbo.Artists.artistname Like '"& searchWord&"') OR (dbo.Recordings.RecordingT itle Like '"& searchWord&"') OR (dbo.Tracks.TrackTitle Like '"& searchWord&"')"
Now I can do searches with having the exact name
I use to have to type The Beatles in the search box
Now I can type %Beatles% in the search box
I would like the users to not have to type %Beatles%
Can I place this on the query code line in my ASP code below?
I tried adding it to the code but it failed every time with syntax error.
On my SQL server I wrote a query that works great but I guess the ASP syntax is slightly different.
Windows 2003 Server IIS 6.0
SQL 2008 Server on a Windows 2008 Server both 64 bit.
(dbo.Artists.artistname = '"& searchWord&"') OR (dbo.Recordings.RecordingT
changed to LIKE
(dbo.Artists.artistname Like '"& searchWord&"') OR (dbo.Recordings.RecordingT
Now I can do searches with having the exact name
I use to have to type The Beatles in the search box
Now I can type %Beatles% in the search box
I would like the users to not have to type %Beatles%
Can I place this on the query code line in my ASP code below?
I tried adding it to the code but it failed every time with syntax error.
On my SQL server I wrote a query that works great but I guess the ASP syntax is slightly different.
<%
dim searchWord
searchWord = Request.Querystring("song")
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxxx;Password=xxxxxxx;Initial Catalog=Music;Network Library=DBMSSOCN;Data Source=xxx.xxx.xxx.xxx,1433"
strSQL1 = "SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle, dbo.Tracks.TrackFileName FROM dbo.Artists INNER JOIN dbo.Recordings ON dbo.Artists.artistid = dbo.Recordings.ArtistID INNER JOIN dbo.Tracks ON dbo.Recordings.RecordingID = dbo.Tracks.RecordingID WHERE (dbo.Artists.artistname Like '"& searchWord&"') OR (dbo.Recordings.RecordingTitle Like '"& searchWord&"') OR (dbo.Tracks.TrackTitle Like '"& searchWord&"')"
Set oRs1=oConn.Execute(strSQL1,lngRecs,1)
if not oRs1.eof then
do until oRs1.eof
response.write "<div class=""results""><input type=""checkbox"" name=""selectItem"" value="""&oRs1("TrackFileName")&"""> "&oRs1("artistname")&" "&oRs1("RecordingTitle")&" "&oRs1("TrackTitle")&" "&oRs1("TrackFileName")&"</div>"
oRs1.movenext
loop
end if
%>
Windows 2003 Server IIS 6.0
SQL 2008 Server on a Windows 2008 Server both 64 bit.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great Job
Thanks for the fast response
Worked great
Now you can just type any thing in the search box and it will search