I think this is what you are looking for.
' The following two statements
' remove surrounding apostrophes in the two name fields. Later,
' via String.Format, we will put them back after we have replaced
' any embedded apostrophes with double apostrophes. This handles
' names that contain embedded apostrophes. If you already did this
' when the input file was created, then you do not need the two
' .Replace("'","''")
' methods in the WriteLine statement below.
Dim strTemp1 As String = oItem.SubItems(1).Text.Substring(1, oItem.SubItems(1).Text.Length - 2)
Dim strTemp2 As String = oItem.SubItems(2).Text.Substring(1, oItem.SubItems(2).Text.Length - 2)
csv.WriteLine(String.Format("{0} {1} {2}'{3}','{4}','{5}',{6},'{7}','{8}'{9}", _
"INSERT INTO", table_name, "VALUES(", _
oItem.Text, created, created_by, _
oItem.SubItems(0).Text, _
strTemp1.Replace("'", "''"), _
strTemp2.Replace("'", "''"), _
");"))
Note:
The following in the above code handles the case where there is an embedded apostrophe in a name (example: O'Toole):
strTemp1.Replace("'", "''"), _
strTemp2.Replace("'", "''"), _
Tested: Visual Basic .NET 2012
__________________________________________________________________________________________________
The following was added to the Solutiuon after the original poster asked about making the program to generate the INSERT statements dynamic so that it could generate INSERT statements for different tables with different columns.
____________________________________________________
The following code is from a program that I wrote more than 10 years ago. It is not the complete program but is enough to show you how to get the tables names of a selected database and then the columns for a selected table. Using this information, you should be able to discern how to create a program to dynamically generate the INSERT statements that you require.
Dim cn As New SqlConnection
Dim bDatabaseOpen As Boolean = False
Const SELECTCOLUMNS As String = "SELECT column_name, is_nullable,data_type,character_maximum_length, numeric_precision,numeric_scale From INFORMATION_SCHEMA.Columns Where Table_Name = '!TABLENAME' And Table_Catalog='!DATABASENAME' Order By Ordinal_Position;"
Const SELECTTABLES As String = "SELECT table_name From INFORMATION_SCHEMA.Tables Where Table_Catalog='!DATABASENAME' and NOT (table_name like 'dt%' or table_name like 'sys%') Order By Table_Name;"
Const SELECTDATABASES As String = "SELECT DISTINCT name From sys.sysdatabases Where name <> 'master' and name <> 'model' and name <> 'msdb' and name <> 'tempdb' and name<>'ReportServer$SQLExpress' and name<>'ReportServer$SQLExpressTempDB' and name<>'Christmas' Order by name;"
Private Sub Form_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.Height = 344
If Me.DesignMode Then Exit Sub
Call OpenDatabase("MSDB")
Call LoadComboBox(SELECTDATABASES, cmbDatabases, "name")
Call CloseDatabase()
End Sub
Private Sub OpenDatabase(ByVal strDatabase As String)
Dim CONNECTIONSTRING As String = "Database=" & strDatabase & ";Server=sage\sqlexpress;Connect Timeout=4;Integrated Security=True;Net=dbmssocn;Application Name=SQLGenerator;"
If (Not bDatabaseOpen) Then
Try
cn.ConnectionString = CONNECTIONSTRING
cn.Open()
bDatabaseOpen = True
Catch myException As SqlException
Call ShowODBCException(myException)
End Try
End If
End Sub
PRivate Sub CloseDatabase()
Try
cn.Close()
Catch
End Try
Try
cn.Dispose()
Catch
End Try
bDatabaseOpen = False
End Sub
Private Sub cmbDatabases_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbDatabases.SelectedValueChanged
cmbDatabases.Enabled = False
If CType(cmbDatabases.SelectedItem, String).Length > 0 Then
Call OpenDatabase(CType(cmbDatabases.SelectedItem, String))
Call LoadComboBox(Replace(SELECTTABLES, "!DATABASENAME", CType(cmbDatabases.SelectedItem, String)), cmbTables, "table_name")
If cmbTables.Items.Count > 0 Then
cmbTables.Focus()
cmbTables.SelectedIndex = 0
End If
End If
End Sub
Private Sub LoadComboBox(ByVal strSQL As String, ByRef cmb As ComboBox, ByVal strKeyName As String)
Dim obcommand As SqlCommand = Nothing
Dim rs As SqlDataReader = Nothing
Dim bOK As Boolean = False
Try
obcommand = New SqlCommand(strSQL, cn)
rs = obcommand.ExecuteReader
bOK = True
Catch myException As SqlException
Call ShowODBCException(myException)
End Try
If bOK Then
While rs.Read()
cmb.Items.Add(DirectCast(rs(strKeyName), String))
End While
End If
Try
rs.Close()
Catch
End Try
obcommand.Dispose()
End Sub
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
Call CreateInsert(CType(cmbDatabases.SelectedItem, String), CType(cmbTables.SelectedItem, String))
End Sub
Sub CreateInsert(ByVal strDatabase As String, ByVal strTable As String)
Dim obcommand As SqlCommand = Nothing
Dim rs As SqlDataReader = Nothing
Dim bOK As Boolean = False
Dim intTotal As Integer
Dim intDecimal As Integer
Dim strColumnName As String
Dim strType As String
Dim bIsNullable As Boolean
Dim strPrepareData As String = ""
Dim StrPrepareData2 As String = ""
Dim strInsert As String = "strSQL=String.Concat(" & Chr(34) & "Insert into " & strTable & "("
Dim strSQL As String = Replace(Replace(SELECTCOLUMNS, "!DATABASENAME", strDatabase), "!TABLENAME", strTable)
Try
obcommand = New SqlCommand(strSQL, cn)
rs = obcommand.ExecuteReader
bOK = True
Catch myException As SqlException
Call ShowODBCException(myException)
End Try
If bOK Then
While rs.Read()
strInsert += DirectCast(rs("column_name"), String) & ", "
End While
strInsert = strInsert.Substring(0, strInsert.Length - 2) & ") " & Chr(34) & " , _" & vbNewLine & Chr(34) & "Values (" & Chr(34) & ", _" & vbNewLine
rs.Close()
obcommand.Dispose()
bOK = False
Try
obcommand = New SqlCommand(strSQL, cn)
rs = obcommand.ExecuteReader
bOK = True
Catch myException As SqlException
Call ShowODBCException(myException)
End Try
If bOK Then
While rs.Read()
strColumnName = DirectCast(rs("column_name"), String)
strType = DirectCast(rs("data_type"), String).ToUpper
bIsNullable = CBool(DirectCast(rs("is_nullable"), String).ToUpper = "YES")
Select Case strType
Case "INT", "BIGINT", "TINYINT", "SMALLINT"
If bIsNullable Then
strInsert += "str" & strColumnName
strPrepareData += "Friend str" & strColumnName & " As String" & vbNewLine
StrPrepareData2 += "txt" & strColumnName & ".text=txt" & strColumnName & ".text.trim" & vbNewLine & _
"if txt" & strColumnName & ".text.length=0 Then" & vbNewLine & _
" str" & strColumnName & "=" & Chr(34) & "Null" & Chr(34) & vbNewLine & _
"Else" & vbNewLine & _
" str" & strColumnName & "=txt" & strColumnName & ".text" & vbNewLine & _
"End If" & vbNewLine
Else
strInsert += Chr(34) & "0" & Chr(34) & " , txt" & strColumnName & ".text"
End If
Case "NVARCHAR", "VARCHAR", "CHAR", "TEXT"
If bIsNullable Then
strInsert += "str" & strColumnName
strPrepareData += "Friend str" & strColumnName & " As String" & vbNewLine
StrPrepareData2 += "txt" & strColumnName & ".text=txt" & strColumnName & ".text.trim" & vbNewLine & _
"if txt" & strColumnName & ".text.length=0 Then" & vbNewLine & _
" str" & strColumnName & "=" & Chr(34) & "Null" & Chr(34) & vbNewLine & _
"Else" & vbNewLine & _
" str" & strColumnName & "=string.concat(" & Chr(34) & "'" & Chr(34) & ", REPLACE(txt" & strColumnName & ".text," & Chr(34) & "'" & Chr(34) & "," & Chr(34) & "''" & Chr(34) & ") , " & Chr(34) & "'" & Chr(34) & ")" & vbNewLine & _
"End If" & vbNewLine
Else
strInsert += Chr(34) & " '" & Chr(34) & ", REPLACE(txt" & strColumnName & ".text.trim," & Chr(34) & "'" & Chr(34) & "," & Chr(34) & "''" & Chr(34) & ") , " & Chr(34) & "'" & Chr(34)
End If
Case "DATETIME", "SMALLDATETIME"
Dim strFormat As String
If strColumnName.ToUpper.IndexOf("TIME") > 0 Then
strFormat = "HH:mm:ss"
Else
strFormat = "yyyy-MM-dd"
End If
If bIsNullable Then
strInsert += "str" & strColumnName
strPrepareData += "Friend str" & strColumnName & " As String" & vbNewLine
StrPrepareData2 += "txt" & strColumnName & ".text=txt" & strColumnName & ".text.trim" & vbNewLine & _
"if txt" & strColumnName & ".text.length=0 Then" & vbNewLine & _
" str" & strColumnName & "=" & Chr(34) & "Null" & Chr(34) & vbNewLine & _
"Else" & vbNewLine & _
" str" & strColumnName & "=string.concat(" & Chr(34) & "'" & Chr(34) & ", FORMAT$(CDATE(txt" & strColumnName & ".text)," & Chr(34) & strFormat & Chr(34) & ") , " & Chr(34) & "'" & Chr(34) & ")" & vbNewLine & _
"End If" & vbNewLine
Else
strInsert += Chr(34) & "'" & Chr(34) & " , " & "FORMAT$(CDATE(txt" & strColumnName & ".text.trim)," & Chr(34) & strFormat & Chr(34) & ")" & " , " & Chr(34) & "'" & Chr(34)
End If
Case "DECIMAL", "NUMERIC"
intTotal = CInt(rs("numeric_precision"))
intDecimal = CInt(rs("numeric_scale"))
If bIsNullable Then
strInsert += "str" & strColumnName
strPrepareData += "Friend str" & strColumnName & " As String" & vbNewLine
StrPrepareData2 += "txt" & strColumnName & ".text=txt" & strColumnName & ".text.trim" & vbNewLine & _
"if txt" & strColumnName & ".text.length=0 Then" & vbNewLine & _
" str" & strColumnName & "=" & Chr(34) & "Null" & Chr(34) & vbNewLine & _
"Else" & vbNewLine & _
" str" & strColumnName & "=FORMAT$(CDBL(REPLACE(REPLACE(txt" & strColumnName & ".text.trim," & Chr(34) & "$" & Chr(34) & "," & Chr(34) & Chr(34) & ")," & Chr(34) & "," & Chr(34) & "," & Chr(34) & Chr(34) & "))," & Chr(34) & "".PadLeft(intTotal, CChar("#")) & DirectCast(IIf(intDecimal > 0, ".", ""), String) & "".PadLeft(intDecimal, CChar("#")) & Chr(34) & ")" & vbNewLine & _
"End If" & vbNewLine
Else
strInsert += Chr(34) & "0" & Chr(34) & " , FORMAT$(CDBL(REPLACE(REPLACE(txt" & strColumnName & ".text.trim," & Chr(34) & "$" & Chr(34) & "," & Chr(34) & Chr(34) & ")," & Chr(34) & "," & Chr(34) & "," & Chr(34) & Chr(34) & "))," & Chr(34) & "".PadLeft(intTotal, CChar("#")) & DirectCast(IIf(intDecimal > 0, ".", ""), String) & "".PadLeft(intDecimal, CChar("#")) & Chr(34) & ")"
End If
Case Else
Debug.WriteLine("Unknown Data Type: [" & strColumnName & "] " & DirectCast(rs("data_type"), String).ToUpper)
strInsert += "[" & strColumnName & "] " & DirectCast(rs("data_type"), String).ToUpper
End Select
strInsert += " , " & Chr(34) & "," & Chr(34) & " , _" & vbNewLine
End While
strInsert = strInsert.Substring(0, strInsert.Length - 11) & " & " & Chr(34) & ");" & Chr(34) & ")" & vbNewLine
End If
End If
Try
rs.Close()
Catch
End Try
Try
obcommand.Dispose()
Catch
End Try
Clipboard.SetDataObject(strPrepareData & vbNewLine & vbNewLine & StrPrepareData2 & vbNewLine & vbNewLine & strInsert, True)
MsgBox("SQL Insert is on the clipboard", MsgBoxStyle.Information, "SQL")
End Sub