stuckedagain stuckedagain - 20 days ago 7
SQL Question

MSSQL stored procedure - C# PK - FK

quite new to SQL and just discovered the wonderful world of stored procedures - and it already gives me a headache. Came here for help.

Scenario 1.

Given a table, I write a store procedure and call it in C# to populate the table.
Everything works as expected.

Country SQL table looks like this

Stored procedure:

CREATE PROCEDURE [dbo].[InsertRecord2]

@countryname nvarchar(64),

AS

INSERT INTO Country(

CountryName
)

VALUES (

@countryname

)

RETURN


Calling in VS:

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.

I want to create an SQL view now, consists of the previous Country table and another table, let's call it City. CountryID, which is the PK for Country table, is the FK in City table.

SQL view looks like this

Stored procedure:

CREATE PROCEDURE [dbo].[InsertRecord2]

@countryname nvarchar(64),
@cityname nvarchar(64)

AS

INSERT INTO Country(

CountryName
)

VALUES (

@countryname

)

INSERT INTO City(

CityName
)

VALUES (

@cityname

)

RETURN


Calling in VS:

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 the problem. Clicking on the button, the following I see on execution:

Additional information: Cannot insert the value NULL into column 'CountryID', table 'isg_cid.dbo.City'; column does not allow nulls. INSERT fails.

Okay, that's pretty obvious - a PK cannot be NULL. But, when I tried to insert into Country table, I didn't have to specify the ID (auto increment, auto seed switched ON), so


  1. why do I have to specify it this time? and

  2. how could I do that?



I suppose it should be done in the stored procedure somehow and I bet this is quite simple to solve - for someone with great experience with SSMS. For me, it's a hassle to figure out what to do.

Thanks for your help!

Answer

It is not the CountryID field from the Country table but the CountryID field from the City table that triggers the error message.
This is the Foreign Key that links a City with its Country and logically cannot be let without a value when you insert a new City.

So, a possible approach is to read the last IDENTITY value set for the Country table using SELECT SCOPE_IDENTITY() and use this value to set the CountryID in the City table.

You need to change the second SP with

CREATE PROCEDURE [dbo].[InsertRecord2]
@countryname nvarchar(64),
@cityname nvarchar(64)

AS

    INSERT INTO Country(CountryName) VALUES (@countryname)
    INSERT INTO City(CountryID, CityName)
    VALUES (SELECT SCOPE_IDENTITY(), @cityname)