title: getting row value from datatable
body: <pre><code>Dim flag As Boolean = True
  
  Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
  
  Dim fileExtension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
  
  Dim fileLocation As String = Server.MapPath(&quot;~/Upload/&quot; &amp; fileName)
  
  'Check whether file extension is xls or xslx
  If fileExtension = &quot;.xls&quot; Then
      connectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; &amp; fileLocation &amp; &quot;;Extended Properties=&quot;&quot;Excel 8.0;HDR=Yes;IMEX=2&quot;&quot;&quot;
  ElseIf fileExtension = &quot;.xlsx&quot; Then
      connectionString = &quot;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=&quot; &amp; fileLocation &amp; &quot;;Extended Properties=&quot;&quot;Excel 12.0;HDR=Yes;IMEX=2&quot;&quot;&quot;
  End If
  
  'Create OleDB Connection and OleDb Command
  
  Dim con As New OleDbConnection(connectionString)
  Dim cmd As New OleDbCommand()
  cmd.CommandType = System.Data.CommandType.Text
  cmd.Connection = con
  Dim dAdapter As New OleDbDataAdapter(cmd)
  Dim dtExcelRecords As New DataTable()
  con.Open()
  Dim dtExcelSheetName As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
  Dim getExcelSheetName As String = dtExcelSheetName.Rows(0)(&quot;Table_Name&quot;).ToString()
  cmd.CommandText = &quot;SELECT * FROM [&quot; &amp; getExcelSheetName &amp; &quot;]&quot;
  dAdapter.SelectCommand = cmd
  dAdapter.Fill(dtExcelRecords)
  con.Close()
  
  Dim row As DataRow
  For Each row In dtExcelRecords.Rows
      If Include.ConvertDbNullToEmpyString(row(2)) = &quot;&quot; Then
          MsgBox(&quot;Error in saving!&quot;)
          Exit For
  
      End If
      MsgBox(row(0) &amp; &quot; &quot; &amp; row(1) &amp; &quot; &quot; &amp; row(2) &amp; &quot; &quot; &amp; row(3))
  Next
  
  
  GridView1.DataSource = dtExcelRecords
  
  GridView1.DataBind()
  ViewState(&quot;file&quot;) = dtExcelRecords
  
  
  Dim dataSet As DataSet = New DataSet(&quot;dataSet&quot;)
  dataSet.Tables.Add(dtExcelRecords)
  '' Display the DataSet contents as XML.
  Console.WriteLine(dataSet.Tables(0).DataSet.GetXml())
  </code></pre>
  <p>I had code which use to upload file excel into datatable and show at gridview.</p>
  <p>After show at gridview, the excel value which has been convert into datatable and showed at gridview will be saved to database.</p>
  <p>My problem :</p>
  <pre><code>Dim row As DataRow
  For Each row In dtExcelRecords.Rows
      If Include.ConvertDbNullToEmpyString(row(2)) = &quot;&quot; Then
          MsgBox(&quot;Error in saving!&quot;)
          Exit For
      End If
      MsgBox(row(0) &amp; &quot; &quot; &amp; row(1) &amp; &quot; &quot; &amp; row(2) &amp; &quot; &quot; &amp; row(3))
  Next
  </code></pre>
  <p>that was code to get the row value.</p>
  <p>But how to set datatable with new data row?</p>
  <p>If I had null value in row 2, I wanted proses input data to datatable will stoped!
  And showing with new value from datarow value into gridView.</p>
  <p>-------------------------------------- EDIT ----------------------------------</p>
  <p>example :</p>
  <p>upload file excel :</p>
  <blockquote>
  <p>col1 ---- col2 ---- col3 ---- col4</p>
  </blockquote>
  <blockquote>
  <p>tes1 ---- test1 ---- test1 ---- test1</p>
  </blockquote>
  <blockquote>
  <p>tes2 ---- test2 ----       ---- test2</p>
  </blockquote>
  <blockquote>
  <p>tes3 ---- test3 ---- test3 ---- test3</p>
  </blockquote>
  <blockquote>
  <p>tes4 ---- test4 ----       ---- test4</p>
  </blockquote>
  <p>and the result at gridview must like this :</p>
  <blockquote>
  <p>col1 ---- col2 ---- col3 ---- col4</p>
  </blockquote>
  <blockquote>
  <p>tes1 ---- test1 ---- test1 ---- test1</p>
  </blockquote>
  <blockquote>
  <p>tes3 ---- test3 ---- test3 ---- test3</p>
  </blockquote>