Formatting a column with windows forms 1

[sonya9879]

Hi,

I have a windows form that basically just retrieves some data from a SQL database. I have on column on the database I need to change before rendering the form on the datagrid. the problem is that I do not know how I could get the value before its added in the datagrid so I can change it.


Here is a sample of my code that defines the datagrid columns:

Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Me.SqlDataAdapter1.Fill(Me.DsCustomers1)

'Step 1: Create a DataGridTableStyle & set mappingname to table.
Dim tableStyle As New DataGridTableStyle
tableStyle.MappingName = "Customers"

'Step 2: Create DataGridColumnStyle for each col
' we want to see in the grid and in the
' order that we want to see them.

' Use a PropertyDescriptor to create a formatted
' column. First get the PropertyDescriptorCollection
' for the data source and data member.
Dim pcol As PropertyDescriptorCollection = _
Me.BindingContext(DsCustomers1, "Customers"). _
GetItemProperties()

' Create a formatted column using a PropertyDescriptor.
' The formatting character "c" specifies a currency format. */

Dim column As _
New DataGridTextBoxColumn(pcol("TotalAmount"), "c", True)
'Dim column As New DataGridTextBoxColumn
column.MappingName = "TotalAmount"
column.HeaderText = "TotalAmount"
tableStyle.GridColumnStyles.Add(column)

column = New DataGridTextBoxColumn
column.MappingName = "InterVal" ' formatCol("Interval")
column.HeaderText = "International"
tableStyle.GridColumnStyles.Add(column)

column = New DataGridTextBoxColumn
column.MappingName = "ID"
column.HeaderText = "ID"
tableStyle.GridColumnStyles.Add(column)

Me.DataGrid1.TableStyles.Add(tableStyle)
End Sub

Look at the formatCol, this is the column I want to change the original value and I have a function I need to run over each value of the datagrid. Something like: column.MappingName = formatCol("Interval") but this obviously doesn't work. I believe I cannot do this here because the datagrid is not instantiated at this point and probably I can't do it this way. Look at the TotalAmount field, there I managed to format the column, but that's not exactly what I want to do, because its not just formatting the column for "Interval", its running a function over each record whcih will change the value.

Hope anyone can share some ideas on this and how I could get it right, appreciate it. I am back to work finally

 

[jebenson]

I think we need a little more information. What is the SQL code for getting your data from the database? What is the format of the original data? What format are you trying to apply to the data? Could you show the formatting code?


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[sonya9879]

Hi jebenson,

'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT totalamount, interval, id FROM customers"
Me.SqlSelectCommand1.Connection = Me.SqlConnection1

The interval is an encrypted value that I need to decrypt before its rendered/displayed on the datagrid.

i have written a function to decrypt the value but the problem is where I can get the value so I can apply the function? Thanks a lot for replying jebenson.

 

[sonya9879]

any ideas anyone?

 

[andrea96]

Is this a value that you need to modify and save back to the database? If not, you could change the value in your dataset.

Andrea

 

[andrea96]

If you need to save it back to the database in the original format, you can create a derived datagridtextboxcolumn and format the text for display in GetColumnValueAtRow and remove the formatting in Commit.

Andrea

 

[sonya9879]

i dont' need to change the value back to the database. I just need to be able to retrieve the value from the database, convert that value to the original form and display that in the datagrid. I know how to get the value and put it on the datagrid. I do not know where I could actually apply a function to each value of the column. Don't have too mcuh experience with windows forms and itembounds is not an option available.

 

[jebenson]

Have you tried looping through the table in the DataSet before you assign to the grid?

Me.SqlDataAdapter1.Fill(Me.DsCustomers1)

Dim r As Integer
For r = 0 to Me.DsCustomers1.Tables(0).Rows.Count - 1
Me.DsCustomers1.Tables(0).Rows(r).Item("interval") = _
<UnEncryptFunctionName>(Me.DsCustomers1.Tables(0).Rows(r).Item("interval"))
Next

Me.DsCustomers1.AcceptChanges()

'Step 1: Create a DataGridTableStyle & set mappingname to table.
Dim tableStyle As New DataGridTableStyle
tableStyle.MappingName = "Customers"

etc.

Of course, put an actual function name in place of <UnEncryptFunctionName>.

One possible pitfall with this approcach is if you use this DataSet/DataAdapter to write data back to the database. However, that should be surmountable by re-encrypting the interval before writing saving to the database.

Another possible drawback is that this method is untested and may not work! But its an idea....

Another possibility is to program a SQLServer stored procedure to call a user-defined function that decrypts the data as it is fetched. Unfortunately my SQLServer programming skills are not strong enough to provide any example code, but you may want to check on the Microsoft SQL Server: Programming Forum (forum183) to see if anybody there has any ideas about this.

Let me know how/if it works.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[sonya9879]

I did somethibng like this:

Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Me.SqlDataAdapter1.Fill(Me.DsCustomers1)

Dim tableStyle As DataGridTableStyle
tableStyle = New DataGridTableStyle
tableStyle.MappingName = "Customers"
' since the dataset has things like field name and number of columns,
' we will use those to create new columnstyles for the columns in our DB table
Dim numCols As Integer
numCols = DsCustomers1.Tables("Customers").Columns.Count
Dim aColumnTextColumn As DataGridEnableTextBoxColumn
Dim i As Integer
i = 0
Do While (i < numCols)
aColumnTextColumn = New DataGridEnableTextBoxColumn(i)
aColumnTextColumn.HeaderText = DsCustomers1.Tables("Customers").Columns(i).ColumnName
aColumnTextColumn.MappingName = DsCustomers1.Tables("Customers").Columns(i).ColumnName
'an EnableCell event handler
If i = 3 Then
aColumnTextColumn.HeaderText = DsCustomers1.Tables("Customers").Columns(i).ColumnName
aColumnTextColumn.MappingName = DsCustomers1.Tables("Customers").Columns(i).ColumnName
End If
tableStyle.GridColumnStyles.Add(aColumnTextColumn)
i = (i + 1)
Loop

Dim r As Integer
For r = 0 To Me.DsCustomers1.Tables(0).Rows.Count - 1
Me.DsCustomers1.Tables(0).Rows(r).Item("interval") = _
DecryptCC(Me.DsCustomers1.Tables(0).Rows(r).Item("interval"))
Next

Me.DsCustomers1.AcceptChanges()

' make the dataGrid use our new tablestyle and bind it to our table
DataGrid1.TableStyles.Clear()
DataGrid1.TableStyles.Add(tableStyle)
DataGrid1.DataSource = DsCustomers1.Tables("Customers")
End Sub

I get an error when I tried to call the function,

Option Strict On disallows implicit conversions from 'System.Object' to 'String'.

 

[andrea96]

Try changing the line where you call your function to this.

 DecryptCC(Me.DsCustomers1.Tables(0).Rows(r).Item("interval")[red].ToString[/red])

 

[sonya9879]

I fixed this error by turning the option strict OFF and then it worked beatifully THANSK A BILLION. Do you know whats the downside of switching option strict off?

 

[sonya9879]

forget what I said, I used your way and works perfectly.

thanks andrea, is people like you that makes the difference , I finished eating my nails and I was going to pullout my hair next

 

[andrea96]

This is from online help.

Traditionally, Visual Basic has had permissive semantics; that is, the Visual Basic language in general does not require explicit syntax to be used when performing operations that might not be optimally efficient (for example, late binding) or that might fail at run time (for example, narrowing conversions). Although permissive semantics simplifies learning the Visual Basic language, it can hamper large-scale application development by allowing coding errors to slip by undetected. Visual Basic .NET offers the option of enforcing strict semantics during compilation.

I always use option strict ON, and I have read several places where it is suggested to keep it ON to avoid potential run-time errors.

Andrea

 

[sonya9879]

i got it, STRICT ON forever and ever, thanks Andrea!