SQL connection will not display on gridview

Question

I have a website and when a user logs in they can see their own information (username and a grid containing information about their medicines). This did load but now since I have moved it only loads the logged in user's username and does not display the grid that held their medicine information.I moved my project file from my laptop on to my college computer and ensured to change the location of where I was loading the data from :

Previously

"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Laura\Final_proj\App_Data\surgerydb.mdf;Integrated Security=True;Connect Timeout=30"

Now:

"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\surgerydb.mdf;Integrated Security=True;Connect Timeout=30"

This should work as the login uses the same data directory source and logs the user in. They are then redirected to the user.aspx page but now the grid does not display

user.aspx page (the query doesnt seem to be even running on this:

  Imports System.Data.SqlClient
  Imports System.Data

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=|DataDirectory|\surgerydb.mdf;Integrated Security=True;Connect Timeout=30")
        Dim cmdstring As String = "SELECT md.MedicineId, md.Name, md.Purpose, md.Instrcutions, DoctorId  " +
                                    "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


Protected Sub GridView1_RowCommand(sender As Object, e As GridViewCommandEventArgs)
    If e.CommandName = "UpdateMedicine" Then
        Dim medecineID As Integer = Integer.Parse(e.CommandArgument.ToString())

    End If
End Sub

The grid:

<asp:Content ID="Content3" ContentPlaceHolderID="contentbody" runat="Server">

<p>

Please Select Your Medication

</p>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"   OnRowCommand="GridView1_RowCommand"  >
  <Columns>
<asp:TemplateField>
<ItemTemplate>
    <asp:LinkButton runat="server" Text="Select" CommandName="UpdateMedicine" CommandArgument='<%# Eval("MedicineId") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Purpose" HeaderText="Purpose" />
<asp:BoundField DataField="Instrcutions" HeaderText="Instructions" />
  </Columns>
</asp:GridView>

</asp:Content>

What it looked like previously:

Hopefully someone can help

kind regards

Did you try `AppDomain.CurrentDomain.SetData`? Check http://stackoverflow.com/questions/1409358/ado-net-datadirectory-where-is-this-documented — Jim Hewitt, Jun 22 '16 at 16:20
Hi @JimHewitt thank you for providing this but the source I am getting the data from should load as it works when it uses the database source for my login in the login page — laurajs, Jun 22 '16 at 16:26
I see `|DataDirectory|` in your connection string. Is that an indication given by someone (e.g. the teacher)? You should probably replace it with the actual folder path. — ConnorsFan, Jun 22 '16 at 16:28
Hi @ConnorsFan - this source works for the login page ` 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=|DataDirectory|\surgerydb.mdf;Integrated Security=True;Connect Timeout=30") ` — laurajs, Jun 22 '16 at 16:34
I just checked the link given by @Jim Hewitt. I didn't know that substitution technique. If it works for your other page, then it must be OK. You would have to see the query itself and the records in `dt`. — ConnorsFan, Jun 22 '16 at 16:39
when I change it to `"Data Source=(LocalDB)\v11.0;AttachDbFilename=P:\Documents\prowork\Final_proj\App_Data\surgerydb.mdf;Integrated Security=True;Connect Timeout=30"` my college file it still wont display the data — laurajs, Jun 22 '16 at 16:42
When I go to run the query it is not recognised - for instance If Iput an error into the query ( a value that will cause an error ie- DoctorId) - it doesnt even pull the error — laurajs, Jun 22 '16 at 16:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115333/discussion-between-connorsfan-and-laurajs). — ConnorsFan, Jun 22 '16 at 16:58
@ConnorsFan - I have updated my code, could you provide your views and knowledge to this question - http://stackoverflow.com/questions/38016746/get-an-insert-query-to-run-after-a-click-event-with-vb-in-asp-net — laurajs, Jun 24 '16 at 15:18

ConnorsFan · Accepted Answer · 2016-06-22T18:42:03.787

After discussion, it turns out that Page_Load was not called, since AutoEventWireup was turned off and the event handler was not specified in code-behind. Either one of these two changes should work:

The event to handle can be specified in code-behind:

Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

or AutoEventWireup can be turned on in the markup file:

<%@ Page AutoEventWireup="true" ... %>

SQL connection will not display on gridview

1 Answers1