Insert Into Access Database - Primary Key

[cday119]

Can someone help me out with this one. I am trying to insert a row of data into an access database. The primary key is set to auto increment. It will work once but the next time you try an insert it says. "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. " This error occurs on the daComputers.Upd ate(dsHardware, "Computers" ) line

Here is the code:

Dim row As DataRow = dsHardware.Tables("Computers").NewRow
        row("Style") = cmbAddStyle.SelectedItem.Key.ToString
        row("Location") = cmbAddLocation.SelectedItem.key.ToString
        row("Sys_ID") = txtAddName.Text
        row("Assignment") = txtAddOwner.Text
        row("Dell_Tag") = txtAddTag.Text
        row("Processor") = txtAddProcessor.Text
        row("RAM") = txtAddRam.Text
        row("Hard_Drive") = txtAddHardDrive.Text
        row("Windows_OS") = txtAddos.Text
        row("Role") = txtAddRole.Text
        row("Netware") = chkAddNetware.Checked
        dsHardware.Tables("Computers").Rows.Add(row)

 'Update the dataset to the values of the textboxes
          dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("Sys_ID") = txtDeviceName.Text
         dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("Assignment") = txtOwner.Text
         dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("Dell_Tag") = txtServiceTag.Text
        dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("RecordNum") = DBNull.Value
         dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("Processor") = txtProcessor.Text
         dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("RAM") = txtRAM.Text
         dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("Hard_Drive") = txtHardDrive.Text
         dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("Windows_OS") = txtOS.Text
         dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("Role") = txtRole.Text
         dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("Decommissioned") = cbxDecomm.Checked
         dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("Netware") = cbxNetware.Checked


        'Update the dataset to the values of the comboboxes
        If Not cmbLocation.SelectedIndex = -1 Then
            dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("Location") = cmbLocation.SelectedItem.Key.ToString
        End If
        If Not cmbStyle.SelectedIndex = -1 Then
            dsHardware.Tables("Computers").Rows(dgvComputers.CurrentRow.Index).Item("Style") = cmbStyle.SelectedItem.Key.ToString
        End If

        'End the editing of the dataset so we can update the database
        Me.BindingContext(dsHardware, "Computers").EndCurrentEdit()
        Me.BindingContext(dsHardware, "Style").EndCurrentEdit()
        Me.BindingContext(dsHardware, "Location").EndCurrentEdit()

        'Create SQL Query
        Dim query As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(daComputers)
        query.GetUpdateCommand()
        daComputers.UpdateCommand = query.GetUpdateCommand

        'And Update the database - If creation of location or style ever occurs wrap the updates in the sql catcher and update the database all at once
        daComputers.Update(dsHardware, "Computers")

Thanks

[madankarmukta]

what is this dgvComputers.. you take help of ..?

Thanks

[cday119]

A datagridview. I have been trying some other things. Here is the newest way that I am doing it. I basically changed the update command of the dataadapter to a insert command.

 Dim row As DataRow = dsHardware.Tables("Computers").NewRow
        row("Style") = cmbAddStyle.SelectedItem.Key.ToString
        row("Location") = cmbAddLocation.SelectedItem.key.ToString
        row("Sys_ID") = txtAddName.Text
        row("Assignment") = txtAddOwner.Text
        row("Dell_Tag") = txtAddTag.Text
        row("Processor") = txtAddProcessor.Text
        row("RAM") = txtAddRam.Text
        row("Hard_Drive") = txtAddHardDrive.Text
        row("Windows_OS") = txtAddos.Text
        row("Role") = txtAddRole.Text
        row("Netware") = chkAddNetware.Checked
        row("RecordNum") = dsHardware.Tables("Computers").Compute("Max(RecordNum)", Nothing) + 1
        dsHardware.Tables("Computers").Rows.Add(row)
        MsgBox(row("RecordNum").ToString)
        MsgBox(dsHardware.Tables("Computers").Rows(dsHardware.Tables("Computers").Rows.Count - 1)("RecordNum").ToString)
        

'End the editing of the dataset so we can update the database
        Me.BindingContext(dsHardware, "Computers").EndCurrentEdit()
        Me.BindingContext(dsHardware, "Style").EndCurrentEdit()
        Me.BindingContext(dsHardware, "Location").EndCurrentEdit()

        'Create SQL Query
        Dim query As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(daComputers)
        query.GetInsertCommand()
        daComputers.InsertCommand = query.GetInsertCommand

        'And Update the database - If creation of location or style ever occurs wrap the updates in the sql catcher and update the database all at once
        daComputers.Update(dsHardware, "Computers")

This code has the same outcome. "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. " This error occurs on the daComputers.Upd ate(dsHardware, "Computers" ) line

I think it comes from the dataset or datadapter not knowing that I inserted a row, and it is creating another row with the same primary key.