Add values in a column of a datagridview in vb.net

[don420]

i want to dynamically add the values in the column of a datagridview the row of which is also being created dynamically...

Form1.TextBox10.Text = total
        con.Open()
        cmd = New SqlClient.SqlCommand("select product,Quantity,MRP,Sale_Rate,Disc_Amt,Amt from sale_table ", con)
        adapter.SelectCommand = cmd
        adapter.Fill(ds)
        Dim r As Integer = ds.Tables(0).Rows.Count
        Dim counter As New Integer
        Dim NewRow As DataRow
        NewRow = ds.Tables(0).Rows(r - 1)
        Form1.DataGridView1.Rows.Add(NewRow.ItemArray)
        counter = Form1.DataGridView1.RowCount()
        Form1.TextBox6.Text = counter - 1
        total += CInt(Form1.DataGridView1.Rows(counter - 1).Cells(6).Value)
        Form1.TextBox10.Text = total
        adapter.Dispose()
        con.Close()


total += CInt(Form1.DataGridView1.Rows(counter - 1).Cells(6).Value)

the value it displays is 0 always
plzz help

[adam_k]

You lost me with the I want to add the values in the column the row.

Your code seems only to be adding the second from last row in datagridview, and then you try to add to total the value of row -1 (your counter is 0 and you subtract 1). (What does form1.textbox6 show?)

If I understand correctly you want to sum the column 6 (Amt) in your datagridview.

The easiest way to do this (at least in my book) is to have SQL calculate that for you. This can be done in the same query (with a union) or in a separate query. You can get the value and be done with it. It's way faster to ask SQL to do it for you, than calculating it.
As I believe that you don't want the total to appear on your gridview, I'll go with a separate query to get it. Also I'll use a binding to show data to the gridview, as it's faster and cleaner.

dim cmd as new sqlclient.sqlcommand
dim bind as new bindingsource

cmd.commandtext = "select product,Quantity,MRP,Sale_Rate,Disc_Amt,Amt from sale_table"
cmd.connection = con 
con.open() 

bind.datasource = cmd.executereader(CommandBehavior.CloseConnection) 
DataGridView1.datasource = bind

cmd.commandtext = "select sum(Amt) from sale_table"
con.open()

textbox10.text = cmd.executescalar 
cmd.dispose 
con.close

If you have to calculate the total from the datagridview:

For i As Integer = 0 To DataGridView1.RowCount() - 1
            total += DataGridView1.Item(6, i).Value
        Next

        TextBox1.Text = total.ToString

[don420]

i came across the problem several days back and thereafter realised its better to go sql query and it worked fine..
anyways thanks 4 replying

[vishalrane]

Thanks....

[Ruchi224]

I am using same code.. However, getting an error: operator '+' is not defined for the type DBnull and integer.. :(

[gd740]

You lost me with the I want to add the values in the column the row.

Your code seems only to be adding the second from last row in datagridview, and then you try to add to total the value of row -1 (your counter is 0 and you subtract 1). (What does form1.textbox6 show?)

If I understand correctly you want to sum the column 6 (Amt) in your datagridview.

The easiest way to do this (at least in my book) is to have SQL calculate that for you. This can be done in the same query (with a union) or in a separate query. You can get the value and be done with it. It's way faster to ask SQL to do it for you, than calculating it.
As I believe that you don't want the total to appear on your gridview, I'll go with a separate query to get it. Also I'll use a binding to show data to the gridview, as it's faster and cleaner.

dim cmd as new sqlclient.sqlcommand
dim bind as new bindingsource

cmd.commandtext = "select product,Quantity,MRP,Sale_Rate,Disc_Amt,Amt from sale_table"
cmd.connection = con 
con.open() 

bind.datasource = cmd.executereader(CommandBehavior.CloseConnection) 
DataGridView1.datasource = bind

cmd.commandtext = "select sum(Amt) from sale_table"
con.open()

textbox10.text = cmd.executescalar 
cmd.dispose 
con.close

If you have to calculate the total from the datagridview:

For i As Integer = 0 To DataGridView1.RowCount() - 1
            total += DataGridView1.Item(6, i).Value
        Next

        TextBox1.Text = total.ToString

hi adam...i would like to ask is that possible to insert the displayed value in TextBox1 into database because it only show in window form??

[adam_k]

I am using same code.. However, getting an error: operator '+' is not defined for the type DBnull and integer.. :(

You either need to handle null values in your database (if it's SQL then isnull(field_or_function,0)) or handle the dbnull with an if that will test if the value is not dbnull before adding the value to the total.

[adam_k]

hi adam...i would like to ask is that possible to insert the displayed value in TextBox1 into database because it only show in window form??

Yes, it is possible to insert the value into db. If your values exist in the db, you can calculate it and insert it into the db with the same query, without moving data from and to the db. Like I said in my original query it will be faster to handle it in the db.

If your data don't exist in the db you can calculate the total and then use an sqlcommand with the appropriate insert or update query and cmd.executenonquery (if we suppose that your sqlcommand is named cmd)