从数据表中获取行值

Question

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("~/Upload/" & fileName)

'Check whether file extension is xls or xslx
If fileExtension = ".xls" Then
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
ElseIf fileExtension = ".xlsx" Then
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
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)("Table_Name").ToString()
cmd.CommandText = "SELECT * FROM [" & getExcelSheetName & "]"
dAdapter.SelectCommand = cmd
dAdapter.Fill(dtExcelRecords)
con.Close()

Dim row As DataRow
For Each row In dtExcelRecords.Rows
    If Include.ConvertDbNullToEmpyString(row(2)) = "" Then
        MsgBox("Error in saving!")
        Exit For

    End If
    MsgBox(row(0) & " " & row(1) & " " & row(2) & " " & row(3))
Next


GridView1.DataSource = dtExcelRecords

GridView1.DataBind()
ViewState("file") = dtExcelRecords


Dim dataSet As DataSet = New DataSet("dataSet")
dataSet.Tables.Add(dtExcelRecords)
'' Display the DataSet contents as XML.
Console.WriteLine(dataSet.Tables(0).DataSet.GetXml())

我有代码用于将文件 excel 上传到数据表并在 gridview 中显示。

在gridview中显示后，已转换为数据表并在gridview中显示的excel值将保存到数据库中。

我的问题：

Dim row As DataRow
For Each row In dtExcelRecords.Rows
    If Include.ConvertDbNullToEmpyString(row(2)) = "" Then
        MsgBox("Error in saving!")
        Exit For
    End If
    MsgBox(row(0) & " " & row(1) & " " & row(2) & " " & row(3))
Next

这是获取行值的代码。

但是如何用新的数据行设置数据表呢？

如果我在第 2 行中有空值，我希望向数据表输入数据的过程将停止！ 并将数据行值中的新值显示到 gridView 中。

- - - - - - - - - - - - - - - - - - - 编辑 - - - - - - -----------------------

示例：

上传 Excel 文件：

col1 ---- col2 ---- col3 ---- col4

tes1 ---- 测试1 ---- 测试1 ---- 测试1

tes2 ---- 测试2 ---- ---- 测试2

tes3 ---- 测试3 ---- 测试3 ---- 测试3

tes4 ---- 测试4 ---- ---- 测试4

和 gridview 的结果必须像这样：

col1 ---- col2 ---- col3 ---- col4

tes1 ---- 测试1 ---- 测试1 ---- 测试1

tes3 ---- 测试3 ---- 测试3 ---- 测试3

Answer 1

尝试，

                Dim dtData As DataTable
                dtData = New DataTable
                dtData.Columns.Add("Row0")
                dtData.Columns.Add("Row1")
                dtData.Columns.Add("Row2")
                dtData.Columns.Add("Row3")

                Dim row As DataRow
                For Each row In dtExcelRecords.Rows
                    If Include.ConvertDbNullToEmpyString(row(2)) = "" Then
                        MsgBox("Error in saving!")
                        Exit For 'or exit Sub
                    End If
                    Dim dr As DataRow = dtData.NewRow

                    dr("Row0") = row(0)
                    dr("Row1") = row(1)
                    dr("Row2") = row(2)
                    dr("Row3") = row(3)

                    dtData.Rows.Add(dr) 'Add this line

                Next

                'Bind dtData to gridview
                'Save data in dtData to database

Answer 2

如果您将 Exit For 更改为 Exit Sub 那么您将在此时停止处理