Asset Dataset Button

Question

I have been trying to write a program for a company that I work for, that would do assist tracking as well as give an overview of where the assists are.

The idea is to have a database with unique numbers on them that is allocated to all the hardware.

When a "desk" button is pressed it will show the info form that desk and it can be edited from there.

I roughly have what I was looking for, but I'm struggling with an issue when I update data, the data is somehow placed in the incorrect row...

E.g:

Desk Name : TL1 PC : 0001 monitor : 0002

Desk Name : TL2 PC : 0003 monitor : 0004

If I update the fist data entry alone it works fine.. however when I update the second entry it gets saved onto the first entry

E.g:

Desk Name : TL2 PC : 0003 monitor : 0004

Desk Name : TL2 PC : 0003 monitor : 0004

This is the code I have so far, any help would be very appreciated.

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

It seems like you might be getting mixed up between your DataSet and your TableAdapterManager.

First, we need to get the values from the text boxes.

            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

Now, we can reference the fields in our Update query as variables, and add parameters to them so we can prevent 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

Now, we need to change a few things.

1. Replace myConn with your connection string and open it in this instance (in the btnUpdate sub)
2. Change all of the fields in the Parameter list to your variables. ("EmployeeID" is the parameter I am providing for the variable empID)
3. Your WHERE clause is going to depend on what records you want to update. That's for you to decide. Is there a primary key you can reference like I do with EmployeeID?

Replace EMPL_ fields with your field names. For clarification, the "@EmployeeID" parameter is the safe way of writing to the database of adding empID directly.

There are a couple other ways to do this, such as OleDbUpdateCommand, etc.

Hope this helps you.