ASP.NET GRIDVIEW DATASET SQL QUERY

Having trouble figuring out how to get this binded to a datatable and run a foreach query on it.

My goal is to have an initial query ran that returns a list of id numbers from sql. Then it puts it to a datatable where i can run a foreach row value in the datatable and run a sql query and add them to a dataset that is then binded to a gridview in asp.net

Here is what I have so far.


        SqlConnection sqlconnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["outlook"].ConnectionString);
        SqlCommand cmd5 = sqlconnection1.CreateCommand();
        cmd5.CommandText = "SELECT xrefid from outlookreport.dbo.assignloans where userid=@userid" ;

        cmd5.Parameters.AddWithValue("@userid", "test@gmail.com");
        SqlDataAdapter da = new SqlDataAdapter(cmd5);
        sqlconnection1.Open();
        DataTable table;

Open in new window

desiredforsomeAsked:
Who is Participating?
 
Daniel Van Der WerkenConnect With a Mentor Independent ConsultantCommented:
This is actually very simple. At first, I had a difficult time understanding that you don't need to implement the "foreach" yourself with a GridView. It does it for you. Here is a simple page I put together that demonstrates what you need to do. Note that you need to set the
DataSource

Open in new window

and then call
DataBind()

Open in new window

to get what you want done.

MyGridView.aspx:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="MyGridView.aspx.cs" Inherits="WebApplication1.MyGridView" %>

<!DOCTYPE html>

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

Open in new window


MyGridView.aspx.cs:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1
{
    public partial class MyGridView : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            GridView1.DataSource = GetSomeDataTable();
            GridView1.DataBind();
        }

        public DataTable GetSomeDataTable()
        {
            DataTable dt = new DataTable();

            DataColumn c1 = new DataColumn("FirstName", typeof(string));
            DataColumn c2 = new DataColumn("LastName", typeof(string));

            dt.Columns.Add(c1);
            dt.Columns.Add(c2);

            DataRow dr1 = dt.NewRow();
            dr1["FirstName"] = "Mary";
            dr1["LastName"] = "Poppins";

            dt.Rows.Add(dr1);


            dr1 = dt.NewRow();
            dr1["FirstName"] = "Wyatt";
            dr1["LastName"] = "Earp";

            dt.Rows.Add(dr1);

            dr1 = dt.NewRow();
            dr1["FirstName"] = "Count";
            dr1["LastName"] = "Dracula";

            dt.Rows.Add(dr1);

            return dt;
        }
    }
}

Open in new window

0
 
Walter PadrónCommented:
From my experience  a table JOIN will do the job.
see http://technet.microsoft.com/en-us/library/aa213233%28v=sql.80%29.aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.