Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Data from SQLDataReader not being displayed on page

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

0
OVC-it-guy
Asked:
OVC-it-guy
  • 16
  • 10
  • 2
2 Solutions
 
Daniel WilsonCommented:
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

0
 
OVC-it-guyAuthor 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.
0
 
nenwmnCommented:
If Not IsDBNull(reader.item("SKU")) then
     lblSKU.Text = Reader.GetString(Reader.GetOrdinal("SKU"))
End If
 
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
OVC-it-guyAuthor 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.
0
 
nenwmnCommented:
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"), _
"')")
0
 
OVC-it-guyAuthor Commented:
That's right.  No errors, but no data being displayed.  In other words, each of my lblLabel.Text is blank.
0
 
nenwmnCommented:
I see an alias AS product is used and is also a named table in the database.
0
 
nenwmnCommented:
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.
0
 
OVC-it-guyAuthor Commented:
Ok, I swapped the alias product for title (product_main.product_name AS title).  No change (nothing bein displayed).
0
 
nenwmnCommented:
When you call GetProduct() from the page_load pass the request query string in to the sub and confirm a value is being passed.
You can also set an if statement If Reader.HasRows look at the HasRows property in the immediate window does it say "True"
'Page_Load'
GetProduct(Request.QueryString("sku"))
Sub GetProduct(ByVal pSKU as string)

End Sub
From here you should know the SKU is as expected and the reader has rows.

0
 
OVC-it-guyAuthor 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.
0
 
OVC-it-guyAuthor Commented:
Nenwmn,

Would you please take a look at my SqlConnection.  Some else mention that it may not be correct.
0
 
OVC-it-guyAuthor 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?
0
 
nenwmnCommented:
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.
0
 
nenwmnCommented:
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
0
 
OVC-it-guyAuthor Commented:
Giving it a fresh try.
0
 
OVC-it-guyAuthor 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

0
 
nenwmnCommented:
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.


0
 
Daniel WilsonCommented:
Add a  label in which to put error messages (while debugging).  Then in your Catch block put:
MyErrorLabel.text = ex.ToString

I think it may be that something's throwing an exception and that's being hidden.
0
 
OVC-it-guyAuthor 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

0
 
OVC-it-guyAuthor 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.
0
 
OVC-it-guyAuthor Commented:
Please disreguard my last post - wrong thread/forum.
0
 
OVC-it-guyAuthor 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
0
 
OVC-it-guyAuthor 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.
0
 
nenwmnCommented:
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"/>



0
 
nenwmnCommented:
So no breakpoints were hit!
0
 
OVC-it-guyAuthor 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.
0
 
OVC-it-guyAuthor 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.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 16
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now