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.

2
Contributors
2
Replies
5
Views
5 Years
Discussion Span
Last Post by GeekByChoiCe

Here is a working example:

``````Imports System.Text

Public Class Form1
'Create the query
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim table As String = "testTable" 'whatever your table name is
Dim columnCount As Integer = DataGridView1.Columns.Count - 1 'get the count of columns
Dim sb As New StringBuilder
sb.AppendFormat("INSERT INTO {0} VALUES ", table)
For Each row As DataGridViewRow In DataGridView1.Rows
For i As Integer = 0 To columnCount
If i = 0 Then 'first value need to have "("
sb.AppendFormat("('{0}',", row.Cells(i).Value)
Continue For
End If
If i < columnCount Then	'all values split by "," excpet last value
sb.AppendFormat("'{0}',", row.Cells(i).Value)
Continue For
End If
'last value needs to have "),"
sb.AppendFormat("'{0}'),", row.Cells(i).Value)
Next
Next

Debug.WriteLine(sb.ToString.TrimEnd(","c)) 'print out the string without the last ","
End Sub

'Fill Grid
For i As Integer = 0 To 2
Next
End Sub

Private Function createRow(index As Integer) As String()
Dim list As New List(Of String)
For Each col As DataGridViewColumn In DataGridView1.Columns
``IO.File.WriteAllText("QuickSQL.sql", sb.ToString.TrimEnd(","c))``