[RESOLVED] DataGridView Columns Width Issue

[toecutter]

2 Tables with almost exact same properties (column widths)

2 TableAdaptors with almost exact same properties

2 DataGridView with exact same properties yet one widens the width of the colomn to the Max length size of either the Tables or the TableAdaptors so it seems.






I have tried all the MSDN examples.

 ReceiptsDataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
        ReceiptsDataGridView.AutoResizeColumn(2, DataGridViewAutoSizeColumnMode.ColumnHeader)
        ReceiptsDataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
        ReceiptsDataGridView.AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCells)
        ReceiptsDataGridView.AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders)
        ReceiptsDataGridView.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
        ReceiptsDataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
        ReceiptsDataGridView.AutoResizeRow(2, DataGridViewAutoSizeRowMode.AllCellsExceptHeader)
        ReceiptsDataGridView.AutoResizeColumns()
        ReceiptsDataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

[jmcilhinney]

If this is a SQL Server database then it may be that one column is type char or nchar while the other column is type varchar or nvarchar. char and nchar are fixed-width, so they will pad their contents with spaces, which would account for what you're seeing. Many other databases would have similar data types.

[toecutter]

You were correct in that the data types were different but i have corrected that and the grids are still behaving differently.

[jmcilhinney]

Changing the data type of a char or nchar column to varchar or nvarchar is not going to change the data that's already in there. When you insert data into a fixed-width text column, the database pads the data to the appropriate width and that is the data that's stored. When you retrieve the data you retrieve the spaces because the spaces are part of the data. When you change the data type the spaces remain because the spaces are part of the data. If you want to remove the spaces from all the data in the column then you'll have to execute an appropriate SQL statement, e.g.

UPDATE receipts SET description = TRIM(description)

That will remove all leading and trailing spaces as well as any multiple spaces between words.

[toecutter]

TRIM is apparently not a SQL command so i found this which runs but doest Trim, also tried "LTRIM"

      Dim connectionString As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\expressAccountsDB.mdf;Integrated Security=True;User Instance=True")
        Dim myCommand As New SqlCommand
        Try
            connectionString.Open()
            myCommand = New SqlCommand("UPDATE receipts SET description = RTRIM(description)")
            'myCommand = New SqlCommand("UPDATE receipts SET description = LTRIM(description)")
            myCommand.Connection = connectionString
            myCommand.ExecuteNonQuery()
            MsgBox("trimmed successfully")
        Catch ex As Exception
            MsgBox(ex.Message())
        End Try
        connectionString.Close()

[jmcilhinney]

TRIM is apparently not a SQL command so i found this which runs but doest Trim, also tried "LTRIM"

      Dim connectionString As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\expressAccountsDB.mdf;Integrated Security=True;User Instance=True")
        Dim myCommand As New SqlCommand
        Try
            connectionString.Open()
            myCommand = New SqlCommand("UPDATE receipts SET description = RTRIM(description)")
            'myCommand = New SqlCommand("UPDATE receipts SET description = LTRIM(description)")
            myCommand.Connection = connectionString
            myCommand.ExecuteNonQuery()
            MsgBox("trimmed successfully")
        Catch ex As Exception
            MsgBox(ex.Message())
        End Try
        connectionString.Close()

Oops! I looked at the wrong documentation. RTRIM is "right trim" and LTRIM is "left trim". They are equivalent to String.TrimEnd and String.TrimStart in VB. SQL Server pads the end of the text with spaces so you need to trim the end, i.e. the righthand side of the text, so RTRIM is the appropriate function.

[toecutter]

hmmm, deleted the table adaptor and made a new one and all good, so dont need the sql command.

thanks

[jmcilhinney]

hmmm, deleted the table adaptor and made a new one and all good, so dont need the sql command.

thanks

I didn't mean that you need that in your application. I meant that you needed to run it once against the database to remove all the padding.

[toecutter]

Oh, so you would do this in the Server Explorer window by right clicking the Table, selecting New Query and entering that cmd into the Query wizard?

[jmcilhinney]

Oh, so you would do this in the Server Explorer window by right clicking the Table, selecting New Query and entering that cmd into the Query wizard?

Precisely. You only need to update the data once. Once the padding is removed it's not going to come back again because you changed the data type of the column.