We help IT Professionals succeed at work.

Data from SQLDataReader not being displayed on page

329 Views
Last Modified: 2012-05-05
I have a web page in VB.NET that I'm developing in MS Visual Web Developer 2005 Express Edition.  I pass the product SKU for a specific product in a QueryString - product_details.aspx?sku=08525A.  But when the page loads, I'm no longer getting errors but I'm not getting anything (pretty much a blank page).  I ran the SQL query (with a value for the Request.QueryString("sku")) against my database and got the desired results.  What am I not doing right?
Script:
 
Public Sub Page_Load(ByVal s As Object, ByVal e As EventArgs)
            GetProduct()
            If Not IsPostBack Then
                makeCart()
            End If
        End Sub
        
        Sub GetProduct()
            Dim sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
            sqlcon.Open()
            Dim mySQL As String
            mySQL = "SELECT product.sku AS sku, product_main.product_name AS product, product.product_name as short_descr, product.description AS description, product.price AS Price FROM product INNER JOIN product_main ON product.associated_master_product_id = product_main.id WHERE (product.sku NOT LIKE '%-main') AND (product.sku = '" & Request.QueryString("sku") & +"')"
            Dim command As New SqlCommand(mySQL, sqlcon)
            Dim reader As SqlDataReader
            reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
            While reader.Read()
                If Not reader.GetValue(0) Is DBNull.Value Then lblSKU.Text = reader.GetValue("0")
                If Not reader.GetValue(1) Is DBNull.Value Then lblProduct.Text = reader.GetValue("1")
                If Not reader.GetValue(2) Is DBNull.Value Then lblShort_descr.Text = reader.GetValue("2")
                If Not reader.GetValue(3) Is DBNull.Value Then lblDescription.Text = reader.GetValue("3")
                If Not reader.GetValue(4) Is DBNull.Value Then lblPrice.Text = reader.GetValue("4")
                'If Not reader.GetValue(5) Is DBNull.Value Then product = reader.GetValue("5")
                'If Not reader.GetValue(6) Is DBNull.Value Then product = reader.GetValue("6")
                'If Not reader.GetValue(7) Is DBNull.Value Then product = reader.GetValue("7")
                'If Not reader.GetValue(8) Is DBNull.Value Then product = reader.GetValue("8")
                'If Not reader.GetValue(9) Is DBNull.Value Then product = reader.GetValue("9")
            End While
            sqlcon.Close()
        End Sub
 
Web Page:
 
<table width="622" border="0" cellpadding="0" cellspacing="0">
              <tr height="142">
                <td width="10" height="142">&nbsp;</td>
                <td width="200" align="center" valign="top" bgcolor="#dddddd"><img src="/images/28860.jpg.jpg" border="0" /></td>
                <td width="412">&nbsp;</td>
              </tr>
              <tr height="10">
                <td colspan="3" height="10" width="622"></td>
              </tr>
              <form id="form1" runat="server">
              <tr>
                <td bgcolor="#FFFFFF" width="10" height="30">&nbsp;</td>
                <td colspan="2" width="612"><h2><asp:Label ID="lblProduct" runat="server" />&nbsp; &nbsp;
                SKU #<asp:Label ID="lblSKU" runat="server" /></h2></td>
              </tr>
              <tr height="20">
                <td colspan="3" height="5" width="622"></td>
              </tr>
              <tr>
                <td bgcolor="#FFFFFF" width="10" height="30">&nbsp;</td>
                <td colspan="2" width="612"><h3><asp:Label ID="lblShort_descr" runat="server" /></h3></td>
              </tr>
              <tr height="20">
                <td colspan="3" height="5" width="622"></td>
              </tr>
              <tr>
                <td bgcolor="#FFFFFF" width="10" height="30">&nbsp;</td>
                <td colspan="2" width="612" class="product"><asp:Label ID="lblDescription" runat="server" /></td>
              </tr>
              <tr height="20">
                <td colspan="3" height="5" width="622"></td>
              </tr>
</form>

Open in new window

Comment
Watch Question

Does the LAST row in the result set include what you want?  b/c only 1 row will be displayed by your code.

Beyond that ... I'm wary of the construction:
If Not reader.GetValue(0) Is DBNull.Value Then lblSKU.Text = reader.GetValue("0")

I'd try:
If Not reader.IsDBNull(0)  Then lblSKU.Text = reader.GetValue(0)

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.isdbnull(VS.80).aspx

Author

Commented:
No, I'm not getting any of the data I want.  Sorry, took the code suggestion from an online tutorial.  I tried your construction and got the same results - no data.

Commented:
If Not IsDBNull(reader.item("SKU")) then
     lblSKU.Text = Reader.GetString(Reader.GetOrdinal("SKU"))
End If
 

Author

Commented:
nenwmn,

I used your construction and still nothing.  What is the deal with this?  I just want to display text on my web page.

Commented:
When you say no data, is the reader returning any rows using the sql statement.

String.Format("{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}{10}{11)", _
"SELECT product.sku AS sku, ", _
"product_main.product_name AS product, ", _
"product.product_name as short_descr, ", _
"product.description AS description, ", _
"product.price AS Price ", _
"FROM product ", _
"INNER JOIN product_main ON ", _
"product.associated_master_product_id = product_main.id ", _
"WHERE (product.sku NOT LIKE '%-main') AND ", _
"(product.sku ='", _
Request.QueryString("sku"), _
"')")

Author

Commented:
That's right.  No errors, but no data being displayed.  In other words, each of my lblLabel.Text is blank.

Commented:
I see an alias AS product is used and is also a named table in the database.

Commented:
Your labels will be blank when the query returns no data. Set a breakpoint and Watch to confirm the sql statement is actually returning a row.

Author

Commented:
Ok, I swapped the alias product for title (product_main.product_name AS title).  No change (nothing bein displayed).
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Let me try and get back to you.  I have an idea of what might be causing this, but am more than willing to try your suggestion.

Author

Commented:
Nenwmn,

Would you please take a look at my SqlConnection.  Some else mention that it may not be correct.

Author

Commented:
I have tried both my connection strings (that work on other pages) but I still get nothing.  Is there something still missing?  Do I have to bind the dat in order to view it on a web page?  This is frustrating.  Can anyone help?  ANd good example or tutorials you would recommend?

Commented:
Your SQL connection is stored in your web.config you are referencing.

You can tell whether the connection is made this by putting a breakpoint in you code and  watch the properties of sqlconn.

Commented:
Dm l_Conn as SqlConnection
Dim l_Comm as SqlCommand
Dim l_Reader as SqlDataReader

'Connection string from web config
Dim l_ConnectionString As String = (ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)

'Initialize the connect
l_Conn  New SqlConnection(l_ConnectionString)
'Create the command
l_Comm = New SqlCommand("Add Sql")
Try
'Open the connection
l_Conn.Open

'Execute the command
l_Reader = l_Comm.ExecuteReader()

Do While l_Reader.Read
     me.txtSKU.text = l_Reader.Item("SKU".Tostring
Loop
'Close he Reader
l_Reader.Close
Catch ex As Exception

Finally
'Close the connection
l_Conn.Close
End Try

Author

Commented:
Giving it a fresh try.

Author

Commented:
Your suggested code, my variable names - same results . . . nothing.

I had some issues with the Web.Config on the web server for our production sites.  Could be something askew with that.  Do you have any recommendations there?
Sub GetProduct()
            Dim myDR As SqlDataReader
 
            'Connection string from web config
            Dim strConn As String = (ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
 
            'Initialize the connect
            Dim MyConn As New SqlConnection(strConn)
            
            'Create the command
            Dim MySQL As String = "SELECT product.sku AS sku, product_main.product_name AS product, product.product_name as short_descr, " & _
            "product.description AS description, product.price AS price FROM product INNER JOIN product_main ON " & _
            "product.associated_master_product_id = product_main.id WHERE (product.sku NOT LIKE '%-main') AND " & _
            "(product.sku = '" & Request.QueryString("sku") & "')"
            
            Dim myCmd As New SqlCommand(MySQL, MyConn)
            
            Try
                'Open the connection
                MyConn.Open()
 
                'Execute the command
                myDR = myCmd.ExecuteReader()
 
                Do While myDR.Read
                    Me.lblSKU.Text = myDR.Item("SKU".ToString)
                Loop
 
                'Close he Reader
                myDR.Close()
 
            Catch ex As Exception
 
            Finally
                'Close the connection
                MyConn.Close()
 
            End Try
        End Sub
 
And on the page:
 
<div>
                <form id="form1" runat="server">
                    <asp:Label ID="lblSKU" runat="server" /><asp:Label ID="lblProduct" runat="server" /><asp:Label ID="lblPrice" runat="server" />
                    Quantity: <asp:textbox id="txtQuantity" runat="server" />&nbsp; &nbsp;
                    Total: <asp:Label id="lblTotal" runat="server" />&nbsp; &nbsp;
                    <asp:Button id="btnAdd" runat="server" Text="Add To Cart" onClick="AddToCart" /><br /><br />
                    <asp:DataGrid id="dg" runat="server" Visible="false" /> <asp:Label ID="lblCount" runat="server" Visible="false" />
                </form>
                </div>

Open in new window

Commented:
Ok there is a typo that should have been picked up during compilation.

Me.lblSKU.Text = myDR.Item("SKU".ToString) missing the closing bracket)

You say the connection settings in the web config file have been tested on other pages with success. Yes post the webconfig im intrigued.


This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Not sure if you're keeping count, but I have re-coded this fairly simple page 4 times now, with no results.  Yes, I had to do a couple things with the web.config (can one of those go corrupt?), but the other pages that use the same connection strings work fine.

Ok, excerpt from slightly masked web.config file here (for security reasons).
<configuration>
	<appSettings/>
	<connectionStrings>
		<add name="OVCconnection" connectionString="server=SERVER33\SQLEXPRESS;uid=MyID;pwd=MyPASSWORD; database=omahavaccine;"/>
		<add name="omahavaccineConnectionString" connectionString="Data Source=SERVER33\SQLEXPRESS;Initial Catalog=omahavaccine;Integrated Security=True" providerName="System.Data.SqlClient"/>
	</connectionStrings>
	<system.web>
		<compilation debug="true" strict="false" explicit="false"/>
		<authentication mode="Windows"/>
        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
	</system.web>
</configuration>

Open in new window

Author

Commented:
Sorry, getting no SKU.  In my haste to try nenwmn's most recent code suggestion, I inadvertantly removed the code to pull the sku from the Request.QueryString (product_details.aspx?sku=..).  I'll put that back in and run it again.

Author

Commented:
Please disreguard my last post - wrong thread/forum.

Author

Commented:
Ok, I tried DanielWilson's code in the Catch block.  No errors when I run in, but nothing displayed (no exceptions)  either.

Anything missing?

HELP!!!!
Script:
 
Sub GetProduct()
            Dim myDR As SqlDataReader
 
            'Connection string from web config
            Dim strConn As String = "server=SERVER33\SQLEXPRESS;uid=Redesigned_OVC;pwd=Th1s1s4sc0t; database=omahavaccine;"
 
            'Initialize the connect
            Dim MyConn As New SqlConnection(strConn)
            
            'Create the command
            Dim MySQL As String = "SELECT product.sku AS sku, product_main.product_name AS product, product.product_name as short_descr, " & _
            "product.description AS description, product.price AS price FROM product INNER JOIN product_main ON " & _
            "product.associated_master_product_id = product_main.id WHERE (product.sku NOT LIKE '%-main') AND " & _
            "(product.sku = '" & Request.QueryString("sku") & "')"
            
            Dim myCmd As New SqlCommand(MySQL, MyConn)
            
            Try
                'Open the connection
                MyConn.Open()
 
                'Execute the command
                myDR = myCmd.ExecuteReader()
 
                Do While myDR.Read
                    Me.lblSKU.Text = myDR.Item("SKU".ToString)
                Loop
 
                'Close he Reader
                myDR.Close()
 
            Catch ex As Exception
                Me.lblMyErrorLabel.Text = ex.ToString
 
            Finally
                'Close the connection
                MyConn.Close()
 
            End Try
        End Sub
 
And on Web page:
 
<div>
                <form id="form1" runat="server">
                    <asp:Label ID="lblSKU" runat="server" /><asp:Label ID="lblProduct" runat="server" /><asp:Label ID="lblPrice" runat="server" /><br /><br />
                    <asp:Label ID="lblMyErrorLabel" runat="server" />
                    Quantity: <asp:textbox id="txtQuantity" runat="server" />&nbsp; &nbsp;
                    Total: <asp:Label id="lblTotal" runat="server" />&nbsp; &nbsp;
                    <asp:Button id="btnAdd" runat="server" Text="Add To Cart" onClick="AddToCart" /><br /><br />
                    <asp:DataGrid id="dg" runat="server" Visible="false" /> <asp:Label ID="lblCount" runat="server" Visible="false" />
                </form>
                </div>

Open in new window

product-details.jpg

Author

Commented:
Ok, found the root of my problems.  At the top of my page I have a line:
<%@ Page Language="VB" AutoEventWireup="false" Debug="true" %>
In a different thread here on EE, a Master (read: true "Expert") told me if I don't set the AutoEventWireup to true, then the Page_Load event will never fire.  No truer words have ever been said.

I took a huge step backwards.  Fearing something happened to my file (hey, I've seen classic ASP files go corrupt all the time), I created a new .ASPX web form and put just the bare-bones things in there to test it.  No kidding, as soon as I remembered about the AutoEventWireup issue, it started working.

Commented:
Your webconfig connectionstring seems fine. I cannot help you without more information on the connection state and reader while debugging. Screenshots would be a big help.

Note last post may not of masked your credentials and differ from integrated security posted earlier.

<add name="omahavaccineConnectionString" connectionString="Data Source=SERVER33\SQLEXPRESS;Initial Catalog=omahavaccine;Integrated Security=True" providerName="System.Data.SqlClient"/>



Commented:
So no breakpoints were hit!

Author

Commented:
Oh, ...., you're right.  ....!  That wasn't supposed to happen.  Oh, well, it's a public forum.  So I'm gonna delete that User right away.

And I removed the breakpoint when I went for a new (clean) web form.

Author

Commented:
It was neither of these contributors' faults for being partially complete, accurrate, and easy to understand.  They worked through each step in the process with me, but I eventually figured it out.  ANd I thank them for making think of certain things that triggered it.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.