Preserving Primary Key on da.fill

[x0td0x]

i am having problems with ado.net and specifically oledb

my main problem is that when i open up my database

::::::::::::::::
odda = New OleDbDataAdapter("SELECT * FROM " & TableName, getCnStr)

ds = New DataSet
odda.Fill(ds, TableName)
::::::::::::::::
if i call ds.tables("employees").primarykey(0)
is null,
, i find out that the primary key is not set
furthermore
ds.tables("employees").columns("employeeid").unique = false!!!!

thus

i cannot add a record to this database

if i manually tell it that the column with "id" in it is the primary
i get later on on trying to add the data
the error "column "employeeid" does not allow nulls"

but if i just dont assign a primary key
it is evident the primary key is not being preserved

the row-editing/adding dialog ends up containing the primarykey
field too, and it can be edited, returned and saved to the database, which threatens the integrity of the database

please help me out here
(i am not generating schemas to use with this data because i need this application to be able to open ANY mdb, )


Private Function AssignData(ByVal TableName As String)
odda = New OleDbDataAdapter("SELECT * FROM " & TableName, getCnStr)

ds = New DataSet
odda.Fill(ds, TableName)

cb = New OleDbCommandBuilder(odda)
odda.InsertCommand = cb.GetInsertCommand
odda.UpdateCommand = cb.GetUpdateCommand
odda.DeleteCommand = cb.GetDeleteCommand

Try

dgData.SetDataBinding(ds.Tables(0), "")
Catch ex As Exception
MsgBox(ex.Message)
End Try

End Function

my add record function is right here

(the frmEditDS has a datarow field that is set in the constructor

the dialog recieves an empty datarow

it fills each item with the required data

then the user clicks okay and the calling procedure retrieves the
data, which is the same datarow passed from .newrow but with new values))

Private Sub miRowAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles miRowAdd.Click

EditDS = New frmEditDS(ds.Tables(0).NewRow, frmEditDS.EditState.Add)
EditDS.ShowDialog()
Dim i As Integer
Try
ds.Tables(0).Rows.Add(EditDS.DataRow)
Catch ex As Exception
MsgBox("Exception occured on adding dialog data to dataset (" & ex.Message & ") from " & ex.Source)
End Try

'ds.Tables(0).ImportRow(dr)
End Sub

[Carnifex]

The data adapter does not return what the primary key is when using the fill method. You need to code for this yourself.

I use SQL so I have no idea what the equivilant is in an OLEDB. (Access I presume)

In SQL I find the primary keys by using

sp_pkeys <tablename>

Which returns a set that I can read using a DataReader object.

Once you have the rows (or if you wish to statically code them yourself then what you need to do is something like

Dim PrimaryKey(0) As DataColumn
 
PrimaryKey(0) = MyTable.Columns("Column")
 
MyTable.PrimaryKey = PrimaryKey

The reason I set it to an array is because Primary Key is a property of an array of datacolumns. (In the case you have a join Primary Key) By setting the primary key this in turn makes AllowNull false.

Hope this helps. MSDN should have more information on the primary key property for DataTables. From there you just need to find a command that will allow you to find the primary key for a table in an Access database.

[x0td0x]

hurrah!! after days!!

quoting msdn

The Fill operation then adds the rows to destination DataTable objects in the DataSet, creating the DataTable objects if they do not already exist. When creating DataTable objects, the Fill operation normally creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created.

odda = New OleDbDataAdapter("SELECT * FROM " & TableName, getCnStr)
 
odda.MissingSchemaAction = MissingSchemaAction.AddWithKey
ds = New DataSet
odda.Fill(ds, TableName)