ADO.NET - problem assigning primary key column

[groston]

This problem is killing me!

What I have doen si greatly simplified the problem and cast it into MS Access (instead of MS SQL from whence it sources) to allow easy distibution.

A zip file with the database and all vb.net files can be found at

http://www.pairofdocs.net/TestTable.zip.

The file is a measily 13.4 kB.

Here is the code that causes the problem:

Private Sub cmdTest
Dim conTestTable As New OleDbConnection(conString)
Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select * from BADGECOPY", conTestTable)
Dim dt As New DataTable
da.Fill(dt)

' Set up the ID column as the primary key
Dim pk(0) As DataColumn
pk(0) = dt.Columns("ID&quot
dt.PrimaryKey = pk <<=== here is the problem
End Sub

When the indicated line is executed, I get the following error:

An unhandled exception of type 'System.ArgumentException' occurred in system.data.dll
Additional information: These columns don't currently have unique values.

The column ID is an array of bytes. The sample database has exactly two rows and the ID's are unique.

I could really use help on this one! Thanks!



----

Gerry Roston
gerry@pairofdocs.net

 

[DotNetDoc]

When you load the Byte Array into the dataset it loads the object type. The datarow cannot interpet complex datatypes. Therfore when it checks to see if they are unique it will see that the string &quot;Byte()Array&quot; is in both columns and it is not unique. It does not know how to convert it like you function does. You can of course set the Primary key to the column that you added IDString.

Hope that helps

DotNetDoc
M.C.S.D.

http://www.DotNetDoc.com

---------------------------------------

Tell me and I forget. Show me and I remember. Involve me and I understand.
- Anonymous Chinese Proverb

-----------------------------------

If you can't explain it simply, you don't understand it well enough.
- A. Einstein

 

[groston]

DotNetDoc,

Your provided what seems to be the correct answer, but it most certainly doesn't help - I think it tells me I'm screwed...

Here's the full story: I am writing an app to interface with a DB that is built and used by another application. Within my app, I need to read in a table, add some rows, then write the changed table back to the DB. Within the DB, the ID column is the primary key for said table.

Using VB.NET, I can create a SQLDataAdapter to get the rows fo the table and store this into a DataSet. To be able to update the underlying DB, I need to define a primary key for the table within the dataset. (This problem arose when my call to FillSchema kept failing - now, thanks to your answer, I understand why.) Since the string I created is not part of the table in the DB, I do not undertsand how this could be used as a primary key for the table within the DataSet.

So, I am now at a complete loss as to how to make this work. Could you please offer a suggestion? Better yet, would it be possible for me to speak with you for 5 minutes to figure out a solution - this problem is a major stumbling block and I am losing time.

Thanks!


----

Gerry Roston
gerry@pairofdocs.net

 

[DotNetDoc]

Are you on msn messenger. If so my sign on is eganenterprises@msn.com

If not msn is free and can be downloaded at

http://messenger.msn.com/

It will allow us to chat realtime.

If not we can work through here. I will be back online at 10:15 (PST) let me know what you would like to do



DotNetDoc
M.C.S.D.

http://www.DotNetDoc.com

---------------------------------------

Tell me and I forget. Show me and I remember. Involve me and I understand.
- Anonymous Chinese Proverb

-----------------------------------

If you can't explain it simply, you don't understand it well enough.
- A. Einstein

 

[RSX02]

I got the same problem
Did you find a solution? If so, I would appreciate to know how...
Thanks in advance

 

[groston]

The problem is that when a byte array is returned, its value is seen as <byte array., thus they all look the same! I do not remember my solution - I either created another column and used an integer as the key or I converted the byte array to a string and used it. Either way, the answer is not ideal.

----

Gerry Roston
gerry@pairofdocs.net

 

[RSX02]

In my case, my primary key is integer. I put it auto increment..(identity)
And I have the probleme that when I want to create a new record and that I put nothing in the &quot;Seq&quot; (which is my primary key) as I thing that my database will increment it by itself it gives me the error that I can't not let the field &quot;Seq&quot; to NULL....
Do you have any idea how can I solve it?

 

[groston]

Can you please post some code. I have code that does what you are trying to do and it works reliably.

----

Gerry Roston
gerry@pairofdocs.net

 

[RSX02]

When the user click on the save button

Dim dr As DataRow = m_DataLayer.DsOwner.Owners.NewRow
'dr.Item(&quot;Seq&quot = 4
dr.Item(&quot;First_Name&quot = Me.First_Name.Text
dr.Item(&quot;Last_Name&quot = Me.Last_Name.Text

m_DataLayer.DsOwner.Owners.Rows.Add(dr)

I put the line dr.Item(&quot;Seq&quot = 4 just to test if it was working with the &quot;Seq&quot; and it does. But if I take off this line I get the error as I told you before.

My field &quot;Seq&quot; is declared as Primary key and it's a integer
I put its Identity Property to yes. Identity seed = 1 and Identity Increment = 1

 

[groston]

Where is Seq defined as the primary key? In the database? If so, did you first do a FillSchema or set the column to be the primarykey column?

----

Gerry Roston
gerry@pairofdocs.net

 

[RSX02]

Yes the primary key is set in the database.
I guess that when you are talking about the FillSchema or set the colum to be the primarykey column is in my vb.net application. I guess I did not do that as I don't know how to do it...

 

[groston]

try:

Dim ds As DataSet
Dim da As New SqlDataAdapter(&quot;Select * from foo&quot;, cn)
da.FillSchema(ds, SchemaType.Mapped, &quot;Table&quot
da.Fill(ds, &quot;Table&quot



----

Gerry Roston
gerry@pairofdocs.net

 

[RSX02]

I use datalayer and datarow?

 

[RSX02]

if the fillschema is used to fill my dataset and my sqldataadapter I already did that. I have data in my dataset as I have data in my datagrid...

 

[groston]

fillSchema is called before you call fill. What it does is get info like the primarykey. Use VS help and read up on it.

----

Gerry Roston
gerry@pairofdocs.net