I'm working on a program that allows me to quickly write database entries. There are 123 columns, almost all of them I'm not using right now so I figured I'd write a program to speed up the process of adding entries to the database.
The problem I'm having is exporting every row in a DataGridView to a .sql file. I'm able to do it with a single row but I'm having trouble with doing it for all rows. Every time I get a index out of range exception. I can't figure out why. The problem is probably obvious, but I need a fresh set of eyes to figure it out.
In order for it to streamwrite each line it first collects all the data from the columns by using a loop then it writes it, clears the string and starts on the next row, looping for all the data, so on and so forth.
Private Sub StreamAll(ByVal table as String) Dim TotalRows As Integer = DataGridView1.Rows.GetRowCount(DataGridViewElementStates.Visible) Dim CurrentRow As Integer = 1 Dim TotalCols As Integer = DataGridView1.Columns.GetColumnCount(DataGridViewElementStates.Visible) Dim CurrentCol As Integer = 0 Dim DataInfo As String = "INSERT INTO " & table & " VALUES ('" Dim Failed As Boolean = False debugoutput.Text &= "Preparing to write..." & vbNewLine Try With FolderBrowserDialog1 .RootFolder = Environment.SpecialFolder.Desktop .SelectedPath = "C:\" If .ShowDialog = DialogResult.OK Then Try Using sw As StreamWriter = New StreamWriter(.SelectedPath & "\" & "QuickSQL.sql") Do Do DataInfo &= DataGridView1.Item(CurrentCol, CurrentRow).Value & "', '" CurrentCol = CurrentCol + 1 Loop Until CurrentCol = TotalCols - 1 DataInfo &= DataGridView1.Item(CurrentCol, CurrentRow).Value & "';" sw.WriteLine(DataInfo) DataInfo = "INSERT INTO " & table & " VALUES ('" CurrentRow = CurrentRow + 1 Loop Until CurrentRow = TotalRows - 1 debugoutput.Text &= "Writing to file..." & vbNewLine sw.Close() End Using Catch ex As Exception debugoutput.Text &= "Streamwrite failed (" & ex.Message & ")." & vbNewLine Failed = True End Try ElseIf .ShowDialog = DialogResult.Cancel Then debugoutput.Text &= "Write aborted." & vbNewLine Failed = True End If End With Catch ex As Exception debugoutput.Text &= "Streamwrite failed (" & ex.Message & ")." & vbNewLine Failed = True End Try Select Case Failed Case Is = False debugoutput.Text &= "Streamwrite successful." & vbNewLine Case Else End Select End Sub
Sorry for the mess of code. Couldn't figure out a more organized way.