SQL Server stored procedure - C# PK - FK -

quite new sql server , discovered wonderful world of stored procedures - , gives me headache. came here help.

scenario 1: given table, wrote stored procedure , call in c# populate table. works expected.

country sql table looks this

stored procedure:

create procedure [dbo].[insertrecord2]     @countryname nvarchar(64),     insert country(countryname)     values (@countryname)      return 

calling in c#

private void button1_click(object sender, eventargs e) {     readonly sqlconnection _connection = new sqlconnection(@"data source=rexgbasqlp042;initial catalog=isg_cid;integrated security=true");      _connection.open();      sqlcommand _command = _connection.createcommand();     _command.commandtype = commandtype.storedprocedure;     _command.commandtext = "insertrecord2";      _command.parameters.add("@countryname", sqldbtype.nvarchar).value = countryname.text;      _command.executenonquery();      _connection.close(); } 

scenario 2: want create sql view now, consists of previous country table , table, let's call city. countryid, pk country table, fk in city table.

sql view looks this

stored procedure:

create procedure [dbo].[insertrecord2]     @countryname nvarchar(64),     @cityname nvarchar(64)     insert country(countryname)     values (@countryname)      insert city(cityname)     values (@cityname)      return 

calling in c#:

private void button1_click(object sender, eventargs e) {     readonly sqlconnection _connection = new sqlconnection(@"data source=rexgbasqlp042;initial catalog=isg_cid;integrated security=true");      _connection.open();      sqlcommand _command = _connection.createcommand();     _command.commandtype = commandtype.storedprocedure;     _command.commandtext = "insertrecord2";      _command.parameters.add("@countryname", sqldbtype.nvarchar).value = countryname.text;     _command.parameters.add("@cityname", sqldbtype.nvarchar).value = cityname.text;      _command.executenonquery();      _connection.close(); } 

and here comes problem. clicking on button, see exception:

additional information: cannot insert value null column 'countryid', table 'isg_cid.dbo.city'; column not allow nulls. insert fails.

okay, that's pretty obvious - pk cannot null. but, when tried insert country table, didn't have specify id (auto increment, auto seed switched on), so

1. why have specify time? and
2. how that?

i suppose should done in stored procedure somehow , bet quite simple solve - great experience ssms. me, it's hassle figure out do.

thanks help!

it not countryid field country table countryid field city table triggers error message.
foreign key links city country , logically cannot let without value when insert new city.

so, possible approach read last identity value set country table using scope_identity() , use value set countryid in city table.

you need change second sp with

create procedure [dbo].[insertrecord2] @countryname nvarchar(64), @cityname nvarchar(64)       insert country(countryname) values (@countryname)     insert city(countryid, cityname)     values (scope_identity(), @cityname)