Populating a grid view with data from a stored procedure

Question

dougancil 0 Junior Poster in Training

15 Years Ago

I have the following code in my asp page:

Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dt As DataTable
        Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim con As New SqlConnection(connectionString)
        Dim cmd As New SqlCommand
        cmd.Connection = con
        con.Open()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "getoncall"
        cmd.Parameters.AddWithValue("@subschedule", TextBox1.Text)
        cmd.ExecuteNonQuery()
        Using da As New SqlDataAdapter(cmd)
            dt = New DataTable
            da.Fill(dt)
            GridView1.DataSource = dt
            GridView1.EmptyDataText = "No Records Found"
            GridView1.DataBind()
            con.Close()
            con.Dispose()
        End Using
    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        TextBox1.Text = ""
    End Sub
End Class

and while the page doesn't throw me any errors, I'm also not seeing any data in my datatable. Can anyone maybe see why?

Thank you

.net:vb.net database-design microsoft:sql-server

2 Contributors
3 Replies
585 Views
23 Hours Discussion Span
Latest Post 15 Years Ago Latest Post by jbisono

Dani AI

Generated 10 Months Ago

Short summary: an empty GridView usually means either the button handler never ran, the database call never executed correctly, or the stored procedure returned no rows. Common culprits in this thread are a missing command->connection/adapter association and an empty Try/Catch that swallows errors.

Checks and quick diagnostics:

Confirm the page event is wired: the control ID in the .aspx must match the code-behind handler. With AutoEventWireup="false" either the handler must be wired (e.g. OnClick="whosoncallButton_click") or the designer must declare the control WithEvents.
Remove the empty Catch or log ex.Message so any SQL/connection errors surface. Silent failures hide the real problem.
If a SqlDataAdapter is used, ensure it has a SelectCommand (or was constructed with the SqlCommand). If SqlCommand.Connection is never set, Fill will return nothing.
Verify the stored procedure returns rows for the exact parameter value by running it in SSMS. Check parameter types/lengths — AddWithValue can infer the wrong type.
After filling, check dt.Rows.Count before binding (use a Label/Trace) to know whether the problem is data or binding.

Example (safe, explicit pattern using a reader and explicit parameter type):

Dim tbl As New DataTable()

Using cn As New SqlConnection("Initial Catalog=mdr;Data Source=SERVER;User Id=USER;Password=PWD;")
    Using cmd As New SqlCommand("getoncall", cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@subschedule", SqlDbType.NVarChar, 100).Value = TextBox1.Text.Trim()
        cn.Open()
        Using rdr As SqlDataReader = cmd.ExecuteReader()
            tbl.Load(rdr)
        End Using
    End Using
End Using

If tbl.Rows.Count > 0 Then
    GridView1.DataSource = tbl
    GridView1.DataBind()
Else
    GridView1.EmptyDataText = "No Records Found"
    GridView1.DataBind()
End If

Notes tied to earlier replies: was right that ExecuteNonQuery is unnecessary when filling a DataSet/DT, and that adapter/connection handling is automatic when used correctly. In 's updated code the command was not associated with the connection/adapter and exceptions were swallowed — those two points explain an empty result even when the page shows no error.

All 3 Replies

jbisono 51 Posting Pro in Training

15 Years Ago

First, you dont need this line cmd.ExecuteNonQuery()
Second, when using fill a dataset thru an adapter you dont need to open and close the connection that is done automatically.

everything looks good just take out the cmd.ExecuteNonQuery()

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

dougancil 0 Junior Poster in Training · Answer 1 · 2011-03-08T03:39:57+00:00

jbisono,

I've modified the code as you suggested:

Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub whosoncallButton_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles whosoncallButton.Click
        Dim dt As DataTable
        Dim da As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim con As New SqlConnection(connectionString)
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "getoncall"
        cmd.Parameters.AddWithValue("@subschedule", TextBox1.Text)
        Try
            dt = New DataTable
            da.Fill(dt)
            GridView1.DataSource = dt
            GridView1.EmptyDataText = "No Records Found"
            GridView1.DataBind()
            con.Dispose()
        Catch ex As Exception
        End Try
    End Sub

    Protected Sub clearButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearButton.Click
        TextBox1.Text = ""
    End Sub
End Class

and I'm still not seeing anything on my page

here is the aspx portion of the page ... as you can see ... there's not much to it:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!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">
<head runat="server">
    <title>Untitled Page</title>
    <style type="text/css">
        .style1
        {
            text-align: center;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div class="style1">
        <br />
        <br />
        <br />
        <br />
        <br />
        Enter Your Schedule Name Below<br />
        <br />
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <br />
        <br />
        <asp:Button ID="whosoncallButton" runat="server" Text="Who's On Call" />
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:Button ID="clearButton" runat="server" Text="Clear" />
        <br />
        <br />
        <br />
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
    </div>
    </form>
</body>
</html>

jbisono 51 Posting Pro in Training · Answer 2 · 2011-03-08T19:08:20+00:00

Uhmm to be honest im more with c# but i always say that it is the same thing, in the only other thing that i can tell you is, in your aspx page the button whosoncallbutton you have to add the property onClick="whosoncallButton_click"

also, check again the connection, the name of the stored procedure and the parameters.

if that does not work instead of filling a datatable try to fill a dataset and add the dataset as datasource, just to test.

Populating a grid view with data from a stored procedure

Dani AI

Recommended Answers Collapse Answers

All 3 Replies

Recommended Answers