laurajs laurajs - 1 year ago 52
SQL Question

Display a table on a webpage from database

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

patient table

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

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

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.

enter image description here

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

enter image description here

enter image description here


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

            GridView1.DataSource = dt
        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 ID="Content2" ContentPlaceHolderID="head" runat="Server">
    <style type="text/css">
        .auto-style2 {
            font-size: x-large;
<asp:Content ID="Content3" ContentPlaceHolderID="contentbody" runat="Server" Inherits="Pages_user" CodeFile="Pages_user.aspx.vb">

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


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