title: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010
body: <p>I need to import records from a comma delimited CSV file into an existing table in SQL Server R2 database using Visual Basic .Net 2010. Existing records in the table are to be deleted prior to import. I have been able to create an in-memory temporary DataTable and populate the records from CSV file using TextFieldParser. I have checked it by binding the in-memory DataTable to a DataGridView. But I am clueless in the second part i.e. how to insert records into the SQL table from the in-memory DataTable.</p>
  
  <p>I have done the following:</p>
  
  <pre><code>`Dim TextFileReader As New TextFieldParser("C:\csvtosql\StockVB\VFPFiles\ExpSysusers.csv")
  
  TextFileReader.TextFieldType = FileIO.FieldType.Delimited
  TextFileReader.SetDelimiters(",")
  
  Dim TextFileTable As DataTable = Nothing
  
  Dim Column As DataColumn
  Dim Row As DataRow
  Dim UpperBound As Int32
  Dim ColumnCount As Int32
  Dim CurrentRow As String()
  
  CurrentRow = TextFileReader.ReadFields() ' Ignore the header
  While Not TextFileReader.EndOfData
      Try
          CurrentRow = TextFileReader.ReadFields()
  
          If Not CurrentRow Is Nothing Then
              ''# Check if DataTable has been created
              If TextFileTable Is Nothing Then
                  TextFileTable = New DataTable("TextFileTable")
                  ''# Get number of columns
                  UpperBound = CurrentRow.GetUpperBound(0)
                  ''# Create new DataTable
                  For ColumnCount = 0 To UpperBound
                      Column = New DataColumn()
                      Column.DataType = System.Type.GetType("System.String")
                      Column.ColumnName = "Column" &amp; ColumnCount
                      Column.Caption = "Column" &amp; ColumnCount
                      Column.ReadOnly = True
                      Column.Unique = False
                      TextFileTable.Columns.Add(Column)
                  Next
              End If
  
              Row = TextFileTable.NewRow
              For ColumnCount = 0 To UpperBound
                  Row("Column" &amp; ColumnCount) = CurrentRow(ColumnCount).ToString
              Next
              TextFileTable.Rows.Add(Row)
  
          End If
  
      Catch ex As Exception
          MsgBox("Line " &amp; ex.Message &amp; "is not valid and will be skipped.")
      End Try
  End While
  TextFileReader.Dispose()
  DataGridView1.DataSource = TextFileTable
  </code></pre>
  
  <p>`
  Can anybody please help/guide me??</p>
  
  <p>I tried the following code to read the records from the DataTable and insert into the SQL Table. But it seems that only the first record is being added.</p>
  
  <pre><code>For Each TextFileTableDataRow As DataRow In TextFileTable.Rows
          Dim Column0 As String = TextFileTableDataRow("Column0")
          Dim Column1 As String = TextFileTableDataRow("Column1")
          Dim Column2 As Int16 = TextFileTableDataRow("Column2")
          Dim Column3 As Boolean = TextFileTableDataRow("Column3")
  
          Dim strSqlQry As String = "INSERT INTO Personnel (Operator,OpPassword,SecurityLevel,Active) VALUES (@Operator,@OpPassword,,@SecurityLevel,@Active)"
          Dim SqlconnectionString As String = gcconnect
  
          Using connection As New SqlClient.SqlConnection(SqlconnectionString)
              Dim cmd As New SqlClient.SqlCommand(strSqlQry, connection)
  
              ' create command objects and add parameters
              With cmd.Parameters
                  .AddWithValue("@Operator", Column0)
                  .AddWithValue("@OpPassword", Column1)
                  .AddWithValue("@SecurityLevel", Column3)
                  .AddWithValue("@LoggedOn", Column7)
              End With
  
              Dim adapter As New SqlClient.SqlDataAdapter()
              adapter.InsertCommand = cmd
  
              '--Update the original SQL table from the datatable
              Dim iRowsInserted As Int32 = adapter.Update(TextFileTable)
          End Using
      Next
  </code></pre>
  
  <p>I am getting the following error:
  "Violation of PRIMARY KEY Constraint 'PK_Personnel'. Cannot insert duplicate key in object 'dbo.Personnel'."</p>