CODEWITHSUNDEEP
c#sql-serverado.net
BingoDingo
BingoDingo

Reputation: 71

Displaying SQL Server table data in webform

I have an application which is supposed to allow a user to view pets from a table in the database.

Picture of web form design and Pets data table: Picture of web form design and Pets data table

Here is my code for the button:

protected void viewAnimalsBreedButton_Click(object sender, EventArgs e)
{
    try
    {
        SqlConnection cnn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\FrendsWithPaws.mdf;Integrated Security=True");

        cnn.Open();
        SqlCommand command = new SqlCommand("SELECT PetID, Breed, Name, Age, Gender, Picture, Sanctuary FROM Pets WHERE Breed='+ breedDropDownList.SelectedValue +'", cnn);
        SqlDataReader reader = command.ExecuteReader();
        petsGridView.DataSource = reader;
        petsGridView.DataBind();
        cnn.Close();
    }
    catch (Exception ex)
    {
        Response.Write("error" + ex.ToString());
    }
}

To start with I have a dropdownlist for the pets breed, when I select a breed in the dropdown and click view animals, I would like the gridview to show me the pets (with most of the contained information) with this breed... I would then like this to work for Species and Sanctuary...

Currently when I select a breed and click view animals nothing happens, as shown in the picture below:

Picture of web form after selection of 'House' breed and click of view animals button: Picture of web form after selection of 'House' breed and click of view animals button

How do I get this to work?

Upvotes: 1

Views: 1562

Answers (2)

Serhat Oz
Serhat Oz

Reputation: 798

You have to load read data to datatable first:

protected void viewAnimalsBreedButton_Click(object sender, EventArgs e)
{
    try
    {
        SqlConnection cnn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\FrendsWithPaws.mdf;Integrated Security=True");

        cnn.Open();
        SqlCommand command = new SqlCommand("SELECT PetID, Breed, Name, Age, Gender, Picture, Sanctuary FROM Pets WHERE Breed='" +  breedDropDownList.SelectedValue + "'", cnn);
        SqlDataReader reader = command.ExecuteReader();
        var dataTable = new DataTable();
        dataTable.Load(dataReader);
        petsGridView.DataSource = dataTable;
        petsGridView.DataBind();
        cnn.Close();
    }
    catch (Exception ex)
    {
        Response.Write("error" + ex.ToString());
    }
}

Upvotes: 0

Salah Akbari
Salah Akbari

Reputation: 39966

Firstly you should always use parameterized queries to avoid SQL Injection and getting rids of this kind of issues. And secondly, you need to create a DataTable and fill it though data reader and bind your table to grid:

cnn.Open();
SqlCommand command = new SqlCommand("SELECT PetID, Breed, Name, Age, Gender, Picture, " +
                                    "Sanctuary FROM Pets where Breed = @Breed ", cnn);

command.Parameters.AddWithValue("@Breed", breedDropDownList.SelectedValue);

DataTable table = new DataTable();
table.Load(command.ExecuteReader());    
petsGridView.DataSource = table;
petsGridView.DataBind();
cnn.Close();

Although specify the type directly and use the Value property is more better than AddWithValue. https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

Upvotes: 2

Related Questions