Display all items in column from database table

Dev Shed Forums Sponsor:

August 14th, 2012, 03:31 PM

kaed

Registered User

Join Date: Sep 2011

Posts: 5

Time spent in forums: 3 h 6 sec
Reputation Power: 0

Display all items in column from database table

I have a database called ForSale with a column Subject. I want to display all the items under subject on my homepage through ASP only (I can't use PHP).

I tried this:

Code:

set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "DSN=forsale"
objconn.Open
  
Dim strSQL
strSQL = "SELECT Subject FROM ForSale"
  
Dim objRS
set objRS = objConn.Execute(strSQL)

response.write(objRS)
  
objConn.close
set objConn = nothing

But it didn't work as it shoots me an error from response.write(objRS). I am new to ASP and very confused with this atm.

August 14th, 2012, 05:07 PM

Doug G

Grumpier Old Moderator

Dev Shed God 19th Plane (14000 - 14499 posts)

Join Date: Jun 2003

Posts: 14,233

Time spent in forums: 1 Month 4 Weeks 14 h 15 m 56 sec
Reputation Power: 4445

Here's a place to start your learning http://msdn.microsoft.com/en-us/lib...64%28v=vs.90%29
You can't response.write an object.

__________________
======
Doug G
======
It is a truism of American politics that no man who can win an election deserves to. --Trevanian, from the novel Shibumi

August 15th, 2012, 01:03 PM

kaed

Registered User

Join Date: Sep 2011

Posts: 5

Time spent in forums: 3 h 6 sec
Reputation Power: 0

Figured it out. And it would have been better if you posted this instead a link to the w3schools ADO page.

Here's the code for future reference and I also added so it would only display the subjects from the past 14 days (and not the dates too).

Code:

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "DSN=forsale"
conn.Open

sql = "SELECT Subject, DatePosted FROM ForSale WHERE ((([DatePosted])<Now()) And (([DateRemove])>Now() Or ([DateRemove]) Is Null)) And ((([Display])=True)) ORDER BY DatePosted DESC"

set rs = conn.execute(sql)

do until rs.EOF
  If rs("DatePosted")> DateAdd("D", -14 , Now) Then 'no posts over 14 days old
	  dim i
	  i = 0
	  for each x in rs.Fields 'loops through both columns (Subject & DatePosted)
		Response.Write("<li><a href='/ForSale/forsale.asp'>" & x.value & "</a></li>")
		i = i + 1
		if i = 1 then exit for 'this is added to keep from displaying the DatePosted column
	  next
  end if
  rs.MoveNext 'next row
loop

rs.close
conn.close
%>

August 15th, 2012, 10:53 PM

Doug G

Grumpier Old Moderator

Join Date: Jun 2003

Posts: 14,233

Time spent in forums: 1 Month 4 Weeks 14 h 15 m 56 sec
Reputation Power: 4445

Quote:

And it would have been better if you posted this instead a link to the w3schools ADO page.

Oh, I didn't realize you just wanted copy & paste code without understanding. Sorry.

August 16th, 2012, 03:26 PM

kaed

Registered User

Join Date: Sep 2011

Posts: 5

Time spent in forums: 3 h 6 sec
Reputation Power: 0

Quote:

Originally Posted by Doug G

Oh, I didn't realize you just wanted copy & paste code without understanding. Sorry.

From your grumpy comment I realized I must have done something unnecessary in the code. So I looked through it and figured the for loop was stupid of me to add.

Now my code is:

Code:

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "DSN=forsale"
conn.Open

sql = "SELECT Subject, DatePosted FROM ForSale WHERE ((([DatePosted])<Now()) And (([DateRemove])>Now() Or ([DateRemove]) Is Null)) And ((([Display])=True)) ORDER BY DatePosted DESC"

set rs = conn.execute(sql)

do until rs.EOF
  If rs("DatePosted")> DateAdd("D", -14 , Now) Then 'no posts over 14 days old
		Response.Write("<li><a href=/ForSale/forsale.asp#" & Replace(rs("Subject"), " ", "") & "'>" & rs("Subject") & "</a></li>")
  end if
  rs.MoveNext 'next row
loop

rs.close
conn.close
%>

Everything in the code (except the do until rs.EOF and the variable names) are mine. I also understand everything in the code. Still grumpy?

September 7th, 2012, 04:18 AM

haowangy

Permanently Banned

Join Date: Aug 2012

Posts: 3

Time spent in forums: 32 m 29 sec
Warnings Level: 10
Number of bans: 1
Reputation Power: 0

Quote:

Originally Posted by kaed

I have a database called ForSale with a column Subject. I want to display all the items under subject on my homepage through ASP only (I can't use PHP).

I tried this:

Code:

set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "DSN=forsale"
objconn.Open
  
Dim strSQL
strSQL = "SELECT Subject FROM ForSale"
  
Dim objRS
set objRS = objConn.Execute(strSQL)

response.write(objRS)
  
objConn.close
set objConn = nothing

But it didn't work as it shoots me an error from response.write(objRS). I am new to ASP and very confused with this atm.

I am new to ASP and very confused with this atm.

November 24th, 2012, 01:57 AM

ghyupdc

Registered User

Join Date: Nov 2012

Posts: 2

Time spent in forums: 26 m 6 sec
Reputation Power: 0

From your grumpy comment I realized I must have done something unnecessary in the code. So I looked through it and figured the for loop was stupid of me to add.

Display all items in column from database table

Developer Shed Advertisers and Affiliates