Inserting SQL data for one table into an html table using ASP.net or Classic ASP

Question

For a bit of background info I'm a Network Admin/Systems Admin with very very minimal self-taught programming 'skills'. In the past I have just modified and tweaked existing code to get whatever I was looking for to work but I'm not having any luck with this current project. I am looking to add dynamic prices from a sql table into an existing html table for only a few of the records that are in the sql table.

I'm using a locally hosted server using IIS6 with a mix of ASP.net 2.0 and classic asp on the site with a 2008 MS SQL Server database.

I already have the connection to the db made in global.asa and am looking for how I can correspond each price to each html item number in the table.

(the sql code I copy/pasted from a different asp file with different intentions so if something looks completely off its probably because of that :[ )

Ex:

<html>
  <head>
<%
       sql = "SELECT * FROM tblProductCatalogue WHERE ( (tblProductCatalogue.CustomerID = 1 )  and (tblProductCatalogue.ItemNumber = ItemNumber)) " 
       Set rs = Server.CreateObject("ADODB.Recordset")
       rs.Open sql, conn, 3, 3

   if NOT rs.eof then
    rs.MoveFirst        

       DerivedPrice = rs("DerivedPrice")

    rs.close
    Set rs = Nothing

%>
  </head>

  <body>
    <table>
      <tr>
        <th>Item Number</th>
        <th>Description</th>
        <th>Price</th>
      </tr>

      <tr>
        <td>PartNumber1</td>
        <td>description1</td>
        <td><%DerivedPrice%></td>
      </tr>

      <tr>
        <td>PartNumber2</td>
        <td>description2</td>
        <td><%DerivedPrice%></td>
      </tr>

      <tr>
        <td>PartNumber3</td>
        <td>description3</td>
        <td><%DerivedPrice%></td>
      </tr>

    </table>
  </body>
</html>

Thanks!

Stan

How are the item numbers in the html table being populated? (hard coded in the html page or rendered dynamically off of a different select?) If a different select you may be able to get price and item information at the same time which would be most efficient. Otherwise, you'll need to modify the select statement to pull back one or all of the items in the html table probably by item number if it's unique. — xQbert
– xQbert, Commented Jan 3, 2014 at 19:57

tgolisch · Accepted Answer · 2014-01-03 23:41:22Z

2

So close! You need a loop around your table rows. Replace your table with this:

if NOT rs.eof then rs.MoveFirst

'...remove this code from your exampl (above), snip---'
   DerivedPrice = rs("DerivedPrice")

rs.close 'especially this code'
Set rs = Nothing  'and this code'
'---snip, end----'
%>
<%= rs.RecordCount %> Rows<br/> <!-- optional, for troubleshooting -->

<table>
  <tr>
    <th>Item Number</th>
    <th>Description</th>
    <th>Price</th>
  </tr>
<%
While NOT rs.eof  'loop through the dataset'
%>
  <tr>
    <td><%= rs("PartNumber") %></td>
    <td><%= rs("Description") %></td>
    <td><%= rs("DerivedPrice") %></td>
  </tr>
<%
   rs.MoveNext
Wend 'end while

rs.close
Set rs = Nothing
%> 
</table>

edited Jan 3, 2014 at 23:41

answered Jan 3, 2014 at 20:02

tgolisch

6,7743 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3158444 Over a year ago

It looks like I messed up somewhere else as well, it's only grabbing the first record but the same sql statement used in a query on the server pulls 1428 rows, is it something with my cursortyp,locktyp values? Thanks

tgolisch Over a year ago

Cursortyp, locktyp that you are using should give you a bi-directional read/write dataset. There is nothing stronger. I added a couple lines of code for troubleshooting. It outputs the rowcount (should show 1428)

user3158444 Over a year ago

that gave me..Microsoft VBScript runtime error '800a01b6' Object doesn't support this property or method: 'RowCount'

tgolisch Over a year ago

Oh sorry. Rowcount is SQL. I meant RecordCount.

user3158444 Over a year ago

Sorry and thanks again for all the help, it was just a line in the table I was trying to comment out until I was ready for and messed up. Its working now. Thanks - Stan

Collectives™ on Stack Overflow

Inserting SQL data for one table into an html table using ASP.net or Classic ASP

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related