Question How to set DataSet Primary Key programmatically


New member
Aug 10, 2011
Programming Experience
I need some help here.
I have a form that populates two tables (countries and states). Country Primary key is Identity. State PK is Country PK + state PK (not identity and entered by the user)
I want one form to allow the user to add or modify countries and states.
My form has a textbox, a datagridview and a save button.
On Load I detect if is a new country or an update from an existing one. The textbox is unbound and I handle inserts and updates programmatically (pretty simple)
Updates works fine since my Query is "Select * from states where CountryID = " & _Country_ID
... so I am able to populate the datagridview with the states and allow to add, edit or delete states. (I hide the countryID column since is Identity and PK)
My problem is with new Countries since the country is inserted right before the states on the same "save button" click. I retrieve the country_ID (Identity) programmatically but I am unable to set it to the States that the user addes on the Datagridview and get a Cannot insert the value NULL into column CountryID.

How can I bound the datagridview to a yet non existint record? Should I use something like "Select * from states where CountryID = -1" on form load just to create the empty grid? if so how can set the countryID to the datagridview or the dataset right after retrieving the SCOPE_IDENTITY() value?
So to put it clear... the user types a Country name on the textbox, and starts populating the Datagridview with the states. Then clicks save and first I insert the country, retrieve the Identity value and try to insert the states using that value and there is where I get stucked.
Any help will be appreciated!
Top Bottom