Solved

ASP Page getting Error Please check my code

Posted on 2014-04-17
53
448 Views
Last Modified: 2014-04-19
I am searching a SQL database, I have tested the quesry in SQL and it works trying to get my asp code to work.

This is the error

ADODB.Connection error '800a0e78'

Operation is not allowed when the object is closed.

/music2.asp, line 11


My Code

<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

<!-- #include file="db_connection.inc" --> 

Set oConn=Server.CreateObject("ADODB.Connection") 

strSQL1 = "SELECT     dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("song")&"</div>"
    oRs1.movenext
    loop
end if


%>

Open in new window


The main page music.htm has the search box where I would like to enter the search criteria and then pass it to music2.asp to search the database and present a list of the items

You can check my web site www.tomsmp3.com  try the search
That is what give the error.


Any code changes please post I can use all the help you can provide.
0
Comment
Question by:Thomas Grassi
  • 24
  • 21
  • 5
  • +2
53 Comments
 
LVL 24

Expert Comment

by:DMTechGrooup
ID: 40008150
What is the code in your include file?

<!-- #include file="db_connection.inc" -->
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40008156
DMT thanks for the fast response

<%strConnect ="Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password=passcode;Initial Catalog=Music;Data Source=tcp:192.168.1.18\music,2433"%>
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40008644
I think strConnect should be oConn
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40008648
Scott

Thanks for responding

you mean like this?

<%stroConn ="Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password=passcode;Initial Catalog=Music;Data Source=tcp:192.168.1.18\music,2433"%>

OR

<%oConn ="Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password=passcode;Initial Catalog=Music;Data Source=tcp:192.168.1.18\music,2433"%>
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40008655
Right now the easier way would be to change the connection to

<%oConn ="Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password=passcode;Initial Catalog=Music;Data Source=tcp:192.168.1.18\music,2433"%>

Open in new window


The issue is you have set the name of the connection to one thing, but then in your code you are using another.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40008674
Scott,

Thanks

Made the change but same results.

ADODB.Connection error '800a0e78'

Operation is not allowed when the object is closed.

/music2.asp, line 11


What am I missing here?

Do you get same error when you try my site?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40008678
You're not opening your connection before attempting to use it:
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open()     '// open the connection

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

'// etc, etc

Open in new window

0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40008688
Carl thanks for responding

You mean like this?

<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

<!-- #include file="db_connection.inc" --> 

Set oConn=Server.CreateObject("ADODB.Connection") 

oConn.Open()     '// open the connection

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

strSQL1 = "SELECT     dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"


if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("song")&"</div>"
    oRs1.movenext
    loop
end if


%>

Open in new window

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40008698
Yes. You need to open the connection before you can execute a query on it. You'll also need to close it again when you're done.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40008702
Dave

Made that change and getting this error

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/music2.asp, line 9


Do you get the same when you go to my site? www.tomsmp3.com
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40008709
Sorry, forgot the connection string:
<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

<!-- #include file="db_connection.inc" --> 

Set oConn=Server.CreateObject("ADODB.Connection") 

oConn.Open "your connection string here"     '// open the connection

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

strSQL1 = "SELECT     dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"


if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("song")&"</div>"
    oRs1.movenext
    loop
end if


%>

Open in new window

0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40008761
without changing anything in your include file, your code should be:

<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

<!-- #include file="db_connection.inc" --> 

Set oConn=Server.CreateObject("ADODB.Connection") 
oConn.Open strConnect

strSQL1 = "SELECT     dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("song")&"</div>"
    oRs1.movenext
    loop
end if


%>

Open in new window

0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40009952
Guys

Just got back from visiting family.

my db_connection.inc
<%strConnect ="Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password=xxxx;Initial Catalog=xxx;Data Source=tcp:xxx\xxx,2433"%>

My ASP code

<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

<!-- #include file="db_connection.inc" --> 

Set oConn=Server.CreateObject("ADODB.Connection") 

oConn.Open strConnect

strSQL1 = "SELECT     dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("song")&"</div>"
    oRs1.movenext
    loop
end if


%>

Open in new window



Still getting this error

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/music2.asp, line 9


What am I doing wrong here?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010178
Double check your SQL Server details. Is it definitely a named instance, and on port 2433? You also shouldn't need the "tcp" part on the front.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40010253
My connection strings are typically

MyConnection = "Provider=SQLOLEDB; datasource=localhost; Database=database_name; Uid=user_name; Pwd=abc_123"
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40010255
Here is a good sample, check out the DSN-Less sample http://webcheatsheet.com/asp/database_connection_to_MSSQL.php
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010406
Carl

Where can I check for those settings?

is there a query I can run?

I have three instances of SQL server running

I have another web site that using a sql data base  on another instance of SQL and that db_connection.inc works I copied that one to use.

Forgot about where the port number is located.

This new database is on the default instance maybe port 1433?

I am not sure where this is located any ideas?


I believe I need the TCP information on the connection string to allow outside users access this it has been I long time since I did this

Any help would be greatly appreciated.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010455
You'll need to open up the SQL Server Configuration Manager, which should be under the SQL Server program group on your Start menu. From there expand SQL Server Network Configuration > Protocols for MSSQLSERVER (there will probably be one of these per instance, so pick the right one).

Then right-click on TCP/IP and choose Properties. You should get a screen like below detailing the IP and Port info:
Configuration Manager
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010471
Carl

Thanks

Found it using port 1433

My new string is:

<%strConnect ="Provider=SQLOLEDB;Persist Security Info=True;User ID=xxxxxxxx;Password=xxxxxxx;Initial Catalog=Music;Network Library=DBMSSOCN;Data Source=tcp:xxx.xxx.xxx.xx,1433"%>

Open in new window



Also this database is using the default sql instance not an instance called music so I removed \music from the tcp ip address and changed the port to 1433

But still getting the same error


Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/music2.asp, line 9


Pulling my hair out hear not sure what I am missing
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010474
Lose the tcp part, and the Network Library parameter. If it still fails, check that you have MDAC installed on the web server - you may not have the SQLOLEDB.1 driver installed.

Also, what SQL version are you using? You may want to use the Native Client instead.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010493
My SQL server is 2008 64 bit
My Windows Server that runs SQL server is Windows 2008 64 bit
My Windows server that runs my web site is Windows 2003

Carl removed those setting still same error.

On the Web server I have another web site that access a sql database using the exact same connection string and works with no problem. That data base is on another sql instance.

I have one web server with several site

I have one sql server with several instances

The connection string for the working web site using a sql database is

<%strConnect ="Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxxx;Password=xxxxx;Initial Catalog=WrestlingHistory;Data Source=tcp:xxx.xxx.xxx.xx\wrestling,2433"%>

Open in new window


That works

So that would indicate that SQLOLEDB.1 is installed otherwise the one above would not work don't you think?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010504
It would indeed. The error still suggests that something is wrong with the server/port combination you are using though.

If you create an ODBC connection through the ODBC Data Sources option in Control Panel, does it work ok?
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010515
Carl

From my computer Windows 7 Pro the one I am doing the development on I went to Control Panal Ran Set Up data sources OBC
Added a User DSN and a System DSN both tested successfully.
When selecting SQL server it searched and found all three of my sql instances.
So I believe that my network configuration is good.

Just can not figure out this db_connection.inc setup and my asp code to work.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010520
I'd Response.Write out your connection string just before you attempt to use it in order to verify that it is actually using the one you are expecting.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010534
Carl

Example please?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010589
Put these two lines right before you open the connection:
Response.Write strConnect
Response.End

Open in new window

That will flush the connection string to the client so you can see what it is set to right before it is used.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010608
Carl

Thanks

<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

<!-- #include file="db_connection.inc" --> 

Set oConn=Server.CreateObject("ADODB.Connection") 

Response.Write strConnect
Response.End

oConn.Open strConnect

strSQL1 = "SELECT     dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("song")&"</div>"
    oRs1.movenext
    loop
end if


%>

Open in new window


It produces a blank page.

www.tomsmp3.com   enter pink Floyd in search box click on submit button
check it out
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010615
OK. That means that either your connection string variable isn't called strConnect in your include file, or it's getting cleared out somewhere.

To test, change:
oConn.Open strConnect

Open in new window

To:
oConn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxxxxxx;Password=xxxxxxx;Initial Catalog=Music;Network Library=DBMSSOCN;Data Source=xxx.xxx.xxx.xx"

Open in new window

Filling in the xxx parts to suit.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010650
Carl

thanks

<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))


Set oConn=Server.CreateObject("ADODB.Connection") 



oConn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxxxx;Password=xxxxxxxxxx;Initial Catalog=Music;Network Library=DBMSSOCN;Data Source=xxx.xxx.xxx.xxx,1433"

Response.Write strConnect
Response.End

strSQL1 = "SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("song")&"</div>"
    oRs1.movenext
    loop
end if


%>

Open in new window



still getting a blank page

Have you gone to the site and try it?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010655
Sorry, I meant to take out the Response.Write and Response.End lines, and try it with the connection string hard coded.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010661
Carl

No problem glad your trying to help me

<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

Set oConn=Server.CreateObject("ADODB.Connection") 

oConn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxx;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 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("song")&"</div>"
    oRs1.movenext
    loop
end if


%>

Open in new window


Same results blank page

TRy it from your computer

http://www.tomsmp3.com/music2.asp?song=pink+floyd
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010664
I'm getting a blank page regardless, which would suggest that the Response.End is still in there.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010696
Carl,

<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

Set oConn=Server.CreateObject("ADODB.Connection") 

Response.End

oConn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxxxxxxxxx;Password=xxxxxxxxxxxx;Initial Catalog=Music;Network Library=DBMSSOCN;Data Source=xxx.xxx.xxx.xxx,1433"

strSQL1 = "SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("song")&"</div>"
    oRs1.movenext
    loop
end if


%>

Open in new window


still blank page.

This is very strange
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010698
You need to take out the Response.End, as it causes execution of the page to halt at that point.

Try your code as:
<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

Set oConn=Server.CreateObject("ADODB.Connection") 

Response.Write "opening connection<br />"
Response.Flush

oConn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxxxxxxxxx;Password=xxxxxxxxxxxx;Initial Catalog=Music;Network Library=DBMSSOCN;Data Source=xxx.xxx.xxx.xxx,1433"

Response.Write "Connection open<br />"
Response.Flush

strSQL1 = "SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("song")&"</div>"
    oRs1.movenext
    loop
end if
%>

Open in new window

0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010718
Carl,

Now

the page presents

opening connection
Connection open

Take a look
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010726
OK. So that suggest your database connection is now working. The blank page is presumably down to your query not returning any results.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010735
Carl

You mean this ?
strSQL1 = "SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

I tested the above in SQL and it works.


Is my ASP code correct  after this line in my asp code?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010739
Yes, the part after that looks correct, but you shouldn't need the HTMLEncode part earlier on.

Change:
searchWord=Server.HTMLEncode(request.form("search"))

Open in new window

To:
searchWord=request.form("search")

Open in new window

0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010744
Carl,

now

<%
dim searchWord
searchWord=request.form("search")
Set oConn=Server.CreateObject("ADODB.Connection") 

Response.Write "opening connection<br />"
Response.Flush

oConn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxx;Password=xxxxxxx;Initial Catalog=Music;Network Library=DBMSSOCN;Data Source=xxxx.xxx.xxx.xxx,1433"

Response.Write "Connection open<br />"
Response.Flush

strSQL1 = "SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("song")&"</div>"
    oRs1.movenext
    loop
end if


%>

Open in new window



Same results

Check it out
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010748
So next you need to check what query you are executing.
<%
dim searchWord
searchWord=request.form("search")
Set oConn=Server.CreateObject("ADODB.Connection") 

Response.Write "opening connection<br />"
Response.Flush

oConn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxx;Password=xxxxxxx;Initial Catalog=Music;Network Library=DBMSSOCN;Data Source=xxxx.xxx.xxx.xxx,1433"

Response.Write "Connection open<br />"
Response.Flush

strSQL1 = "SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

Response.Write strSQL1 & "<br />"
Response.Flush

Set oRs1 = oConn.Execute(strSQL1,lngRecs,1)

Response.Write "Number of record: " & Cstr(lngRecs) & "<br />"
Response.Flush

if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("song")&"</div>"
    oRs1.movenext
    loop
end if
%>

Open in new window

0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010753
Carl,

Thanks

opening connection
Connection open
SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = '') OR (dbo.Recordings.RecordingTitle = '') OR (dbo.Tracks.TrackTitle = '')
Number of record: 0


No records returned
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010759
Ah, your querystring value isn't called search, it's called song. So:
searchWord = Request.Querystring("search")

Open in new window

Needs to be:
searchWord = Request.Querystring("song")

Open in new window

0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010766
Carl,

Do you mean

dim searchWord
searchWord=request.form("search")
Set oConn=Server.CreateObject("ADODB.Connection")

To

dim searchWord
searchWord = Request.Querystring("song")
Set oConn=Server.CreateObject("ADODB.Connection")
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010768
Yes.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010770
Carl

we are moving forward after the above change we get this now

opening connection
Connection open
SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = 'pink floyd') OR (dbo.Recordings.RecordingTitle = 'pink floyd') OR (dbo.Tracks.TrackTitle = 'pink floyd')
Number of record: -1

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/music2.asp, line 26
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 40010774
That's because there is no column in your query called "song". So you need to change the line:
response.write "<div class=""results"">"&oRs1("song")&"</div>"

Open in new window

To reference one of the columns in your query.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010785
Carl

I would like the users to be able to enter either

Artist name
Name of Album
or  Name of Song

Changed to this

response.write "<div class=""results"">"&oRs1("artistname")&"</div>"


Check it out

Producing some results now.

How do I display the remainder of the data from the query?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010788
Your code is already looping through and showing all of the rows. So, all you need to do is add in the columns you want to display. Basically you need this section:
oRs1("artistname")

Open in new window

For each field you want to display.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40010805
trgrassijr55,

You have come a long way to get this far.  Honestly, it would probably be best to get a professional to help you the rest of the way.  If you are determined to keep going on, I think Carl has done a great job so far.

Probably a good idea to close out the question since you at least have your data pouring out.

Then 2 separate questions to deal with the data and finally making sure the input is scrubbed  and using a parameter query because what you are doing now will open you up for an sql injection.   But don't go to step 3 without first going to step 2.

Good luck.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40010845
Carl,

Good news got it working now.

Take a look.

I just need to figure out how to make the output a url so they can click on the item and play the song.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40010849
You can inject a field from your recordset into a anchor tag , you just need to decide which field you need.

Something along the lines of:
Response.Write "<a href=playsong.asp?songid=" & oRs1("some_field") & ">click</a>"

Open in new window

0
 
LVL 23

Author Closing Comment

by:Thomas Grassi
ID: 40010905
Carl

Thanks for all the help

Working on next phase of the site now

Need to get the url working so they can select which song to play and need to get a way to have the users crate a playlist
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now