Member Avatar for Oxiegen

Hi!
Sorry about my bad grammer and/or spelling. English is not my native language.

I don't know if this already have been asked and answered, however...

I'm currently developing a program in which it will be possible to select table fields from a database and set various criteria using dropdown-lists. This will then dynamically generate an SQL-query string.
I then convert the datareader I use for this query into a datatable.

The above part works great.

Now to the problem.
I have other functions that perform an export to excel, but those has fixed number of fields and it's then easy, for example, to use VALUES ('" & row.Item(0) & "','" & row.Item(2) & "','" & row.Item(3) & "'..... to retrieve and export the information.

But because this latest function has fields that varies in number, the above code won't exactly work.
I need a way to retrieve the information into a single string where: str &= "'" & row.Item(<dynamic number 0 to field.count - 1>) & "'," .

And here's the crux. All this has to be performed inside a single For Each row AS DataRow In DataTable.Rows because the data is exported into excel row by row.

Any ideas?

  • 1 Contributor
  • 1 Reply
  • 246 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Oxiegen

Dani AI

Generated

Good call, — the helper-function pattern is a solid way to keep the export loop clean. A few practical refinements that make the solution safer and more robust: handle DBNull.Value, escape embedded single quotes if the result will be embedded in SQL, avoid repeated string concatenation inside tight loops (use StringBuilder), and prefer parameterized or bulk operations when inserting many rows into a database.

A safer, more efficient helper that produces a comma-separated, single-quoted list suitable for a VALUES clause:

Private Function QuoteRowForSql(row As DataRow) As String
    Dim sb As New System.Text.StringBuilder()
    Dim items = row.ItemArray
    For i As Integer = 0 To items.Length - 1
        Dim v As String = If(IsDBNull(items(i)), String.Empty, items(i).ToString())
        v = v.Replace("'", "''") ' escape single quotes for SQL
        sb.Append("'"c).Append(v).Append("'"c)
        If i < items.Length - 1 Then sb.Append(",")
    Next
    Return sb.ToString()
End Function

If Excel will be used to consume the file, producing a CSV is often simpler and faster than building SQL INSERT text. CSV requires double-quote quoting and doubling internal quotes:

Private Function ToCsvLine(row As DataRow) As String
    Dim fields = row.ItemArray.Select(Function(o)
                                         Dim s = If(IsDBNull(o), String.Empty, o.ToString())
                                         s = s.Replace("""", """""")
                                         Return """" & s & """"
                                     End Function).ToArray()
    Return String.Join(",", fields)
End Function

Notes and troubleshooting: prefer streaming (StreamWriter) for large exports to avoid large in-memory strings; if inserting into MySQL, use parameterized commands or MySqlBulkLoader for bulk loads instead of concatenated VALUES; if Excel formatting or types matter, consider a library that writes native .xlsx files (for example ClosedXML) rather than interop or CSV. Finally, always validate/escape data and avoid building SQL from untrusted input.

Member Avatar for Oxiegen

Problem solved.
I simply added a funktion that returns the string containing the values.

By calling the funktion like this:

values = DataValues(row, dt.Columns.Count - 1)

The information is returned like this:

Private Function DataValues(ByVal DRow As DataRow, ByVal Max As Integer) As String
        Dim retVal As String

        retVal = "'" & DRow.Item(0) & "',"
        For i As Integer = 1 To Max - 1
            retVal &= "'" & DRow.Item(i) & "',"
        Next
        retVal &= "'" & DRow.Item(Max) & "'"

        Return retVal
    End Function
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.