asked on

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)

hilltop

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

sirbounty

ASKER

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!

hilltop

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

sirbounty

ASKER

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

hilltop

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.

ASKER CERTIFIED SOLUTION

hilltop

membership

This solution is only available to members.

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

Start Free Trial

hilltop

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

sirbounty

ASKER

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

sirbounty

ASKER

::bump::

hilltop

How else could I help?

hilltop

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

sirbounty

ASKER

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

sirbounty

ASKER

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.

hilltop

Happy to help.