Display sql data in datagrid view having predefined columns

Question

I want the sql data to be displayed in the data grid predefined columns when id(in database) is entered in the PID(textbox). for example: if I enter 1 in textbox, the product_Name(wai wai chicken) should display in the column Particulars, the product_Id(1) should itself display in the column PID, actual_Sp(15) should display in the column Amount and also when number is entered in Qty textbox it should display in Unit Qty column and should be multiplied with amount (say when 1 is entered wai wai is displayed as well as 15(actual_sp) is displayed in amount when 2 is entered in qty textbox the amount should display 30(as 15*2=30)).

I have tried the following code but it is adding new columns in my DGV instead of displaying in my predefined columns:

string cc = "";
        int n;
        if (int.TryParse(textBox1.Text, out n))
        {
            cc = "product_Id = " + textBox1.Text.Trim();
        }
        else
        {
            cc = "product_Name LIKE '%" + textBox1.Text.Trim() + "%'";
        }

        if (string.IsNullOrEmpty(textBox1.Text))
        {
            cc = "1 = 1";
        }

        SqlConnection con = new SqlConnection("Data Source=SUMIT;Initial Catalog=Project;Integrated Security=True");

        SqlCommand cmd = new SqlCommand("Select product_Name as [Product Name],cast(actual_Sp as INT) * '" + textBox2.Text + " 'as [Actual SP] from Product WHERE " + cc, con); ;

    cmd.CommandType = CommandType.Text;
        SqlDataAdapter sda = new SqlDataAdapter(cmd);

        DataTable dt = new DataTable();
        sda.Fill(dt);
        dataGridView1.AutoGenerateColumns = false;
       dataGridView1.ColumnCount = 7;

        dataGridView1.Columns[0].Name = "ProductId";
        dataGridView1.Columns[0].HeaderText = "PID";
        dataGridView1.Columns[0].DataPropertyName = "product_Id";

        dataGridView1.Columns[1].HeaderText = "S.N.";
        dataGridView1.Columns[1].Name = "Serial Number";

        dataGridView1.Columns[2].Name = "Particulars";
        dataGridView1.Columns[2].HeaderText = "Particulars";
        dataGridView1.Columns[2].DataPropertyName = "product_Name";


        dataGridView1.Columns[3].Name = "Unit Quantity";
        dataGridView1.Columns[3].HeaderText = "Unit Quantity";

        dataGridView1.Columns[4].Name = "Amount";
        dataGridView1.Columns[4].HeaderText = "Amount";

        dataGridView1.Columns[5].Name = "Discount";
        dataGridView1.Columns[5].HeaderText = "Discount";

        dataGridView1.Columns[6].Name = "Sub-Total";
        dataGridView1.Columns[6].HeaderText = "Sub-Total";     
        dataGridView1.DataSource = dt;

    }

SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Dec 21, 2015 at 8:17
you could make a list view instead. With a listview you can customize the grid better than with a gridview. — Code_Viking
– Code_Viking, Commented Dec 21, 2015 at 10:14
Looks like you need to DataBind the columns in your DGV to the SQL columns you want them to contain. — Tab Alleman
– Tab Alleman, Commented Dec 21, 2015 at 16:02

Mohit S · Accepted Answer · 2015-12-22 02:02:27Z

1

This might do the trick for you.

SqlDataAdapter a = new SqlDataAdapter(
                    "Select product_Name as [Product Name],
                            actual_Sp as [Actual SP] from Product WHERE " + cc, con);

with

SqlDataAdapter a = new SqlDataAdapter(
                    "Select product_Name as [Product Name], 
                            actual_Sp * " + TxtQty.Text + " as [Actual SP] 
                            from Product WHERE " + cc, con);

This will do the multiplication of the actual_Sp for the number of quantity you add to the TextBox

answered Dec 22, 2015 at 2:02

Mohit S

14.1k6 gold badges36 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sumit Bhattarai Over a year ago

when I debug It displays error converting data type varchar to numeric

Mohit S Over a year ago

To run this query actual_Sp should be numeric type instead of varchar. BTW you can try cast(actual_Sp as INT) * " + TxtQty.Text + "

Sumit Bhattarai Over a year ago

actual_Sp is of type numeric but still its showing error

Sumit Bhattarai Over a year ago

now its showing ColumnCount property cannot be set on a data-bound DataGridView control error. I've edited the code I've posted please go through it once again..

Collectives™ on Stack Overflow

Display sql data in datagrid view having predefined columns

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related