problem with query

[mie.ilani]

i have problem with query the database.
i want to add data into two table that share same Primary key. here my code

error message :The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

 Dim con As New OleDb.OleDbConnection(My.Settings.KK3DB)
        Dim com As New OleDb.OleDbCommand
        Dim adap As New OleDb.OleDbDataAdapter
        Dim ds As New DataSet
        con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\User\Desktop\dia\14052012\KK3MailingSystem\KK3MailingSystem\db\KK3MailingSystem.mdb;Persist Security Info=True")
        con.Open()

        com.CommandText = " INSERT INTO Staff (Staff_Id,Staff_Name, Staff_IC,Staff_Ext,Staff_HP,Staff_Email,Staff_Position) VALUES ('" + txtId.Text + "','" + txtName.Text + "','" + txtIC.Text + "','" + txtExt.Text + "','" + txtHP.Text + "','" + txtEmail.Text + "','" + txtPosition.Text + "')"
        com.Connection = con
        com.ExecuteNonQuery()< error here

        com.CommandText &= " INSERT INTO Login (Staff_Id, Staff_Username) VALUES ('" + txtId.Text + "','" + txtUsername.Text + "')"
        com.Connection = con
        com.ExecuteNonQuery()
        adap = New OleDbDataAdapter("select * from Staff", con)
        adap.Fill(ds, "1")
        StaffDataGridView.DataSource = ds.Tables("1")
        Me.StaffDataGridView.Rows(Me.StaffDataGridView.RowCount - 2).Selected = True

[JorgeM]

Did you check the SQL tables to make sure that you are not tyring to insert a value for your primary key field that already exists in the table(s)?

[mie.ilani]

i think the mistake is when i try to insert the data on PK. so what query should i used?

[Reverend Jim]

It's not a query problem. It's a data problem. You are trying to insert a record with a primary key that already exists. It's like trying to store two different values into an array with the same index, except that in the array mode you just end up overwriting the existing value. At least SQL is nice enough to prevent you from doing that.

[jgat2011]

mie.ilani, Reverend Jim gave the answer for your problem. It would be preferable for you to put the primary key to be an auto increment field so that when inserting, you can avoir that mistake of having duplicate values.

[lolafuertes]

@jgat2011: Having an auto increment field as primary key is not always a good idea. Ex: assume a table for the states. If you define PK SatateId as integer autoincrement, and a field stateName as a string, you can insert the same stateName in the table without error.

Continuing from the Reverend Jim, maybe need to use SQL sentences like:
IF NOT EXISTS (SELECT Staff_Id FROM Staff) INSERT INTO Staff (Staff_Id,Staff_Name, Staff_IC,Staff_Ext,Staff_HP,Staff_Email,Staff_Position) VALUES ('" + txtId.Text + "','" + txtName.Text + "','" + txtIC.Text + "','" + txtExt.Text + "','" + txtHP.Text + "','" + txtEmail.Text + "','" + txtPosition.Text + "') ELSE UPDATE Staff SET Staff_Name = '" + txtName.Text + "', Staff_IC = '" + txtIC.Text + "', Staff_Ext = '" + txtExt.Text + "', Staff_HP = '" + txtHP.Text + "', Staff_Email = '" + txtEmail.Text + "', Staff_Position = '" + txtPosition.Text + "' WHERE Staff_id = '" + txtId.Text + "';"
where, in first place, searches for the id if exists, then if not exists inserts, but if already exists updates.

Hope this helps

[mie.ilani]

urm, can i kow how to replace code EXIST. im using microsoft access 2003.

 Public Sub doSave()
        Dim con As New OleDb.OleDbConnection(My.Settings.KK3DB)
        Dim com As New OleDb.OleDbCommand
        Dim adap As New OleDb.OleDbDataAdapter
        Dim ds As New DataSet
        con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\backup project\KK3MailingSystem\KK3MailingSystem\KK3MailingSystem\db\KK3MailingSystem.mdb;Persist Security Info=True")
        con.Open()

        If Not EXISTS Then <error here
            com.CommandText = "(SELECT Staff_Id FROM Staff) INSERT INTO Staff (Staff_Id,Staff_Name, Staff_IC,Staff_Ext,Staff_HP,Staff_Email,Staff_Position) VALUES ('" + txtId.Text + "','" + txtName.Text + "','" + txtIC.Text + "','" + txtExt.Text + "','" + txtHP.Text + "','" + txtEmail.Text + "','" + cboPosition.Text + "') "
        Else
            com.CommandText = "UPDATE Staff SET Staff_Name = '" + txtName.Text + "', Staff_IC = '" + txtIC.Text + "', Staff_Ext = '" + txtExt.Text + "', Staff_HP = '" + txtHP.Text + "', Staff_Email = '" + txtEmail.Text + "', Staff_Position = '" + cboPosition.Text + "' WHERE Staff_id = '" + txtId.Text + "';"
        End If
        com.Connection = con
        com.ExecuteNonQuery()
        adap = New OleDbDataAdapter("Select * from Staff", con)
        adap.Fill(ds, "1")
        StaffDataGridView.DataSource = ds.Tables("1")
        Me.StaffDataGridView.Rows(Me.StaffDataGridView.RowCount - 2).Selected = True
        con.Close()

[Reverend Jim]

You can check to see if a record exists by doing a SELECT on the primary key then checking to see if a record was returned. If the query returns a record then you can modify it using an UPDATE query. If a record is not returned then you can do an INSERT query.