ASP Page getting Error Please check my code

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.
LVL 23
Thomas GrassiSystems AdministratorAsked:
Who is Participating?
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
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
 
DMTechGrooupCommented:
What is the code in your include file?

<!-- #include file="db_connection.inc" -->
0
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I think strConnect should be oConn
0
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
My connection strings are typically

MyConnection = "Provider=SQLOLEDB; datasource=localhost; Database=database_name; Uid=user_name; Pwd=abc_123"
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Here is a good sample, check out the DSN-Less sample http://webcheatsheet.com/asp/database_connection_to_MSSQL.php
0
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
Carl

Example please?
0
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
Sorry, I meant to take out the Response.Write and Response.End lines, and try it with the connection string hard coded.
0
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
I'm getting a blank page regardless, which would suggest that the Response.End is still in there.
0
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
Carl,

Now

the page presents

opening connection
Connection open

Take a look
0
 
Carl TawnSystems and Integration DeveloperCommented:
OK. So that suggest your database connection is now working. The blank page is presumably down to your query not returning any results.
0
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
Yes.
0
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.