DataSet, DataAdapter, no dataTable Primary key

Question

Ok so I'm having this:

Dim sql As String = "SELECT * FROM Articles"
dAdapter = New OleDbDataAdapter(sql, connection)
dSet = New DataSet("tempDatatable")

With connection
    .Open()
    dAdapter.Fill(dSet, "Articles_table")
    .Close()
End With

With DataGridView1
    .DataSource = dSet
    .DataMember = "Articles_table"
End With

And I wonder if there is any possible way to define the first column as the primary key. I've looked around but everyone is using a manual datatable to fill up the datagrid. Since I'm using a dataBase I don't know how to set a primary key from there. I need some help.

Answer 1

You have to set the DataAdapter's MissingSchemaAction to AddWithKey:

var table = new DataTable();
using(var con = new SqlConnection(connectionString))
using (var da = new SqlDataAdapter("SELECT * FROM Articles", con))
{
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey
    da.Fill(table);
}

Edit: VB.NET:

Dim table = New DataTable()
Using con = New SqlConnection(connectionString)
    Using da = New SqlDataAdapter("SELECT * FROM Articles", con)
        da.MissingSchemaAction = MissingSchemaAction.AddWithKey
        da.Fill(table)
    End Using
End Using

Now the necessary columns and primary key information to complete the schema are automaticaly added to the DataTable.

Read: Populating a DataSet from a DataAdapter

Answer 2

You can add a primary key to your data table using something like this:

var table = dSet.Tables["Articles_table"];
table.PrimaryKey = new DataColumn[] { table.Columns[0] };

Sorry, just realised the question was tagged with vb.net, not c#. VB.net would be:

Dim table = dSet.Tables("Articles_table")
table.PrimaryKey = New DataColumn() {table.Columns(0)}