Hi everyone,

Long time ASP 3.0 user, ultra n00b to ASP.NET. I've finally found an example of using an SqlDataReader on 4GuysFromRolla which is working a treat, I feel like I'm at home again (as opposed to using data binding which is new to me).

The reason I am using a DataReader is because I need to perform some on-the-fly calculations which seems far too difficult in a DataGrid (and too heavy, not what it's designed for etc).

At any rate, I am executing a very simple query, which I hope to build a table from. The obvious issue I am having is, the sub Page_Load is called when the page is first loaded (regardless of where this sub is located within the ASPX file).

I have tried a couple of options to get the data in the correct position in the HTML, but these seem cumbersome. I.e. Storing the output in a string and doing a Response.Write of this string in the correct position or creating this as a seperate function which executes at the correct position. Neither work.

I obviously don't have any understand on how ASP.NET pages are constructed.

Here be the code, I hope someone can point out how ridiculously simple this is when you know what to do!

VB.Net Code:
  1. <%@ Import Namespace="System.Data" %>

  2. <%@ Import Namespace="System.Data.SqlClient" %>

  3.  
  4. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

  5.  
  6. <html xmlns="http://www.w3.org/1999/xhtml">

  7. <head runat="server">

  8.     <title></title>

  9. </head>

  10. <body>

  11. <table>

  12.     <tr>

  13.         <td>Quantitiy</td>

  14.         <td>Description</td>

  15.         <td>Item Price</td>

  16.         <td>Item Total</td>

  17.     </tr>

  18. <script language="VB" runat="server">

  19.     

  20.     Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

  21.         

  22.         Dim strTableOutput As String = Nothing

  23.         

  24.         Dim blnError As Boolean = False

  25.         

  26.         Dim strCRM_GUID As String

  27.         strCRM_GUID = Request.QueryString("id")

  28.         If strCRM_GUID = Nothing Then

  29.             blnError = True

  30.         End If

  31.         

  32.         Dim strRef_Arrow As String

  33.         strRef_Arrow = Request.QueryString("Ref_Arrow")

  34.         If strRef_Arrow = Nothing Then

  35.             blnError = True

  36.         End If

  37.         

  38.         If Not blnError Then

  39.             'Create a connection string

  40.             Dim connString As String

  41.             connString = "Data Source=CCA-SQL;Initial Catalog=Arrow;Integrated Security=True;"

  42.     

  43.             'Open a connection

  44.             Dim objConnection As SqlConnection

  45.             objConnection = New SqlConnection(connString)

  46.             objConnection.Open()

  47.     

  48.             'Specify the SQL string

  49.             Dim strSQL As String = "SELECT [QUANTITY], [DESCRIPTION], [SELLING_PRICE], [NET_VALUE], [TAX_VALUE] FROM [ARROW_TO_CRM_DEBTOR_CHECKDETAIL] WHERE (([CRM_GUID] = '" & strCRM_GUID & "') AND ([REF_ARROW] = '" & strRef_Arrow & "'))"

  50.     

  51.             'Create a command object

  52.             Dim objCommand As SqlCommand

  53.             objCommand = New SqlCommand(strSQL, objConnection)

  54.  
  55.             'Get a datareader

  56.             Dim objDataReader As SqlDataReader

  57.             objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

  58.  
  59.             If Not objDataReader.HasRows Then

  60.                 'Output No Rows

  61.                 Response.Write("<TR><TD COLSPAN=""5"">There are no records available</TD></TR>")

  62.             Else

  63.                 'Output

  64.                 While objDataReader.Read()

  65.                     Response.Write("<TR><TD>" & objDataReader("QUANTITY") & "</TD></TR>")

  66.                 End While

  67.             End If

  68.             

  69.             'Close the datareader/db connection

  70.             objDataReader.Close()

  71.         Else

  72.             'Output No Rows

  73.             Response.Write("<TR><TD COLSPAN=""5"">There are no records available</TD></TR>")

  74.         End If

  75.          

  76.     End Sub

  77. </script>

  78. </table>

  79. </body>

  80. </html>

This is the output I get:
Code:
<TR><TD>10.0000</TD></TR><TR><TD>4.0000</TD></TR><TR><TD>7.0000</TD></TR><TR><TD>20.0000</TD></TR><TR><TD>2.0000</TD></TR><TR><TD>9.0000</TD></TR><TR><TD>7.0000</TD></TR><TR><TD>1.0000</TD></TR>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head><title>

</title></head>
<body>
<table>

    <tr>
        <td>Quantitiy</td>
        <td>Description</td>
        <td>Item Price</td>
        <td>Item Total</td>
    </tr>

</table>
</body>
</html>
Also, I am using Visual Web Developer 2008 Express, not Visual Studio 2008.

Cheers,
Scoota