• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

Reading SQL source and populating table based on current user

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
sirbounty
Asked:
sirbounty
  • 6
  • 6
1 Solution
 
Bob LearnedCommented:
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
 
sirbountyAuthor Commented:
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
 
Bob LearnedCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sirbountyAuthor Commented:
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
 
Bob LearnedCommented:
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
 
sirbountyAuthor Commented:
I made those adjustments, but it still doesn't seem to be reading anything.
Do I still need the data member property set?
0
 
Bob LearnedCommented:
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
 
sirbountyAuthor Commented:
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
 
Bob LearnedCommented:
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
 
sirbountyAuthor Commented:
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
 
Bob LearnedCommented:
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
 
sirbountyAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now