sqloledbdbfjetoledbcommand

How to creata a 2-column combination into a primary key in OLEDB with VB.NET


So I did an update in the "RPD" table and the database does not have a primary key then my solution must create a primary key in the "PNM" column but I can only update only 1 row so the solution is whether it can be by combining the "PNM" column with "NOD" then it becomes the primary key or is there another solution

Thanks

Table name : RPD These are the details with the product items

PNM NOD ITM QTY
1000 01 Product01 10
1000 02 Product01 15
1000 03 Product01 20
1001 01 Product01 15
1001 02 Product01 30

Table name : RPG This is the table master

PNM DATE CUSTNAME
1000 26-11-2022 A
1001 26-11-2022 B
Using cn As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" & Path & "; Extended Properties=dBase IV")
Using cmd As New OleDbCommand("ALTER TABLE RPD ADD CONSTRAINT idxMyTable PRIMARY KEY (PNM)", cn)
                cn.Open()
                cmd.ExecuteNonQuery()
End Using
End Using

Solution

  • As per this link : ALTER TABLE to add a composite primary key

      Using cn As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" & Path & "; Extended Properties=dBase IV")
            Using cmd As New OleDbCommand("ALTER TABLE RPD ADD CONSTRAINT idxMyTable PRIMARY KEY (PNM,NOD)", cn)
    
                cn.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using