Solved

Reading SQL source and populating table based on current user

Posted on 2013-11-15
12
357 Views
Last Modified: 2013-11-19
I received this code from another question, and I'm wanting to modify it a bit to suit a different need.
I need that when this page loads, it will read a sql database, passing the current user's logon name.  Sql psuedo query would state "Select MtItems from tbl where owner='<user>'".

And then the list would simply contain the list of those MtItems, but allow the user to click on each to get more details (though I realize this could potentially be a 'part 2' question, which I'm fine with asking).
Thanks for the help!



  
    <form id="form1" runat="server">
    <div>
        Distibution Lists:<br />
        <asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True"></asp:ListBox>
        <% If ListBox1.SelectedValue <> "" Then%>
        <hr />
        Details:<br />
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="btnSave" runat="server" Text="Save" />
        <% End If%>
    </div>
    </form>
    

Open in new window

0
Comment
Question by:sirbounty
  • 6
  • 6
12 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39652818
This doesn't seem like good code, so I am not sure how to help you.  Do you have code-behind with this page?  Are you looking for ways to execute the query?
0
 
LVL 67

Author Comment

by:sirbounty
ID: 39653380
I'm willing to switch out the code, as I haven't really gotten started with this piece.
Queries I can pretty much handle, it's the generation/placeholders for the tables that confuse me (remember, I'm more of a win forms kind of guy ;)
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39653468
I will try to find some examples that explain what you need to do.

Working With ListBox using ASP.NET and Csharp
http://mrbool.com/working-with-listbox-using-asp-net-and-csharp/25298

Code-behind code converted to VB.NET:

Protected Sub Page_Load(sender As Object, e As EventArgs)
	If Not IsPostBack Then
		Dim oDs As DataSet = ReadDataSet()
		lstBrowser.DataTextField = "Name"
		lstBrowser.DataValueField = "ID"
		lstBrowser.DataSource = oDs
		lstBrowser.DataBind()
	End If
End Sub

Open in new window


ASP.NET is for the most part stateless, so the server doesn't keep track of past browser requests.  There are things like View State, Caching, and Session variables, that help to keep track of page state.  

The usual approach to loading data into a control is to check for initial page load (Not IsPostBack), and load the data.   Usually, the state of the control will be kept in view state, so that you don't have to rebind on post-back.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 39657329
I still don't like web coding, but... I've got a book... :^)

Ok, I put all of that into my page, along with the code to read from the db, but it's not working... I get a blank list.  For this first step, I really just need a list of all items (MMRecord) belong to OwnerID (don't need to display the ownerID).

Imports System.Data
Imports System.Data.SqlClient

Partial Class manage
    Inherits System.Web.UI.Page
    Private objDataDL As DataTable

    Protected Sub Page_Load(sender As Object, e As EventArgs)
        If Not IsPostBack Then
            Dim oDs As DataSet = ReadDataSet()
            lstBrowser.DataTextField = "OwnerID"
            lstBrowser.DataValueField = "MMRecord"
            lstBrowser.DataSource = oDs
            lstBrowser.DataBind()
        End If
    End Sub

    Private Function ReadDataSet() As DataSet
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim connString As New SqlConnection(ConfigurationManager.ConnectionStrings("MMList").ConnectionString)

        Dim OwnerID As String = "******"
        da.SelectCommand = New SqlCommand("SELECT * FROM tblMMLists WHERE OwnerID='" & OwnerID & "';", connString)
        da.Fill(ds, "tblMMLists")
        dt = ds.Tables("tblMMLists")

        Return ds
    End Function
End Class

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39657353
If you set the DataSource property to a DataSet, then you also need to set the DataMember property to the desired table name.  

I would use the SqlDataAdapter to fill a DataTable, and then use that for the DataSource, since you only have a single table for a data source.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 39657422
I made those adjustments, but it still doesn't seem to be reading anything.
Do I still need the data member property set?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 39657490
DataSet needs DataMember
DataTable does not need DataMember

If you haven't used the DataSet/DataTable visualizer, you can put a breakpoint in the method where the data is filled, and hover over the DataTable, and show the records for the query.  

DataTable Visualizer
0
 
LVL 67

Author Comment

by:sirbounty
ID: 39659249
I hate to be a pain, I really do, but I can't get it to break anywhere.  I even tried just adding a dummy table and returning that, but it won't allow me to highlight or break at that point.

Eventually, I need it to read the current user, but for now I'm hard coding the OwnerID, and I've tested the query on the db server with no issues.  Sadly, it still shows an empty box.

I expect this to be, as usual with me, a long series of questions, to get this working.  I feel like I could carve this out in a win form app in about 30 minutes, but I feel like a freshman in high school when coding web apps... :(  
Goal is to present to the user a listing of all managed distribution lists owned by them, and then utlimately allow them to modify or delete them from this page.  So this is merely step 1, allowing them to see a list of all they own.  For this test scenario, I only own a single record.  I doubt that's the issue, but I bring it up, just in case, or if it seems like I'm veering in a wrong direction...

 Private Function ReadDataSet() As DataTable
        Dim connString As New SqlConnection(ConfigurationManager.ConnectionStrings("MMList").ConnectionString)
        Dim da As New SqlDataAdapter("SELECT * FROM tblMMLists WHERE OwnerID='******';", connString)
        Dim ds As New DataSet
        da.Fill(ds, "tblMMLists")

        Return ds.Tables("tblMMLists")
    End Function

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39659264
What type of web site are you working with?

Web Site model = no project file
Web Application model = project file

If you can't place breakpoints, then you need to check if you have the Debug configuration or Release configuration.

Another debug step is to put some information into a Label on the page.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 39659339
Apparently the wrong one?  I don't see a project file...

It's in debug configuration, and I also have this set in my web.config...
<compilation debug="true" strict="false" explicit="true" targetFramework="4.0">
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 39659559
When I create a web site, I always choose the Web Application model (File > New > Project > Visual Basic > Web).  The Web Site model was never a good idea, since you don't have as much control over the configuration (everything in the folder is considered part of the web site).

There are few reasons for why debug breakpoints are not getting hit--too many to list all here.

Some indicators:

Hovering over the breakpoint it says "The breakpoint will not currently be hit the source code differs from the original version"

The breakpoint indicators are not solid red circles

If haven't invested much time in your web site, I would suggest creating a new web application, and the issues might go away.
0
 
LVL 67

Author Closing Comment

by:sirbounty
ID: 39660027
I don't even see web site listed... I imagine I would have selected the web application route (though I have no problem starting from scratch either).

I think I'll park it here for now.  I'm going to try to create it as a winform app, so that I know what backend functionality I need to investigate (some AD and Exchange operations, some using powershell).  Once I get that working, I'll probably revisit this, assuming I have a better handle on how to approach it.
Thanks again for your help - hope you have a nice Thanksgiving.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

706 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

13 Experts available now in Live!

Get 1:1 Help Now