4 Replies - 637 Views - Last Post: 10 January 2016 - 01:38 PM Rate Topic: -----

#1 lboyce72  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 18-August 15

VB show database in gridview - not working

Posted 09 January 2016 - 08:10 PM

I want to show information from a database in a gridview. To keep the gridview simple, I am only using the gridview control. The problem is my data is not showing. I am not very well versed in VB, but I have all my work listed below to ask for help, what I am missing in my code. Appreciate any help!

Thanks Larry

Code behind

Imports System.Data, System.Data.OleDb
Partial Class ShowRegistration
    Inherits System.Web.UI.Page

    'Show registration information in a gridview
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter
        Dim cmd As New OleDbCommand
        Dim Conn As New OleDbConnection
        Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Larry\Documents\Visual Studio 2010\WebSites\Week4Ex01VBBoyce\Users.mdb;"
        Conn.Open()
        cmd.Connection = Conn
        cmd.CommandText = "SELECT * from Users"
        Conn.Close()

        da.SelectCommand = cmd
        da.Fill(dt)
        GridViewRegistration.DataSource = dt
        GridViewRegistration.DataBind()

    End Sub



and the webpage

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="ShowRegistration.aspx.vb" Inherits="ShowRegistration" %>
    <%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<body>
    <form id="form1" runat="server">
    <div>
        
        <asp:GridView ID="GridViewRegistration" runat="server" AutoGenerateColumns="true" />

            <br />
            <br />
            <br />
        <asp:Button ID="btnRegistration" runat="server" Text="Return to previous page" />
    </div>
    </form>
</body>
</html>



Is This A Good Question/Topic? 0
  • +

Replies To: VB show database in gridview - not working

#2 maceysoftware  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 350
  • View blog
  • Posts: 1,508
  • Joined: 07-September 13

Re: VB show database in gridview - not working

Posted 10 January 2016 - 05:24 AM

Your opening and closing your connection before attempting to to fill your datatable, as the connection is currently closed it won't work.

Imports System.Data, System.Data.OleDb
Partial Class ShowRegistration
    Inherits System.Web.UI.Page

    'Show registration information in a gridview
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter
        Dim cmd As New OleDbCommand
        Dim Conn As New OleDbConnection
        Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Larry\Documents\Visual Studio 2010\WebSites\Week4Ex01VBBoyce\Users.mdb;"
        Conn.Open() ' Opening the connection
        cmd.Connection = Conn
        cmd.CommandText = "SELECT * from Users"
        Conn.Close() ' Closing the connection

        da.SelectCommand = cmd
        da.Fill(dt) ' actually trying to get data from the database but the connection is closed
        GridViewRegistration.DataSource = dt
        GridViewRegistration.DataBind()

    End Sub


Was This Post Helpful? 0
  • +
  • -

#3 lboyce72  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 18-August 15

Re: VB show database in gridview - not working

Posted 10 January 2016 - 09:01 AM

I appreciate your input so I moved the closing statement, but unfortunately I still have the same result. Any other idea what I may be missing here?

Thanks, Larry

Imports System.Data, System.Data.OleDb
Partial Class ShowRegistration
    Inherits System.Web.UI.Page

    'Show registration information in a gridview
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter
        Dim cmd As New OleDbCommand
        Dim Conn As New OleDbConnection
        Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Larry\Documents\Visual Studio 2010\WebSites\Week4Ex01VBBoyce\Users.mdb;"
        Conn.Open()
        cmd.Connection = Conn
        cmd.CommandText = "SELECT * from Users"
        da.SelectCommand = cmd
        da.Fill(dt)
        'Moved close statement here
        Conn.Close()
        GridViewRegistration.DataSource = dt
        GridViewRegistration.DataBind()

    End Sub


Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • blow up my boots
  • member icon

Reputation: 6450
  • View blog
  • Posts: 26,093
  • Joined: 12-December 12

Re: VB show database in gridview - not working

Posted 10 January 2016 - 10:56 AM

I just want to advise that it is not necessary to open and close the connection, the Fill method of the adapter does that:

Quote

The Fill method retrieves the data from the data source using a SELECT statement. The IDbConnection object associated with the select command must be valid, but it does not need to be open. If the IDbConnection is closed before Fill is called, it is opened to retrieve data and then closed. If the connection is open before Fill is called, it remains open.

This is a common misunderstanding.
Was This Post Helpful? 1
  • +
  • -

#5 lboyce72  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 18-August 15

Re: VB show database in gridview - not working

Posted 10 January 2016 - 01:38 PM

I was working on another page today and used the same code, but with a button_click as opposed to page_load and it worked. With that I was able to figure out, I did not have a handle event

Old
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)



New
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load



Andrewsw, thank you for the input! I took the connection code out and you are correct, it still works.

Larry
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1