ASP.net form to read from SQL

I'm creating a simple form in asp.net.
On page load, I want to display the user's name (I'll get to that later if I can't figure it out )
Next I want to retrieve a list of elements from a SQL table that belong to this user.
If there are any results returned, I want to get a total and display a label that says "You have X items" and then show a checked listbox with the items.
If there are no results, no list would be displayed and a message stating that they have no items.

I can handle the  logic for displaying everything, but it's been so terribly long since I've done any development in asp.net, that I don't really know where to begin to make that SQL connection and pull back results.
I would need to pass credentials because the user's account won't have permissions to read the data.
But the table layout will have a field called Owner that would be equivalent to their user ID that I'm returning from the windows login identity.

If someone could help me get started, I think I can carry this on to completion, as presumably it will come back to me... (I hope)
LVL 67
sirbountyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hilltopCommented:
I personally like to use a combination of stored procedure, cookies and asp. Assume you have a table with two fields userid, pass, and an html form with two inputs named uid and pass. It seems like overkill, but the convenience of using stored procedures will shorten your dev time, expand functionality, etc...

T-SQL Stored procedure
USE [yourdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[lookuser] (
    @userid nvarchar(50),
	@pass nvarchar(50)
 )
AS
SET NOCOUNT ON

--look up your stuff
select userid, pass from youruserstable where userid = @userid and pass = @pass

Open in new window



ASP Form processor
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<% 

SqlConnection Conn = new SqlConnection("Server=yourserver;Database=testdb;UID=user;PWD=password;");
Conn.Open();

String uid = Request["uid"].ToLower();
String pass = Request["pass"];

SqlCommand lookcmd = new SqlCommand("lookuser @userid='" + uid + "',@pass='" + pass + "'", Conn);
    SqlDataReader lookreader = lookcmd.ExecuteReader();
    while (lookreader.Read()) { 
      
       Response.Write(lookreader["userid"].ToString());
       Response.Write(lookreader["pass"].ToString());
}
    lookreader.Close();
    lookcmd.Dispose();
   Conn.Close();
%>

Open in new window


Setting and reading cookies. These days most everyone will use session cookies, but I prefer good old fashion cookies as they are compatible with web farms and multi server setups out of the box.
//Sets a cookie
Response.Cookies["cookiename"]["uid"] = "username");
Response.Cookies["cookiename"]["pass"] = "password";
Response.Cookies["cookiename"].Expires = DateTime.Now.AddMinutes(15);

//Reads a cookie
Request.Cookies["mintdns"]["uid"].ToString();
Request.Cookies["mintdns"]["pass"].ToString();

Open in new window

sirbountyAuthor Commented:
Good idea on the sproc - I've just set that up, however I'm not sure I quite explained properly.
I'm not passing user id and password to the db.
Basic user John would need to access the table to retrieve records where his employee ID is under field Owner.
So, I would need the web site to either use specific credentials, or is there a way to give everyone read access to a sproc?  Maybe that's the easiest solution...  I realize that's a separate permission issue.

I've gotten to the code part now - but I'm not sure how I connect that code with my page_load declaration of user.  In my page load, I'm dropping the employee ID into lblUser.  How would I reference that as you have your uid above?
I see you've referenced Request - but I don't recognize where that's pulled from.  And I'm afraid I'm completely lost when it comes to where to place the cookie code you've shown.

Thanks for the help so far!
hilltopCommented:
Yeah you can do just about anything with this setup. there is no need to pass a stored procedure anything. It is overkill for a simple lookup. I just prefer to use all stored procedures for my SQL.

USE [yourdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[lookuser] 
AS
SET NOCOUNT ON

--look up your stuff
select * from youruserstable

Open in new window


SqlCommand lookcmd = new SqlCommand("lookuser'", Conn);
    SqlDataReader lookreader = lookcmd.ExecuteReader();
    while (lookreader.Read()) { 
      
       Response.Write(lookreader["userid"].ToString());
       Response.Write(lookreader["pass"].ToString());
}
    lookreader.Close();
    lookcmd.Dispose();

Open in new window


You could also just use
SqlCommand lookcmd = new SqlCommand("select * from yourtable", Conn);
    SqlDataReader lookreader = lookcmd.ExecuteReader();
    while (lookreader.Read()) { 
      
       Response.Write(lookreader["userid"].ToString());
       Response.Write(lookreader["pass"].ToString());
}
    lookreader.Close();
    lookcmd.Dispose();

Open in new window

Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

sirbountyAuthor Commented:
I like the stored procedure approach actually - I can use it in some other relevant code for this same app.
I suppose I need a bit more help with the logic of filling out the table when the user visits the site so that they can see their records (if they have any).
I want it to be a checked list box, because I'd like for them to take action on any selected items (they can choose to update only one, delete one or multiple).
hilltopCommented:
Here is a pagination example. The sky is the limit. See my post at the bottom of this article.

https://www.experts-exchange.com/questions/29124798/Paginate-database-records-using-stored-procedure.html

You can do tons of stuff using this approach like accessing COM, sending mail etc.., all from within SQL server. I often do complex work, but pass pages back simple replies. For me it makes for easier management and is just more powerful in my opinion.
hilltopCommented:
In the IDE. Default form with listview
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApplication10
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection Conn = new SqlConnection("Server=yourserver;Database=testdb;UID=user;PWD=password;");
            Conn.Open();
            SqlCommand lookcmd = new SqlCommand("lookuser", Conn);
            SqlDataReader lookreader = lookcmd.ExecuteReader();
            while (lookreader.Read())
            {
                listBox1.Items.Add(lookreader["userid"].ToString());
                listBox1.Items.Add(lookreader["pass"].ToString());
            }
            lookreader.Close();
            lookcmd.Dispose();
            Conn.Close();
           
        }
    }
}

Open in new window


Or you could just write html with your replies etc..
SqlCommand lookcmd = new SqlCommand("select * from yourtable", Conn);
    SqlDataReader lookreader = lookcmd.ExecuteReader();
    while (lookreader.Read()) { 
      Response.Write("<input type="checkbox" />" + lookreader["userid"].ToString() + " <br />");
       Response.Write("<input type="checkbox" />" + lookreader["pass"].ToString() + " <br />");
}
    lookreader.Close();
    lookcmd.Dispose();

Open in new window


Here is a working page with a table.
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<% 

SqlConnection Conn = new SqlConnection("Server=yourserver;Database=testdb;UID=user;PWD=password;");
Conn.Open();
%>
<table>
<td>User</td><td>pass</td>
<%
SqlCommand lookcmd = new SqlCommand("lookuser", Conn);
    SqlDataReader lookreader = lookcmd.ExecuteReader();
    while (lookreader.Read()) { 
      %>
<tr>
<td><%=lookreader["userid"].ToString()"></td>
<td><%=lookreader["pass"].ToString()"></td>
</tr>
<%

}
    lookreader.Close();
    lookcmd.Dispose();
   Conn.Close();
%>
</table>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hilltopCommented:
I am not sure I understood your response, but if it was related to writing to the DB then you should know that the same query can be used to write data. Just use an insert in the stored procedure etc.. I also like to use transactions when I write or update records. You will notice in the example below.

Like This

T-SQL Insert stored procedure
USE [yourdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sampleinsert] (
    @userid nvarchar(50),
	@pass nvarchar(50)
   )
AS
SET NOCOUNT ON
DECLARE @result nvarchar(5)

IF NOT EXISTS (SELECT top 1 userid FROM yourtable with (Nolock) WHERE userid = @userid and pass = @pass) BEGIN
BEGIN TRY
BEGIN TRANSACTION genericname
INSERT INTO yourtable
(userid, pass) 
VALUES (@userid, @pass) 
END TRY
BEGIN CATCH
IF(@@TRANCOUNT > 0)
ROLLBACK TRANSACTION genericname
SET @result = 'lock' 
SELECT @result as 'action'
RETURN
END CATCH
SET @result = 'ok' 
SELECT @result as 'action'
RETURN
END
SET @result = 'exist' 
SELECT @result as 'action'

Open in new window


And a sample insert.aspx
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<% 

SqlConnection Conn = new SqlConnection("Server=yourserver;Database=testdb;UID=user;PWD=password;");
Conn.Open();

String uid = Request["uid"].ToLower();
String pass = Request["pass"];

SqlCommand lookcmd = new SqlCommand("sampleinsert @userid='" + uid + "',@pass='" + pass + "'", Conn);
    SqlDataReader lookreader = lookcmd.ExecuteReader();
    while (lookreader.Read()) { 
      if(lookreader["action"].ToString() == "ok"){Response.Write("ok");}
       if(lookreader["action"].ToString() == "exist"){Response.Write("exist");}
       if(lookreader["action"].ToString() == "lock"){Response.Write("lock");}
}
    lookreader.Close();
    lookcmd.Dispose();
   Conn.Close();
%>

Open in new window

sirbountyAuthor Commented:
Yeah, remember when I said it's been a long time since I've worked with ASP.Net? :)

My code is throwing an error on <table>
Runtime error: Only content controls are allowed directly in a content page that contains content controls.

<%@ Page Title="BDManager" Language="VB" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.vb" Inherits="BDL._Default" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<%
    Dim conn as SqlConnection : conn = new SqlConnection("Server=******;Database=****")
    conn.Open()
    %>
<table> '<<< error here
    <tr>    <td>ID</td>    <td>Display Name</td>    <td>Owner</td>    <td>Backup</td>    <td>Created</td>     <td>Expiration</td>    </tr>
    <%
    Dim uid as string = Request("userID").ToLower()
    Dim cmd as SqlCommand = new SqlCommand("sp_GetOwner @userID='" + uid + "'",conn)
    Dim reader as SqlDataReader = cmd.ExecuteReader()
    while (reader.read()) 
        response.Write(reader("id"))
    end While
    reader.Close()
    reader.Dispose()
    conn.Close()
    %>
</table>
<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">
....

Open in new window

sirbountyAuthor Commented:
::bump::
hilltopCommented:
How else could I help?
hilltopCommented:
To solve that error be sure your code is enclosed by <asp:Content runat="server">, like below.

<%@ Page Title="BDManager" Language="VB" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.vb" Inherits="BDL._Default" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<asp:Content ID="yourid" runat="server" ContentPlaceHolderID="yourid">

<%    SqlConnection Conn = new SqlConnection("Server=yourserver;Database=testdb;UID=user;PWD=password;");
    Conn.Open();
%>
<table>
<td>User</td><td>pass</td>
<%
SqlCommand lookcmd = new SqlCommand("lookuser", Conn);
    SqlDataReader lookreader = lookcmd.ExecuteReader();
    while (lookreader.Read()) { 
      %>
<tr>
<td><%Response.Write(lookreader["userid"].ToString());%></td>
<td><%=lookreader["pass"].ToString()%></td>
</tr>
<%

}
    lookreader.Close();
    lookcmd.Dispose();
   Conn.Close();
%>
</table>
</asp:Content>

Open in new window

sirbountyAuthor Commented:
Looks to be an issue with the account.
I can't use integrated security or sspi since this will be coming from the web server.
I suppose I need to figure out how to pass different (domain) credentials to my connection string
sirbountyAuthor Commented:
Thanks - I was able to get what I needed to get started on this.  I'll likely post for some more help in the near future.  I appreciate the assistance.
hilltopCommented:
Happy to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.