CODEWITHSUNDEEP
c#sqlsql-servert-sqlstored-procedures
stuckedagain
stuckedagain

Reputation: 13

SQL Server stored procedure - C# PK - FK

Quite new to SQL Server 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 wrote a stored 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 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: I want to create a SQL view now, consists of the previous Country table and another table, let's call it City. CountryID, which is the PK for the Country table, is a FK in the 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 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 the problem. Clicking on the button, I see an exception:

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!

Upvotes: 1

Views: 229

Answers (1)

Steve
Steve

Reputation: 216293

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 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 (SCOPE_IDENTITY(), @cityname)

Upvotes: 1

Related Questions