Inserting a single selected row in gridview into database ASP.net

Question

I have run into a little problem that has stomped me a little bit. I am using a gridview that is connected to a datasource that displays the data. What I am trying to do is move a row that I would like into another table. The code I currently have now, inserts all rows on the click instead of just the selcted row. Here is the code for my button click..

    protected void lbSelect_OnClick(object sender, EventArgs e)
    {

        conn.Open();
        tran = conn.BeginTransaction();
        cmd.Transaction = tran;
        //string slno = null;

        try
        {
            foreach (GridViewRow g1 in gvVehicleImport.Rows)
            {
                string StockNumber = (g1.FindControl("lblStockNumber") as Label).Text;
                string SalesPerson = (g1.FindControl("lblSalesPerson") as Label).Text;
                string Buyer = (g1.FindControl("lblBuyer") as Label).Text;
                string GrossProfit = (g1.FindControl("lblGrossProfit") as Label).Text;
                string DealDate = (g1.FindControl("lblDealDate") as Label).Text;
                string Make = (g1.FindControl("lblMake") as Label).Text;
                string Model = (g1.FindControl("lblModel") as Label).Text;
                string CarTruck = (g1.FindControl("lblCarTruck") as Label).Text;
                string NewUsed = (g1.FindControl("lblNewUsed") as Label).Text;
                string Lender = (g1.FindControl("lblLender") as Label).Text;
                string AmtFinanced = (g1.FindControl("lblAmtFinanced") as Label).Text;
                string RetailLease = (g1.FindControl("lblRetailLease") as Label).Text;
                string BankName = (g1.FindControl("lblBankName") as Label).Text;
                string Status = (g1.FindControl("lblStatus") as Label).Text;
                string ChangedBy = (g1.FindControl("lblChangedBy") as Label).Text;

                string query = "INSERT INTO Vehicle VALUES('" + StockNumber + "','" + SalesPerson + "','" + Buyer + "','" + GrossProfit + "','" + DealDate + "','" + Make + "','" + Model + "','" + CarTruck + "','" + NewUsed + "','" + Lender + "','" + AmtFinanced + "','" + RetailLease + "','" + BankName + "','" + Status + "','" + ChangedBy + "')";
                //slno = StockNumber;
                cmd.CommandText = query;
                cmd.ExecuteNonQuery();
            }


            tran.Commit();
            conn.Close();
            lblImportMessage.Text = "Row move successful.";


        }
        catch (Exception ex)
        {
            tran.Rollback();
            lblImportMessage.Text = "Row move was unsuccessful, " + ex.ToString();
        }

I am calling the labels to insert into the database which works well, but it inserts every row, not just the selected one. Any thoughts would be great! Thanks!

Please use parameterized queries! – Nikita Silverstruk May 21 '13 at 17:56 — Nikita Silverstruk, May 21 '13 at 17:56

score 2 · Answer 1 · edited May 23 '17 at 11:57

2

You could use the SelectedRow property of the GridView
Remove the loop and use something like this

GridViewRow g1 = gvVehicleImport.SelectedRow;
if(g1 != null)
{
   ... // code to insert
}

Said that, please remove ASAP the string concatenation that builds your Sql command and use a parameterized query. This will allow you to avoid Syntax Error (when your input text contains a single quote) and Sql Injection where a malicious user try to wreak havoc your db

edited May 23 '17 at 11:57

Community

1
1

answered May 20 '13 at 20:47

Steve

213,761
22
232
286

Awesome. I think I tried a variation of that at one point but I will definitely give it another try in the morning! Thanks for the help! Will let you know if I get it to work or not! – Humpy May 21 '13 at 04:26
I have tried this again and it's still not working properly. No row is added to the table dispite my label claiming that it was moved properly. I did go back and parameterize the query though. Thanks for that idea again. If you have any other ideas let me know! Thanks again! – Humpy May 21 '13 at 15:17
Well, the code above adds only a check if the SelectedRow is NOT null before trying to use your own code to insert data. So the only explanation possible is that the SelectedRow is null when you call that code. – Steve May 21 '13 at 18:39

score 0 · Accepted Answer · answered May 21 '13 at 16:22

I figured it out finally. I had to abandon the onclick select button I created into a template, but not big deal. Here is the code.

    protected void gvVehicleImport_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
    {
        SqlConnection conn = new SqlConnection("Data Source=DataBase;Initial Catalog=DataBase;Integrated Security=True");
        conn.Open();

        string StockNumber;
        string SalesPerson;
        string Buyer;
        string GrossProfit;
        string DealDate;
        string Make;
        string Model;
        string CarTruck;
        string NewUsed;
        string Lender;
        string AmtFinanced;
        string RetailLease;
        string BankName;
        string Status;
        string ChangedBy;

        try
        {
            GridViewRow g1 = gvVehicleImport.Rows[e.NewSelectedIndex];


            //converts the labels of the gridview into strings
            StockNumber = (g1.FindControl("lblStockNumber") as Label).Text;
            SalesPerson = (g1.FindControl("lblSalesPerson") as Label).Text;
            Buyer = (g1.FindControl("lblBuyer") as Label).Text;
            GrossProfit = (g1.FindControl("lblGrossProfit") as Label).Text;
            DealDate = (g1.FindControl("lblDealDate") as Label).Text;
            Make = (g1.FindControl("lblMake") as Label).Text;
            Model = (g1.FindControl("lblModel") as Label).Text;
            CarTruck = (g1.FindControl("lblCarTruck") as Label).Text;
            NewUsed = (g1.FindControl("lblNewUsed") as Label).Text;
            Lender = (g1.FindControl("lblLender") as Label).Text;
            AmtFinanced = (g1.FindControl("lblAmtFinanced") as Label).Text;
            RetailLease = (g1.FindControl("lblRetailLease") as Label).Text;
            BankName = (g1.FindControl("lblBankName") as Label).Text;
            Status = (g1.FindControl("lblStatus") as Label).Text;
            ChangedBy = (g1.FindControl("lblChangedBy") as Label).Text;


            //inserts statement inserts above strings into table
            SqlCommand addImport = new SqlCommand("INSERT INTO Vehicle(v_StockNumber, v_SalesPerson, v_Buyer, v_GrossProfit, v_DealDate, v_Make, v_Model, v_CarTruck, v_NewUsed, v_Lender, v_AmtFinanced, v_RetailLease, v_BankName, v_Status, v_ChangedBy)"
            + "VALUES(@v_StockNumber, @v_SalesPerson, @v_Buyer, @v_GrossProfit, @v_DealDate, @v_Make, @v_Model, @v_CarTruck, @v_NewUsed, @v_Lender, @v_AmtFinanced, @v_RetailLease, @v_BankName, @v_Status, @v_ChangedBy)", conn);
            addImport.Parameters.AddWithValue("@v_StockNumber", StockNumber);
            addImport.Parameters.AddWithValue("@v_SalesPerson", SalesPerson);
            addImport.Parameters.AddWithValue("@v_Buyer", Buyer);
            addImport.Parameters.AddWithValue("@v_GrossProfit", GrossProfit);
            addImport.Parameters.AddWithValue("@v_DealDate", DealDate);
            addImport.Parameters.AddWithValue("@v_Make", Make);
            addImport.Parameters.AddWithValue("@v_Model", Model);
            addImport.Parameters.AddWithValue("@v_CarTruck", CarTruck);
            addImport.Parameters.AddWithValue("@v_NewUsed", NewUsed);
            addImport.Parameters.AddWithValue("@v_Lender", Lender);
            addImport.Parameters.AddWithValue("@v_AmtFinanced", AmtFinanced);
            addImport.Parameters.AddWithValue("@v_RetailLease", RetailLease);
            addImport.Parameters.AddWithValue("@v_BankName", BankName);
            addImport.Parameters.AddWithValue("@v_Status", Status);
            addImport.Parameters.AddWithValue("@v_ChangedBy", ChangedBy);

            //executes the import
            addImport.ExecuteNonQuery();
            //}

            //closes connection
            conn.Close();
            lblImportMessage.Text = "Row move successful.";


        }
        catch (Exception ex)
        {
            lblImportMessage.Text = "Row move was unsuccessful, " + ex.ToString();
        }
    }

I have tried multiple things, many got it to insert a single row with no data, but this one seems to be working okay for me right now. Hope this helps anyone else that is having trouble. Thanks again!

Don't forget to change the SelectedIndexChanged to SelectedIndexChanging in the asp! — Humpy, May 21 '13 at 16:23

Inserting a single selected row in gridview into database ASP.net

2 Answers2