question

ArtHansen-4605 avatar image
0 Votes"
ArtHansen-4605 asked ArtHansen-4605 answered

MS SQL Stored Procedure working in SSMS but not workng when called from C#

Hello

I’m following a tutorial which creates a winForm based app using C# and accessing SQL DBs and text files. Whilst I’m uncertain whether my problem is rooted on the SQL or C# side of the house I’m very strongly leaning towards C# despite the abort error message being an SQL exception provided by the Visual Studio debugger:
"System.Data.SqlClient.SqlException
Message=The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TeamPlayers_Players". The conflict occurred in database "TourneyManagerSSMS", table "dbo.Players", column 'id_player'.
The statement has been terminated.
Source=.Net SqlClient Data Provider"

Table TeamPlayers is a junction table referencing /linked to the Team and Player parent tables via foreign keys.. According to what I could find searching the internet the above error occurs if either of the parent tables are empty or if the foreign key doesn’t exist in the linked parent table. Both parent tables have validated test data and the primary key of both is setup with IDENTITY (1,1). I can write to and read from the DB tables via winForms and the specific query being called in C# can be successfully executed in SSMS, populating the junction table with data.

The app aborts when attempting to INSERT to the junction table. Checking the value contained in “model.id” shows expected data. Checking the value contained in “addTM2db.id”, however, reveals a value of 0 – which of course does not exist in the Player parent table. Hovering the cursor over “model.TeamMembers” – which is where I think “addTM2db.id” is getting its content from – shows a numerical value consistent with the number of Players selected on the winForm dropdown to be attached to a given team.

I fail to understand why the values in “model.TeamMembers” are not being picked up by “addTM2db.id”.

Following is (I think) the relevant code:

     public class Team
     {
         public int id { get; set; }
         public string TeamName { get; set; }
         public List<Player> TeamMembers { get; set; } = new List<Player>();
     }
     public class Player
     {
         public int id { get; set; }
         public string FirstName { get; set; }
         public string LastName { get; set; }
         public string EMail { get; set; }
         public string CellPhone { get; set; }
         public string getName { get { return $"{LastName}, {FirstName}"; } }
     }
     public partial class Create_Team : Form
     {
         // set-up for player combo dropdown and team Members listBox
         private List<Player> availablePlayers = ConnectionConfig.Connects.GetPlayers_All();
         private List<Player> selectedPlayers = new List<Player>();
            
         private void nuTeamButton_Click(object sender, EventArgs e)
         {
             if (ValidateTeamInput())
             {
                 MessageBox.Show("A Team Name must be entered.");
             }
             else
             {
                 Team model = new Team();
                 {
                     model.TeamName = nuTeamNameTextBox.Text;
                     model.TeamMembers = selectedPlayers;
                 }
                 model = ConnectionConfig.Connects.nuTeam(model);
                 teamAdded();
                 // TODO MAYBE Re-Initialize Form
                 nuTeamNameTextBox.Text = "";
             }
         }
     }
    
     public class DatabaseConnect : IdataSourceConnect
     {
         private const string DB = "Tourneys_MS_SQL";
         public Team nuTeam(Team model)
         {
             using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionConfig.connectString(DB)))
             {
                 var add2db = new DynamicParameters();
                 add2db.Add("@TeamName", model.TeamName);
                 add2db.Add("@id_team", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
    
                 connection.Execute("dbo.spTeams_ADD", add2db, commandType: CommandType.StoredProcedure);
                 model.id = add2db.Get<int>("@id_team");
    
                 foreach (Player addTM2db in model.TeamMembers) // values as expected
                 {
                     add2db = new DynamicParameters();
                     add2db.Add("@id_team", model.id); // values as expected
                     add2db.Add("@id_player", addTM2db.id);  ///  ====> ZERO !!!!!!!!!!!!!!!
                     // add2TMdb.Add("@id_teamplayers", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
                     /*====> ABORT LINE:*/ connection.Execute("dbo.spTeamPlayers_ADD", add2db, commandType: CommandType.StoredProcedure);
                     // model.id = add2TMdb.Get<int>("@id_teamplayers");
                 }
                 return model;
             }
         }
     }

I’ve tried with and without the lines commented out (the tutorial author does it without) with the same SQL exception result. New teams are added to DB (line 54); run aborts on line 63.

Thank you in advance for your time & expertise.
Art


sql-server-generaldotnet-csharpwindows-forms
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @ArtHansen-4605
Is the tutorial text or video based and what is the web address?

0 Votes 0 ·

Hello @karenpayneoregon (where at Orygun?)

Well, not the response I was hoping for but if you're looking for thorough start to finish I found this very helpful. The vid itself is 24 HOURS long & I've been working through it for about 2 weeks now & am only about 1/2 thru. Of course I'm adding my own buzzers & bells to makes sure I'm actually understanding the mtrl. For the most part the author avoids too much unnecessary prattle.

https://www.youtube.com/watch?v=wfWxdh-_k_4. In case the link fails search on Tim Corey.

0 Votes 0 ·

The error you getting (as you have already suggested) is because addTM2db.id (player Id) is 0. What you need to make sure is for each Player in model.TeamMembers, it's Id is set to a valid value (Id exits in Players table).

Unfortunately with the provided code, I can't see how it's getting set. Can you explain how actual model.TeamMembers binding is set, please.

0 Votes 0 ·
ArtHansen-4605 avatar image
0 Votes"
ArtHansen-4605 answered

Hello @JaliyaUdagedara and @TimonYang-MSFT

Problem resolved...

Root cause was inconsistent naming of the primary key "id_player" field in the SQL Player table and the associated C# Player class "id" property name.

After some ferreting around I found YamlDotNet which enabled me to see the relevant object content which then made it possible to determine the initial data acquired from the DB (availalePlayers) was also missing the ID info. Finding that out led to discovery of the naming inconsistency. Which meant the "id" in C# had never been populated in the first place.

Thanx again for your help and have a great start to the Euro-based New Year.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TimonYang-MSFT avatar image
0 Votes"
TimonYang-MSFT answered TimonYang-MSFT published

From the current code, your code to assign a value to "model.TeamMembers" is the following line:

 model.TeamMembers = selectedPlayers;

In addition, there are no other related codes in this code.
So, when you enter foreach, model.TeamMembers should still be an empty list.
But it doesn't seem to be the case at present. You successfully entered the foreach, indicating that it has at least one object.
Please show the part of the code that adds the object to it, or add watch to it when debugging, so that we can know its current value.
In addition, there is a small problem. The following two lines seem unnecessary to be placed in foreach:

  add2db = new DynamicParameters();
  add2db.Add("@id_team", model.id); // values as expected

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ArtHansen-4605 avatar image
0 Votes"
ArtHansen-4605 answered ArtHansen-4605 edited

Hello @JaliyaUdagedara and @TimonYang-MSFT
Thank you for your substantive responses confirming I’m at least focusing on the correct place to find the abort cause. Here is the additional you both requested:

         private void addTeamMemberButton_Click(object sender, EventArgs e)
         {
             Player xferPlayer = (Player)selectTeamMemberComboBox.SelectedItem;
             if (xferPlayer != null)
             {
                 availablePlayers.Remove(xferPlayer);
                 selectedPlayers.Add(xferPlayer);  // populating selectedPlayers list(Box)
                 wireUpLists();
             }
         }

I have re-worked the relevant code flow presentation in case that is helpful:

  // ===> extract current list of player from SQL table and load into C# list object
         public List<Player> GetPlayers_All()
         {
             List<Player> output;
             using (IDbConnection connection = 
  new System.Data.SqlClient.SqlConnection(ConnectionConfig.connectString(DB)))
             {
                 output = connection.Query<Player>("dbo.spPlayers_GetAll").ToList();
             }
             return output;
         }
         // ===> set-up for combobo dropdown and Members listBox
         private List<Player> availablePlayers = ConnectionConfig.Connects.GetPlayers_All();
         private List<Player> selectedPlayers = new List<Player>();
  // ===> populating winForm controls
         private void wireUpLists()
         {
             selectTeamMemberComboBox.DataSource = null;
             selectTeamMemberComboBox.DataSource = availablePlayers;
             selectTeamMemberComboBox.DisplayMember = "getName";
                
             MembersListBox.DataSource = null;
             MembersListBox.DataSource = selectedPlayers;
             MembersListBox.DisplayMember = "getName";
         }
  // ===> select specific player from dropdown and move from available to selected
         private void addTeamMemberButton_Click(object sender, EventArgs e)
         {
             Player xferPlayer = (Player)selectTeamMemberComboBox.SelectedItem;
             if (xferPlayer != null)
             {
                 availablePlayers.Remove(xferPlayer);
                 selectedPlayers.Add(xferPlayer);  // populating selectedPlayers list(Box)
                 wireUpLists();
             }
         }
  // ===> create new Team object containing data used to update SQL table
  private void nuTeamButton_Click(object sender, EventArgs e)
         {
  Team model = new Team();
  {
  model.TeamName = nuTeamNameTextBox.Text;
  model.TeamMembers = selectedPlayers;
  }
  model = ConnectionConfig.Connects.nuTeam(model);
  }
  // ===> add new info to SQL table
         public Team nuTeam(Team model)
         {
             using (IDbConnection connection = 
  new System.Data.SqlClient.SqlConnection(ConnectionConfig.connectString(DB)))
             {
                 var add2db = new DynamicParameters();
                 add2db.Add("@TeamName", model.TeamName);
                 add2db.Add("@id_team", 0, dbType: DbType.Int32, 
                     direction: ParameterDirection.Output);
    
                 connection.Execute("dbo.spTeams_ADD", add2db, 
  commandType: CommandType.StoredProcedure);
                                    
                 model.id = add2db.Get<int>("@id_team");
    
                 var add2junctionTable = new DynamicParameters();
                 foreach (Player add2TeamMemberdb in model.TeamMembers)
                 {
                     add2junctionTable = new DynamicParameters();
                     add2junctionTable.Add("@id_team", model.id);
                     add2junctionTable.Add("@id_player", add2TeamMemberdb.id);  ///  ====> ZERO !!
                     add2junctionTable.Add("@id_teamplayers", 0, dbType: DbType.Int32, 
                         direction: ParameterDirection.Output);
    
                     connection.Execute("dbo.spTeamPlayers_ADD", add2junctionTable, 
  commandType: CommandType.StoredProcedure);
    
                     model.id = add2junctionTable.Get<int>("@id_teamplayers");
                 }
                 return model;
             }
         }

Thanks again for your help and let me know if there’s anything else I can provide to facilitate identification of a resolution.
Art


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.