asked on

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

Bob Learned

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?

sirbounty

ASKER

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 ;)

Bob Learned

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.

sirbounty

ASKER

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

Bob Learned

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.

sirbounty

ASKER

I made those adjustments, but it still doesn't seem to be reading anything.
Do I still need the data member property set?

Bob Learned

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.

sirbounty

ASKER

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

Bob Learned

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.

sirbounty

ASKER

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">

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sirbounty

ASKER

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.