Trying to set a primary key within a dataset.

[mechwarrior3]

I have an MS Access 2003 database that I am trying to interact with using ADO .NET methods. I generate a data adapter complete with select, insert and update commands. Now, this database does not have a primary key defined. Now, when I want to update a row in the database, I am trying to use the .Find method. However, the .Find method requires a primary key so what I am trying to do is set a primary key within the datatable that is retrieved from the database through my SELECT command. However, this isn't working. Here is my code:

[SIZE=2]AccessAdapter.SelectCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand("SELECT * FROM tbl_HCI_Lot_Inspection_Data", AccessConn)
[/SIZE][SIZE=2]AccessAdapter.Fill(AccessDataSet, "tbl_HCI_Lot_Inspection_Data")
AccessDataSet.Tables("tbl_HCI_Lot_Inspection_Data").PrimaryKey = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataColumn() _[/SIZE]
[SIZE=2]{AccessDataSet.Tables("tbl_HCI_Lot_Inspection_Data").Columns("strLotNumber"), AccessDataSet.Tables("tbl_HCI_Lot_Inspection_Data").Columns("strMixID")}[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strMixNum [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = txtMixLotNum.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strMfgNum [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = txtMfgLotNum.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] keyArray() [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = {strMfgNum, strMixNum}
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessDataRow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow = AccessDataSet.Tables(0).Rows.Find(keyArray)
[/SIZE]

At the .PrimaryKey line I get this error, "An unhandled exception of type 'System.ArgumentException' occurred in system.data.dll.

Additional information: These columns don't currently have unique values."

What does this error mean? Why do the columns have to have unique values to be set as primary keys? Also, is there anyway to work around this so that I can still set a primary key in my datatable to perform the .Find method? Any suggestions and/or advice is greatly appreciated.

 

[fpineda101]

"Why do the columns have to have unique values to be set as primary keys? Also, is there anyway to work around this so that I can still set a primary key in my datatable to perform the .Find method?"

Primary keys are unique identifiers. There is no way around that. That is one of the first things you learn in SQL

I am assuming you have a full version of Access 2000 or greater. You can open the .mdb file with Access, click on Tables, click on your table in Design View. Right click the desired column for the primary key. The pop-up menu will have a command to set as primary key.

 

[mechwarrior3]

Resolved!

So I have to modify the actual database and set the columns that I want to use as primary keys in the datatable to be primary keys in the database?

EDIT: What I actually did was took care of the problem by no longer needing to find the datarow I was working with. By taking care of that issue with a properly configured SELECT statement, I did not need to search the datatable for the correct row anymore because there was only one row in the datatable. Thanks for the help.

 

[fpineda101]

Glad you got it working. The answer to your question is yes. You would change the table in the database, not the database. Going into the table in Design View and setting a column to be the primary key was the best way to go.

 

[mechwarrior3]

Ah, okay. Well, that would have been tricky so I'm glad there was another way to fix the problem. Thanks again for your help, fp.

 

[fpineda101]

Its not that tricky. I attaching a zip file to this posting, hopefully it works. Its a Word document that has some screen shots on how to do it. Very simple! Take a look...

 

[mechwarrior3]

Hmmm. The link is working for me. I tried to save it and all I got was "clear.gif". I also tried to open it in Internet Explorer and just got a blank page. I am interested in seeing this process though.