Display a table on a webpage from database

Question

I am creating a website that allows a patient to log in and see their own data. So far when they log in they are redirected to the user.aspx page and a session displays their username on a label from the Patient table( I have included the session information to help with the question) ... But I also want a table to display the patients corresponding medicine information:

The patient table (all tables are dummy data):

The medicine table:

The session after login is authenticated in login.aspx:

Public Function CheckUser(username As String, password As String) As Integer
    Dim cmdstring As String = "SELECT * FROM Patient  Where Username=@USERNAME AND Password=@PASSWORD"
    Dim found = 0
    Using conn As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Laura\Final_proj\App_Data\surgerydb.mdf;Integrated Security=True;Connect Timeout=30")

        Dim cmd = New SqlCommand(cmdstring, conn)
        cmd.Parameters.Add("@USERNAME", SqlDbType.NChar).Value = username
        cmd.Parameters.Add("@PASSWORD", SqlDbType.NChar).Value = password
        conn.Open()



        Dim reader = cmd.ExecuteReader()

        While reader.Read()
            Session("PatientId") = CInt(reader.Item("PatientId"))
            Session("Username") = CStr(reader.Item("Username"))
            found = CInt(reader.Item("PatientId"))
        End While

        reader.Close()
    End Using
    Return (found)
End Function

Label displaying user name in label in user.aspx:

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    Label1.Text = Session("Username")
End Sub

I have another table called prescription (link table) that has composite keys Patientid (from Patient table) and Medicine (from medicine table) - both foreign keys.

When the user logs in how can I get the Medicine table to display showing the user's corresponding medicine and the information from the table (Name, Purpose, Instructions) on user.aspx. Will I do this using a gridview from Toolbox?

Not sure where I am going wrong with the solution here

errors:

did your issue resolve ?

Nazir Ullah
– Nazir Ullah

2016-06-11 19:38:02 +00:00
Commented Jun 11, 2016 at 19:38 — Nazir Ullah
– Nazir Ullah, Commented Jun 11, 2016 at 19:38

Nazir Ullah · Accepted Answer · 2016-06-12 09:03:44Z

1

yea simply add the gridview from toolbox in user.aspx page and run the below line of code on page load event of user.aspx page

Partial Class Pages_user
    Inherits System.Web.UI.Page

    Sub Page_Load(ByVal Sender As System.Object, ByVal e As System.EventArgs)

        If Not IsPostBack Then
            Dim conn As New System.Data.SqlClient.SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Laura\Final_proj\App_Data\surgerydb.mdf;Integrated Security=True;Connect Timeout=30")
            Dim cmdstring As String = "SELECT pt.PatientId, pt.ForeName, pt.Username, md.Name, md.Purpose, md.Instrcutions  " +
                                        "FROM Patient pt INNER JOIN prescription pr ON pt.PatientId = pr.PatientId  " +
                                        "INNER JOIN medicine md ON md.MedicineId = pr.MedicineId Where pt.PatientId  = @PatientId"
            Dim dt As New System.Data.DataTable()
            Dim da As New System.Data.SqlClient.SqlDataAdapter(cmdstring, conn)
            da.SelectCommand.Parameters.Add("@PatientId", System.Data.SqlDbType.Int).Value = CInt(Session("PatientId").ToString())
            conn.Open()
            da.Fill(dt)
            conn.Close()

            GridView1.DataSource = dt
            GridView1.DataBind()
        End If

    End Sub
End Class

And your aspx page code will be

<%@ Page Title="" Language="VB" MasterPageFile="~/Masterpages/MasterPage2.master" AutoEventWireup="true" CodeFile="user.aspx.vb" Inherits="Pages_user" %>

<asp:Content ID="Content1" ContentPlaceHolderID="title" runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="head" runat="Server">
    <style type="text/css">
        .auto-style2 {
            font-size: x-large;
        }
 </style>
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="contentbody" runat="Server" Inherits="Pages_user" CodeFile="Pages_user.aspx.vb">

    <p>
       <span class="auto-style2">Please Select Your Medication&nbsp;&nbsp;&nbsp;
        </span>

    </p>

    <asp:GridView ID="GridView1" runat="server" ></asp:GridView>

</asp:Content>

edited Jun 12, 2016 at 9:03

answered Jun 11, 2016 at 19:07

Nazir Ullah

6105 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

laurajs Over a year ago

Hi Nazir - thank you for taking the time to answer this. I keep getting stuck could you help me abit further? I have provided errors above in the question (new images) not sure where I am going wrong

Nazir Ullah Over a year ago

which line of code throwing error and what error are thrown ,can you share with me

laurajs Over a year ago

Hi Nazir :) I have provided the an image showing the code and errors above

Nazir Ullah Over a year ago

you should put this code in page load event not on top of page

Nazir Ullah Over a year ago

Sub Page_Load(ByVal Sender As System.Object, ByVal e As System.EventArgs) ' put code here End Sub

|

Collectives™ on Stack Overflow

Display a table on a webpage from database

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related