Hi there,

My issue: I have a datagridview and a DB file with ~100 columns and ~200 rows of data. This data is imported to the DGV onLoad(). Once i have the data i run a script that searchs a predefined(by the user) folder for text files. Each text file has anywhere between 2 and 100 lines of text each in the following format:

CRADEG      DEGREE OF CRASH
      1     Fatal
      2     Injury
      3     Non-casualty (towaway)

I can loop through the text file (ignoring the first line) and get each line of information and assign it a code (left column) and variable (right column).
What happens next is the script searchs for a column in the DGV with the same name as the text file. Once found, it loops through each row and looks for CODE1 (in my example CODE1 is '1') it then replaces the code in the DGV with the variable from the textfile (in my example VAR1 is 'Fatal').

However when this happens i get the following error:

Fatal is not a valid value for Double.

Obviously this means that the cell is set to hold a 'Double' data type. But i need to change it (and the rest of the column) to use the 'String' data type.

My code for those interested:

        Dim headers As New List(Of String)
        For Each col As DataGridViewColumn In Me.DataGridView1.Columns
            headers.Add(col.HeaderText.ToString)
        Next
        DataGridView1.ReadOnly = False
        DataGridView1.AllowUserToAddRows = True
        DataGridView1.AllowUserToDeleteRows = True
        DataGridView1.AllowUserToOrderColumns = True
        Dim StrReader As StreamReader
        For i = 0 To headers.Count - 1
            If File.Exists(txtFiles & headers.Item(i) & ".txt") Then
                StrReader = New StreamReader(txtFiles & headers.Item(i) & ".txt")
                Dim line As String
                Dim code As String
                Dim variable As String
                StrReader.ReadLine()
                Do
                    line = StrReader.ReadLine()
                    If line <> "" Then
                        line.Trim()
                        code = line.Split(vbTab)(0)
                        variable = line.Split(vbTab)(1)
                        code = code.Substring(7)
                        MsgBox("C: " & code & vbNewLine & "V: " & variable)
                        For j = 0 To DataGridView1.Rows.Count - 2
                            DataGridView1.CurrentCell = DataGridView1.Item(headers.Item(i), j)
                            DataGridView1.CurrentCell.ValueType = GetType(String)
                            If DataGridView1.CurrentCell.Value = code Then
                                DataGridView1.CurrentCell.Value = variable
                            End If
                            MsgBox(DataGridView1.CurrentCell.Value)
                        Next
                    End If
                Loop Until line Is Nothing

                StrReader.Close()
            End If
        Next

Im not sure where to go from here and i've spent over an hour searching different ways to change the datatype, only to find infomation on swapping from a DGVTextBox to a DGVComboBox etc.

Any help is appreciated!!!
Regards, James

Recommended Answers

All 6 Replies

My issue: I have a datagridview and a DB file with ~100 columns and ~200 rows of data. This data is imported to the DGV onLoad().

Does this mean that you first fill a datatable from the database and then set the dgv.DataSource equal to the datatable?

If so, that is the source of the problem as the dgv as bound to the datatable. The datatable is where the column type is defined as double.

You could add an unbound column to the dgv and set the value for each row as you need to. You would then hide the original column.

Edit:

Please show the Form_Load method

This is what runs on formLoad():

        Dim fileName As New IO.FileInfo(frmStepOne.ofdFileSearch.FileName)
        Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBase IV;Data Source='" & fileName.DirectoryName & "'")
        Dim tablename As String = fileName.Name.Substring(0, fileName.Name.Length - fileName.Extension.Length)
        Dim command As New OleDb.OleDbCommand(tablename, connection)
        command.CommandType = CommandType.TableDirect
        Dim dataTable As New DataTable
        connection.Open()
        Dim reader As OleDb.OleDbDataReader = command.ExecuteReader
        dataTable.Load(reader)
        DataGridView1.DataSource = dataTable
        connection.Close()
        connection.Dispose()

This is the first project ive used a database, so i was bound to miss something.

Edit: This is all run from frmStepTwo.
frmStepOne, contains 2 textboxs from which i get the location of the database file (.dbf) and the folder location that contians the text files (.txt)

This is not an ideal solution, but I think that it has the least chance of confusing you.

We will create a new datatable with only string values.

Dim fileName As New IO.FileInfo(frmStepOne.ofdFileSearch.FileName)
Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBase IV;Data Source='" & fileName.DirectoryName & "'")
Dim tablename As String = fileName.Name.Substring(0, fileName.Name.Length - fileName.Extension.Length)
Dim command As New OleDb.OleDbCommand(tablename, connection)
command.CommandType = CommandType.TableDirect
Dim dataTable As New DataTable
connection.Open()
Dim reader As OleDb.OleDbDataReader = command.ExecuteReader
dataTable.Load(reader)
connection.Close()
connection.Dispose()
'---------------- 
Dim datatableAllString As New DataTable

For Each col As DataColumn In dataTable.Columns
   datatableAllString.Columns.Add(col.ColumnName, GetType(String))
Next
Dim newrow As DataRow
For Each r As DataRow In dataTable.Rows
   newrow = datatableAllString.NewRow
   For i As Int32 = 0 To datatableAllString.Columns.Count - 1
      newrow(i) = r.ItemArray(i).ToString()
   Next i
Next r
dataTable.Dispose()
'----------------
DataGridView1.DataSource = datatableAllString

Not sure if i have implemented correctly. But the code you have provided above gives me a blank database. The columns are strings, but the data i require isnt being imported.

Sorry but that, I forgot a line of code to add the newrow to the table.

For Each r As DataRow In dataTable.Rows
   newrow = datatableAllString.NewRow
   For i As Int32 = 0 To datatableAllString.Columns.Count - 1
      newrow(i) = r.ItemArray(i).ToString()
   Next i
   datatableAllString.Rows.Add(newrow)
Next r
commented: Solution! +1

Sorry but that, I forgot a line of code to add the newrow to the table.

No worries. I should not have been lazy and looked for an error myself.

The combined solutions have fixed the error :)

Thanks heaps mate :) really appreciate it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.