locked
Select scope identity RRS feed

  • Question

  • User972233924 posted

    Hi everyone, 

    So as part of my final year project in college, we've to create a web application and I've chosen to use asp.net web forms for it, a language I've had little to do with prior to this year. Basically, I have fields which add data to the SQL Server database (working perfectly), now I want to use the auto-generated primary key that has just been added and add it as a foreign key in another table when the buttons clicked. From reading forums the Scope Identity function should do this but after trial and error I can't figure out where exactly to put it in my code.

    Also depending on what number is entered in a field (NoOfPitches) I want to add that many records to the other table, I assume I can use an If or case statement for this?

    protected void RegisterClub_Click(object sender, EventArgs e)
        {
            if (ClubName.Text != "" & ClubAddress.Text != "" & ClubTown.Text != "" & ClubCounty.Text != "" & ClubEmail.Text != "" & ClubPhone.Text != "" & ClubType.Text != "" & NoOfPitches.Text != "")
            {
                String CS = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
                using (SqlConnection conn = new SqlConnection(CS))
                {
                    SqlCommand cmd = new SqlCommand("insert into Clubs values('" + ClubName.Text + "', '" + ClubAddress.Text + "', '" + ClubTown.Text + "','" + ClubCounty.Text + "', '" + ClubEmail.Text + "' , '" + ClubPhone.Text + "', '" + ClubType.Text + "', '" + NoOfPitches.Text + "')", conn);
                    
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    //Response.Redirect("AdminClubs.aspx");
                    conn.Close();
                }
            }
            else
            {
               
                lblClubMessage.Text = "All Fields Are Mandatory.";
            }
    
    
            String ConStr = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(ConStr))
            {
                
                SqlCommand cmd = new SqlCommand("Insert into Pitch (ClubName, NoOfPitches) values ('"+ ClubName.Text + "','" + NoOfPitches.Text + "')", conn); 
                conn.Open();
                cmd.ExecuteNonQuery();
                Response.Redirect("AdminClubs.aspx");
            }
    
    
    
        }

    Any help on this would be hugely appreciated.

    Thanks,

    Mark

    Saturday, February 3, 2018 12:36 PM

Answers

  • User2103319870 posted

    Markk25

    From reading forums the Scope Identity function should do this but after trial and error I can't figure out where exactly to put it in my code.

    You can use ExecuteScalar to read the value from ScopeIdentity. Also use parameterized queries to safequard your code from SQLInjection. For more details you can check this link : How To: Protect From SQL Injection in ASP.NET

    protected void RegisterClub_Click(object sender, EventArgs e)
    		{
    			int modified = 0;
    			if (ClubName.Text != "" & ClubAddress.Text != "" & ClubTown.Text != "" & ClubCounty.Text != "" & ClubEmail.Text != "" & ClubPhone.Text != "" & ClubType.Text != "" & NoOfPitches.Text != "")
    			{
    				String CS = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
    				using (SqlConnection conn = new SqlConnection(CS))
    				{
    					using (SqlCommand cmd = new SqlCommand("insert into Clubs(ClubName,ClubAddress,ClubTown,ClubCount,ClubEmail,ClubPhone,ClubType,NoOfPitches) " +
    						"values(@ClubName,@ClubAddress,@ClubTown,@ClubCounty,@ClubEmail,@ClubPhone,@ClubType,@NoOfPitches);SELECT CAST(scope_identity() AS int);", conn))
    					{
    						//Add paramater with value
    						cmd.Parameters.AddWithValue("@ClubName", ClubName.Text);
    						cmd.Parameters.AddWithValue("@ClubAddress", ClubAddress.Text);
    						cmd.Parameters.AddWithValue("@ClubTown", ClubTown.Text);
    						cmd.Parameters.AddWithValue("@ClubCounty", ClubCounty.Text);
    						cmd.Parameters.AddWithValue("@ClubEmail", ClubEmail.Text);
    						cmd.Parameters.AddWithValue("@ClubPhone", ClubPhone.Text);
    						cmd.Parameters.AddWithValue("@ClubType", ClubType.Text);
    						cmd.Parameters.AddWithValue("@NoOfPitches", NoOfPitches.Text);
    						conn.Open();
    						//Use Execute Scalar to get the inserted value from scope identity
    						modified = (Int32)cmd.ExecuteScalar();
    						//Response.Redirect("AdminClubs.aspx");
    						conn.Close();
    					}
    				}
    			}
    			else
    			{
    
    				lblClubMessage.Text = "All Fields Are Mandatory.";
    			}
    			String ConStr = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
    			using (SqlConnection conn = new SqlConnection(ConStr))
    			{
    
    				using (SqlCommand cmd = new SqlCommand("Insert into Pitch (ClubName, NoOfPitches) values (@ClubName,@NoOfPitches)"))
    				{
    					//Add paramater with value
    					cmd.Parameters.AddWithValue("@ClubName", ClubName.Text);
    					cmd.Parameters.AddWithValue("@NoOfPitches", NoOfPitches.Text);
    					conn.Open();
    					cmd.ExecuteNonQuery();
    					Response.Redirect("AdminClubs.aspx");
    				}
    			}
    		}

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 3, 2018 3:19 PM

All replies

  • User2103319870 posted

    Markk25

    From reading forums the Scope Identity function should do this but after trial and error I can't figure out where exactly to put it in my code.

    You can use ExecuteScalar to read the value from ScopeIdentity. Also use parameterized queries to safequard your code from SQLInjection. For more details you can check this link : How To: Protect From SQL Injection in ASP.NET

    protected void RegisterClub_Click(object sender, EventArgs e)
    		{
    			int modified = 0;
    			if (ClubName.Text != "" & ClubAddress.Text != "" & ClubTown.Text != "" & ClubCounty.Text != "" & ClubEmail.Text != "" & ClubPhone.Text != "" & ClubType.Text != "" & NoOfPitches.Text != "")
    			{
    				String CS = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
    				using (SqlConnection conn = new SqlConnection(CS))
    				{
    					using (SqlCommand cmd = new SqlCommand("insert into Clubs(ClubName,ClubAddress,ClubTown,ClubCount,ClubEmail,ClubPhone,ClubType,NoOfPitches) " +
    						"values(@ClubName,@ClubAddress,@ClubTown,@ClubCounty,@ClubEmail,@ClubPhone,@ClubType,@NoOfPitches);SELECT CAST(scope_identity() AS int);", conn))
    					{
    						//Add paramater with value
    						cmd.Parameters.AddWithValue("@ClubName", ClubName.Text);
    						cmd.Parameters.AddWithValue("@ClubAddress", ClubAddress.Text);
    						cmd.Parameters.AddWithValue("@ClubTown", ClubTown.Text);
    						cmd.Parameters.AddWithValue("@ClubCounty", ClubCounty.Text);
    						cmd.Parameters.AddWithValue("@ClubEmail", ClubEmail.Text);
    						cmd.Parameters.AddWithValue("@ClubPhone", ClubPhone.Text);
    						cmd.Parameters.AddWithValue("@ClubType", ClubType.Text);
    						cmd.Parameters.AddWithValue("@NoOfPitches", NoOfPitches.Text);
    						conn.Open();
    						//Use Execute Scalar to get the inserted value from scope identity
    						modified = (Int32)cmd.ExecuteScalar();
    						//Response.Redirect("AdminClubs.aspx");
    						conn.Close();
    					}
    				}
    			}
    			else
    			{
    
    				lblClubMessage.Text = "All Fields Are Mandatory.";
    			}
    			String ConStr = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
    			using (SqlConnection conn = new SqlConnection(ConStr))
    			{
    
    				using (SqlCommand cmd = new SqlCommand("Insert into Pitch (ClubName, NoOfPitches) values (@ClubName,@NoOfPitches)"))
    				{
    					//Add paramater with value
    					cmd.Parameters.AddWithValue("@ClubName", ClubName.Text);
    					cmd.Parameters.AddWithValue("@NoOfPitches", NoOfPitches.Text);
    					conn.Open();
    					cmd.ExecuteNonQuery();
    					Response.Redirect("AdminClubs.aspx");
    				}
    			}
    		}

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 3, 2018 3:19 PM
  • User972233924 posted

    Thanks for the help, I hadn't thought of protecting against SQL Injections.

    I see how it works now but I think what I need is still slightly different, 

    I want to retrieve the auto-generated ClubID from the first set of SQL statements when inserting a new record,  and add it as a foreign key in the second set of SQL statements in the pitch table. To do this do I place the ExecuteScalar() beneath the parameter definitions (for the pitch table) as if it was one?

    Since I'm creating a new record here will this just retrieve the auto-generated PitchID and not the ClubID that I'm looking for?

    Thanks very much for your help,

    Mark

    Saturday, February 3, 2018 9:57 PM
  • User1841825477 posted

    You say that you want to use code to add a foreign key.
    But as I understand things, PK-FK relationships are usually defined in the schema of a database during the schema design process.  

    And CRUD transactions are most effective when they are implemented via parameterized stored procedures that you then call from your .aspx pages.

    Not only is this more efficient, it also protects against SQL injection attacks.

    Just some thoughts.
     Ken

      

    Saturday, February 3, 2018 10:31 PM
  • User972233924 posted

    Eventually got it working properly with some reworking,

    Thanks so much for the help A2H,

    regards,

    Mark

    Sunday, February 4, 2018 2:38 PM
  • User972233924 posted

    For anyone wondering here's my finished code, loop used at the end to add the data to the other table depending on the dropdown value

    protected void RegisterClub_Click(object sender, EventArgs e)
        {
            int var = 0;
    
            if (ClubName.Text != "" & ClubAddress.Text != "" & ClubTown.Text != "" & ClubCounty.SelectedValue != "" & ClubEmail.Text != "" & ClubPhone.Text != "" & ClubType.Text != "" & NoOfPitches.SelectedValue != "")
            {
                String CS = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
                using (SqlConnection conn = new SqlConnection(CS))
                {
                    using (SqlCommand cmd = new SqlCommand("insert into Clubs(ClubName, ClubAddress, ClubTown, ClubCounty, ClubEmail, ClubPhone, ClubType, NoOfPitches) " +
                            "Values(@ClubName, @ClubAddress, @ClubTown, @ClubCounty, @ClubEmail, @ClubPhone, @ClubType, @NoOfPitches);SELECT CAST(scope_identity() AS int);", conn))
                    {
                        //Add paramater with value
                        cmd.Parameters.AddWithValue("@ClubName", ClubName.Text);
                        cmd.Parameters.AddWithValue("@ClubAddress", ClubAddress.Text);
                        cmd.Parameters.AddWithValue("@ClubTown", ClubTown.Text);
                        cmd.Parameters.AddWithValue("@ClubCounty", ClubCounty.SelectedValue);
                        cmd.Parameters.AddWithValue("@ClubEmail", ClubEmail.Text);
                        cmd.Parameters.AddWithValue("@ClubPhone", ClubPhone.Text);
                        cmd.Parameters.AddWithValue("@ClubType", ClubType.Text);
                        cmd.Parameters.AddWithValue("@NoOfPitches", NoOfPitches.SelectedValue);
                        conn.Open();
    
                        var = (Int32)cmd.ExecuteScalar();
                        //Response.Redirect("AdminClubs.aspx");
                        conn.Close();
                    }
    
                    String ConStr = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(ConStr))
                    {
                        using (SqlCommand cmd = new SqlCommand("Insert into Pitch (ClubID, ClubName, NoOfPitches, PitchName) values (@ClubID, @ClubName, @NoOfPitches, @PitchName)", con))
                        {
                            int Pitches = Convert.ToInt32(NoOfPitches.SelectedValue);
                            conn.Open();
                            for (int i = 1; i <= Pitches; i++)
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@ClubID", var);
                                cmd.Parameters.AddWithValue("@ClubName", ClubName.Text);
                                cmd.Parameters.AddWithValue("@NoOfPitches", i);
                                cmd.Parameters.AddWithValue("@PitchName", "Pitch " + i.ToString());
                                cmd.ExecuteNonQuery();
                            }
                            conn.Close();
                            Response.Redirect("AdminClubs.aspx");
                        }
                    }
                }
            }
            else
            {
               
                lblClubMessage.Text = "All Fields Are Mandatory.";
            }
    
            
    
           
        }

    Monday, February 5, 2018 2:09 PM