CODEWITHSUNDEEP
c#.netasp.netsqlgridview
Nico
Nico

Reputation: 237

Custom SQL in a Gridview

How do I put data into a gridview using programming code? I need an SQL statement which has variables:

"SELECT Mod_Naam, Mod_Omschrijving FROM Model WHERE Mod_ID = " + modid + " ", con

I tried the wizard but I can't use variables in there so I have to use programming code (Never done this before and don't know how)

Upvotes: 0

Views: 1888

Answers (3)

Steven Ryssaert
Steven Ryssaert

Reputation: 1967

First, you need to get your data from your database in a Type that a GridView can handle. A DataTable would do just fine.

The following code fills a DataTable "Models" with the data, retrieved from the query.

// Assuming 'connection' is a valid connection to your database
string modid = "134"; // or int modid = 134;
string query = "SELECT Mod_Naam, Mod_Omschrijving 
                FROM Model 
                WHERE Mod_ID = " + modid;
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
DataSet models = new DataSet();
adapter.Fill(models , "Models");

Next, you want the data to be attached to your GridView. You can put this in the Page_Load of your page, and in the if(!Page.IsPostBack) region if you only want to load the data on load of the page, but not on postback.

if (models.Tables.Count > 0)
{
    myGridView.DataSource = models;
    myGridView.DataBind();
}

Please make note that it is best to make your queries safe from SQL Injections

For more information on running safe queries; I'd refer you to the MSDN page about prepared statements.

Upvotes: 2

Muhammad Akhtar
Muhammad Akhtar

Reputation: 52241

using (SqlConnection c = new SqlConnection(
    Properties.Settings.Default.DataConnectionString))
    {
    c.Open();
    // Create new DataAdapter
    using (SqlDataAdapter a = new SqlDataAdapter("SELECT Mod_Naam, Mod_Omschrijving FROM Model WHERE Mod_ID = @modid ", c))
    {
        a.SelectCommand.Parameters.AddWithValue("modid", "");

        // Use DataAdapter to fill DataTable
        DataTable t = new DataTable();
        a.Fill(t);

        // GridView1.DataSource = t; // <-- From your designer
    }
    }

Upvotes: 3

FIre Panda
FIre Panda

Reputation: 6637

Your could do something like

    SqlConnection con = new SqlConnection("");//put connection string here
    SqlCommand objCommand = new SqlCommand("SELECT FirstName, LAstName from MyTable where FirstName = '" + "ABC" + "'", con);//let MyTable be a database table
con.Open();
    sqlDataReader dr = objCommandExecuteReader();
myGridView.DataSource = dr;//let myGridView be your GridView name
myGridView.DataBind();

Upvotes: 2

Related Questions