资产数据集按钮

Question

我一直试图为一家我工作的公司编写一个程序，这将有助于跟踪以及概述的援助在哪里。

这样做的目的是要有一个数据库，数据库上的数字是唯一的，分配给所有硬件。

当一个“桌子”按钮被按下，它将显示该桌子的信息形式，它可以从那里编辑。

我大致有我想要的东西，但是当我更新数据的时候，我在努力解决一个问题，数据被放置在不正确的行中。

，例如：

桌面名称: TL1 PC : 0001显示器: 0002

桌面名称: TL2 PC : 0003显示器: 0004

如果我只更新第一个数据输入，它就能正常工作。但是，当我更新第二个条目时，它会被保存到第一个条目中。

，例如：

桌面名称: TL2 PC : 0003显示器: 0004

桌面名称: TL2 PC : 0003显示器: 0004

这是我到目前为止的代码，任何帮助都将不胜感激。

Public Class Form1
Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim MaxRows As Integer
Dim inc As Integer


Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: This line of code loads data into the 'Lib_9th_NLDataSet.tblContacts' table. You can move, or remove it, as needed.
    Me.TblContactsTableAdapter.Fill(Me.Lib_9th_NLDataSet.tblContacts)

    dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    dbSource = "Data Source = \\elite03\IT\Assets\Lib 9th NL.mdb"

    con.ConnectionString = dbProvider & dbSource

    con.Open()


    sql = "SELECT Desk,Pc,Monitor,Keyboard,Phone,Laptop FROM tblcontacts"
    da = New OleDb.OleDbDataAdapter(sql, con)
    da.Fill(ds, "Lib_9th_NL")



    con.Close()
    MaxRows = Lib_9th_NLDataSet.tblContacts.Rows(inc).Item(inc)
    inc = 0


End Sub

Private Sub NavigateRecords()


    txtDesk_Name.Text = ds.Tables("Lib_9th_NL").Rows(inc).Item(0)
    txtPC.Text = ds.Tables("Lib_9th_NL").Rows(inc).Item(1)
    txtMonintor.Text = ds.Tables("Lib_9th_NL").Rows(inc).Item(2)
    txtKeyboard.Text = ds.Tables("Lib_9th_NL").Rows(inc).Item(3)
    txtPhone.Text = ds.Tables("Lib_9th_NL").Rows(inc).Item(4)
    txtlaptop.Text = ds.Tables("Lib_9th_NL").Rows(inc).Item(5)

End Sub

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click

    Me.Validate()
    Me.TblContactsBindingSource.EndEdit()
    Me.TableAdapterManager.UpdateAll(Me.Lib_9th_NLDataSet)

End Sub
Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click


    If inc <> MaxRows - 0 Then

        inc = inc + 1

        NavigateRecords()

    Else
        MsgBox("No More Rows")

    End If
End Sub

Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
    If inc > 0 Then

        inc = inc - 1

        NavigateRecords()

    Else

        MsgBox("First Record")

    End If

End Sub

Private Sub btnAddNew_Click(sender As Object, e As EventArgs) Handles btnAddNew.Click
    btnCommit.Enabled = True
    btnUpdate.Enabled = False
    btnDelete.Enabled = False
    btnAddNew.Enabled = False


    txtDesk_Name.Clear()
    txtPC.Clear()
    txtMonintor.Clear()
    txtKeyboard.Clear()
    txtPhone.Clear()
    txtlaptop.Clear()
End Sub

Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
    btnCommit.Enabled = False
    btnAddNew.Enabled = True
    btnUpdate.Enabled = True
    btnDelete.Enabled = True

    inc = 0

    NavigateRecords()
End Sub

Private Sub btnCommit_Click(sender As Object, e As EventArgs) Handles btnCommit.Click
    If inc <> -1 Then

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow

        dsNewRow = ds.Tables("Lib_9th_NL").NewRow()


        dsNewRow.Item("desk") = txtDesk_Name.Text
        dsNewRow.Item("PC") = txtPC.Text
        dsNewRow.Item("Monitor") = txtMonintor.Text
        dsNewRow.Item("Keyboard") = txtKeyboard.Text
        dsNewRow.Item("Phone") = txtPhone.Text
        dsNewRow.Item("Laptop") = txtlaptop.Text

        ds.Tables("Lib_9th_NL").Rows.Add(dsNewRow)

        da.Update(ds, "Lib_9th_NL")

        MsgBox("New Record added to the Database")


        btnCommit.Enabled = False
        btnAddNew.Enabled = True
        btnUpdate.Enabled = True >
        btnDelete.Enabled = True

    End If
End Sub

Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
    If MessageBox.Show("Do you really want to Delete this Record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then

        MsgBox("Operation Cancelled")
        Exit Sub

    End If

    Dim cb As New OleDb.OleDbCommandBuilder(da)

    ds.Tables("Lib_9th_NL").Rows(inc).Delete()
    MaxRows = MaxRows - 1

    inc = 0

    da.Update(ds, "Lib_9th_NL")
    NavigateRecords()
End Sub

Private Sub TL1_Click(sender As Object, e As EventArgs) Handles TL1.Click
    txtDesk_Name.Text = Lib_9th_NLDataSet.tblContacts.Rows(0).Item(1)
    txtPC.Text = Lib_9th_NLDataSet.tblContacts.Rows(0).Item(2)
    txtMonintor.Text = Lib_9th_NLDataSet.tblContacts.Rows(0).Item(3)
    txtKeyboard.Text = Lib_9th_NLDataSet.tblContacts.Rows(0).Item(4)
    txtPhone.Text = Lib_9th_NLDataSet.tblContacts.Rows(0).Item(5)
    txtlaptop.Text = Lib_9th_NLDataSet.tblContacts.Rows(0).Item(6)
End Sub

Private Sub Ret1_Click(sender As Object, e As EventArgs) Handles Ret1.Click
    txtDesk_Name.Text = Lib_9th_NLDataSet.tblContacts.Rows(1).Item(1)
    txtPC.Text = Lib_9th_NLDataSet.tblContacts.Rows(1).Item(2)
    txtMonintor.Text = Lib_9th_NLDataSet.tblContacts.Rows(1).Item(3)
    txtKeyboard.Text = Lib_9th_NLDataSet.tblContacts.Rows(1).Item(4)
    txtPhone.Text = Lib_9th_NLDataSet.tblContacts.Rows(1).Item(5)
    txtlaptop.Text = Lib_9th_NLDataSet.tblContacts.Rows(1).Item(6)
End Sub

End Class

Answer 1

似乎您的DataSet和TableAdapterManager混淆了。

首先，我们需要从文本框中获取值。

            empID = EmployeeIDTxt.Text
            dob = DateOfBirthTxt.Text
            mailStation = MailStationTxt.Text
            eFirst = FNameTxt.Text
            prefName = PrefNameTxt.Text
            eLast = LNameTxt.Text
            homeAddress = Address1Txt.Text
            city = CityTxt.Text
            state = StateTxt.Text
            zip = ZipTxt.Text
            payGroup = PayGroupTxt.Text
            fileNo = FileNoTxt.Text
            begDT = BegDtTxt.Text

现在，我们可以将更新查询中的字段作为变量引用，并向它们添加参数，以防止SQL Injection。

     'This is your update statement
     Dim updateQry As String = String.Empty 
     updateQry = "UPDATE YOURTABLE"
     updateQry &= " SET  EMPL_ID = @EmployeeID,EMPL_LAST_NM = @LastName, EMPL_FIRST_NM = @FirstName, EMPL_PREFRD_NM = @PrefName,"
     updateQry &= "      EMPL_BIRTH_DT = @DateOfBirth, EMPL_MAIL_STN_CD = @MS, EMPL_ADDR1_TXT = @HomeAddress,"
     updateQry &= "       EMPL_ADDR2_TXT = @Add2, EMPL_CITY_NM = @City, EMPL_STATE_CD = @State, EMPL_POSTL_CD = @Zip,"
     updateQry &= " WHERE EMPL_ID = @EmployeeID ;"

     'Declare Connection String
      Using sqlConnection As New OleDBConnection(myConn)
         Using cmd As New OleDBCommand()
             'Declare variable for SQL command
                With cmd
                   .Connection = sqlConnection
                   .CommandType = CommandType.Text
                   .CommandText = updateQry
                   'Prevent against SQL Injection -> Add Parameters
                     With .Parameters
                        .AddWithValue("@EmployeeID", empID)
                        .AddWithValue("@FirstName", eFirst)
                        .AddWithValue("@LastName", eLast)
                        .AddWithValue("@PrefName", prefName)
                        .AddWithValue("@DateOfBirth", dob)
                        .AddWithValue("@MS", mailStation)
                        .AddWithValue("@HomeAddress", homeAddress)
                        .AddWithValue("@City", city)
                        .AddWithValue("@State", state)
                        .AddWithValue("@Zip", zip)
                     End With
                End With

                Try
                    sqlConnection.Open()
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MessageBox.Show("Error Updating " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
       End Using
   End If
Catch ex As Exception
    MsgBox(ex.Message)
 Finally
    MsgBox("" & FNameTxt.Text & " " & LNameTxt.Text & " has been updated")
End Try

现在，我们需要改变一些事情。

1. 用连接字符串替换myConn，并在这个实例中打开它(在btnUpdate子中)
2. 将“参数”列表中的所有字段更改为变量。("EmployeeID“是我为变量empID提供的参数)
3. 您的WHERE子句将取决于要更新哪些记录。那得由你来决定。是否有一个主键可以像我一样引用EmployeeID？

用字段名替换EMPL_字段。为了澄清这一点，"@EmployeeID“参数是直接向数据库添加empID的安全方法。

还有其他几种方法可以做到这一点，比如OleDbUpdateCommand等等。

希望这能帮到你。