Changing column datatype when it's binded with a datatable

Question

My goal:

Create a bank balance system with balance break per day

Actual Results:

I can extract the data what I want from my database, sum balance, and fill the datagrid with it

what I need:

I need a break in the transition of each day, which will tell me the balance of the day...

The problem:

I get an error if I try to input a string "Saldo" in "Valor" (decimal) column, which contains all values
I tried to change "DataType" of column "Valor", but it says I can't change datatype of a filled column.

Code

    Dim dt As New DataTable()

        Using adapter As New SqlDataAdapter("SELECT * FROM TAB_Movimentos 
                                         WHERE Banco = @Banco 
                                         ORDER BY Data ASC, Ordem ASC, Valor DESC, Histórico ASC", New SqlConnection(MontaStringConexaoSQLServer()))
            adapter.SelectCommand.Parameters.Add("@Banco", SqlDbType.Int).Value = cbBanco.Value
            adapter.Fill(dt)
        End Using

    tabHistorico.DataSource = dt.DefaultView

    linhasaldo as integer = 5

    Dim nlinha As DataRow = dt.NewRow
    nlinha("Valor") = "Saldo: "
    nlinha("Saldo") = saldo
    dt.Rows.InsertAt(nlinha, linhasaldo)

This's what I want, It need some formatting in values, I reached this changing my SQL "Valor" column data type to varchar

This's what I've in the moment, this's the "tabhistorico" datagrid

This's my database

As you've discovered, you can't add a string value into your decimal column without [converting the column](https://stackoverflow.com/a/9028087/3773066) to type varchar - which loses your money-format. Theoretically, after this conversion you could iterate through the column, parse the decimal values, and add money formatting. Then you could add your new rows to this newly formatted *Valor* column. Currently, I can't test it to be sure. — OhBeWise, Apr 29 '20 at 20:14
Alternatively, you could [add a border separator](https://stackoverflow.com/a/35610742/3773066). My suggestion is in C# instead of VB, but I find [Telerik Code Converter](https://converter.telerik.com/) useful for that. — OhBeWise, Apr 29 '20 at 20:18

score 1 · Accepted Answer · answered Apr 29 '20 at 20:55

Instead of changing the underlying table to accommodate the string in Valor, cast Valor to string in your adapter so it's temporary. This leaves all your data in the table safe and usable by any other programs you have, but still lets you use it as a string here and insert the Saldo rows. It's also a lot more efficient for time and memory than reading in the table and then performing a copy and convert on it. To do that, make your QueryString in Adapter as follows:

SELECT Banco, Lancamento, Historico, Data
    , CONVERT(nvarchar(50),Valor) as Valor
    , Ordem 
FROM TAB_Movimentos 
WHERE Banco = @Banco 
ORDER BY Data ASC, Ordem ASC, Valor DESC, Histórico ASC

You might also have to do the same with the non-null numeric columns to get them to accept blanks.

Note that this has the same problem @OhBeWise points out - you lose the formatting of numbers because they aren't numbers anymore as far as your DataGridView is concerned. You might want to use FORMAT() instead of CONVERT() (if it's available in your version of SQL-SERVER)

Changing column datatype when it's binded with a datatable

1 Answers1