Solved

Data from SQLDataReader not being displayed on page

Posted on 2008-09-30
28
297 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

0
Comment
Question by:OVC-it-guy
  • 16
  • 10
  • 2
28 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22609444
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
 

Author Comment

by:OVC-it-guy
ID: 22609603
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
 
LVL 2

Expert Comment

by:nenwmn
ID: 22609808
If Not IsDBNull(reader.item("SKU")) then
     lblSKU.Text = Reader.GetString(Reader.GetOrdinal("SKU"))
End If
 
0
 

Author Comment

by:OVC-it-guy
ID: 22609878
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
 
LVL 2

Expert Comment

by:nenwmn
ID: 22609930
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
 

Author Comment

by:OVC-it-guy
ID: 22609996
That's right.  No errors, but no data being displayed.  In other words, each of my lblLabel.Text is blank.
0
 
LVL 2

Expert Comment

by:nenwmn
ID: 22610008
I see an alias AS product is used and is also a named table in the database.
0
 
LVL 2

Expert Comment

by:nenwmn
ID: 22610035
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
 

Author Comment

by:OVC-it-guy
ID: 22610055
Ok, I swapped the alias product for title (product_main.product_name AS title).  No change (nothing bein displayed).
0
 
LVL 2

Accepted Solution

by:
nenwmn earned 250 total points
ID: 22610108
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
 

Author Comment

by:OVC-it-guy
ID: 22610874
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
 

Author Comment

by:OVC-it-guy
ID: 22611382
Nenwmn,

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

Author Comment

by:OVC-it-guy
ID: 22611451
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
 
LVL 2

Expert Comment

by:nenwmn
ID: 22612371
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 2

Expert Comment

by:nenwmn
ID: 22612449
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
 

Author Comment

by:OVC-it-guy
ID: 22613123
Giving it a fresh try.
0
 

Author Comment

by:OVC-it-guy
ID: 22613264
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
 
LVL 2

Expert Comment

by:nenwmn
ID: 22613551
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
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 250 total points
ID: 22613552
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
 

Author Comment

by:OVC-it-guy
ID: 22613905
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
 

Author Comment

by:OVC-it-guy
ID: 22614013
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
 

Author Comment

by:OVC-it-guy
ID: 22614058
Please disreguard my last post - wrong thread/forum.
0
 

Author Comment

by:OVC-it-guy
ID: 22614244
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
 

Author Comment

by:OVC-it-guy
ID: 22615401
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
 
LVL 2

Expert Comment

by:nenwmn
ID: 22615645
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
 
LVL 2

Expert Comment

by:nenwmn
ID: 22615784
So no breakpoints were hit!
0
 

Author Comment

by:OVC-it-guy
ID: 22615952
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
 

Author Closing Comment

by:OVC-it-guy
ID: 31501730
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now