locked
Select Scope Identity RRS feed

  • Question

  • User-1671044487 posted

    Hi all, 

    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  MySQL database, now I want to use the auto-generated primary key(ProductID) 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. 

    I am currently following a YouTube tutorial series and have had no real problems with the code he has shown up until this point. Unfortunately the solutions I have found do not fix the problem that I have. Here is a link to the tutorial which I'm currently experiencing problems with. 

    Below I have attached the code of my AddProducts.aspx.cs file, my database procedure and the error message from attempting to "Update" the procedure respectively.

    Thanks in advance.

    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    public partial class AddProduct : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindWeight(); //create Bind for Weight category
                BindAllergens(); //create Bind for Allergens category
            }
        }
    
        private void BindWeight()
        {
            String CS = ConfigurationManager.ConnectionStrings["KETAdatabaseConnectionString1"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("select * from tblWeight", con);
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);
    
                if (dt.Rows.Count != 0)
                {
                    ddlPWeight.DataSource = dt;
                    ddlPWeight.DataTextField = "Weight";
                    ddlPWeight.DataValueField = "WeightID";
                    ddlPWeight.DataBind();
                    ddlPWeight.Items.Insert(0, new ListItem("-Select-", "0"));
                }
            }
        }
    
        private void BindAllergens()
        {
            String CS = ConfigurationManager.ConnectionStrings["KETAdatabaseConnectionString1"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("select * from tblAllergens", con);
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);
    
                if (dt.Rows.Count != 0)
                {
                    ddlPAllergens.DataSource = dt;
                    ddlPAllergens.DataTextField = "AllergenName";
                    ddlPAllergens.DataValueField = "AllergenID";
                    ddlPAllergens.DataBind();
                    ddlPAllergens.Items.Insert(0, new ListItem("-None-", "0"));
                }
            }
        }
    
        protected void btnAdd_Click(object sender, EventArgs e)
        {
            //https://www.youtube.com/watch?v=K_YKCHA5Hig
            String CS = ConfigurationManager.ConnectionStrings["KETAdatabaseConnectionString1"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("procInsertProducts",con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@PName",txtPName.Text);
                cmd.Parameters.AddWithValue("@PWeightID", ddlPWeight.SelectedItem.Value);
                cmd.Parameters.AddWithValue("@PPrice", txtPPrice.Text);
                cmd.Parameters.AddWithValue("@PBestBefore", txtPBestBefore.Text);
                cmd.Parameters.AddWithValue("@PAllergensID", ddlPAllergens.SelectedItem.Value);
                cmd.Parameters.AddWithValue("@PContains",txtPContains.Text);
                //cmd.Parameters.AddWithValue("@PImages", fuImg.);
                con.Open();
                Int64 PID = Convert.ToInt64(cmd.ExecuteScalar());
            }
        }
    }
    CREATE PROCEDURE [dbo].[procInsertProducts]
    	(
    	@PName nvarchar(50),
    	@PWeightID bigint,
    	@PPrice money,
    	@PBestBefore nvarchar(50),
    	@PAllergensID bigint,
    	@PContains nvarchar(MAX)
    	)
    AS
    	insert into tblProducts values (@PName, @PWeightID, @PPrice, @PBestBefore, @PAllergensID, @PContains)
    	Select SCOPE_IDENTITY()
    RETURN 0
    /*
    Deployment script for C:\USERS\JAMES\DESKTOP\FINAL YEAR PROJECT\APP_DATA\KETADATABASE.MDF
    
    This code was generated by a tool.
    Changes to this file may cause incorrect behavior and will be lost if
    the code is regenerated.
    */
    
    GO
    SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
    
    SET NUMERIC_ROUNDABORT OFF;
    
    
    GO
    :setvar DatabaseName "C:\USERS\JAMES\DESKTOP\FINAL YEAR PROJECT\APP_DATA\KETADATABASE.MDF"
    :setvar DefaultFilePrefix "C_\USERS\JAMES\DESKTOP\FINAL YEAR PROJECT\APP_DATA\KETADATABASE.MDF_"
    :setvar DefaultDataPath "C:\Users\James\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\"
    :setvar DefaultLogPath "C:\Users\James\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\"
    
    GO
    :on error exit
    GO
    /*
    Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
    To re-enable the script after enabling SQLCMD mode, execute the following:
    SET NOEXEC OFF; 
    */
    :setvar __IsSqlCmdEnabled "True"
    --GO
    --IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    --    BEGIN
    --        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
    --        SET NOEXEC ON;
    --    END
    
    
    GO
    USE [$(DatabaseName)];
    
    
    GO
    
    IF (SELECT OBJECT_ID('tempdb..#tmpErrors')) IS NOT NULL DROP TABLE #tmpErrors
    GO
    CREATE TABLE #tmpErrors (Error int)
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    GO
    BEGIN TRANSACTION
    GO
    PRINT N'Creating [dbo].[procInsertProducts]...';
    
    
    GO
    CREATE PROCEDURE [dbo].[procInsertProducts]
    	(
    	@PName nvarchar(50),
    	@PWeightID bigint,
    	@PPrice money,
    	@PBestBefore nvarchar(50),
    	@PAllergensID bigint,
    	@PContains nvarchar(MAX)
    	)
    AS
    	insert into tblProducts values (@PName, @PWeightID, @PPrice, @PBestBefore, @PAllergensID, @PContains)
    	Select SCOPE_IDENTITY()
    RETURN 0
    GO
    IF @@ERROR <> 0
       AND @@TRANCOUNT > 0
        BEGIN
            ROLLBACK;
        END
    
    IF @@TRANCOUNT = 0
        BEGIN
            INSERT  INTO #tmpErrors (Error)
            VALUES                 (1);
            BEGIN TRANSACTION;
        END
    
    
    GO
    
    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
    GO
    IF @@TRANCOUNT>0 BEGIN
    PRINT N'The transacted portion of the database update succeeded.'
    COMMIT TRANSACTION
    END
    ELSE PRINT N'The transacted portion of the database update failed.'
    GO
    DROP TABLE #tmpErrors
    GO
    PRINT N'Update complete.';
    
    
    GO
    

    Wednesday, February 28, 2018 1:45 PM

All replies

  • User475983607 posted

    At the end of the insert button click handler, btnAdd_Click, you have this code.

    Int64 PID = Convert.ToInt64(cmd.ExecuteScalar());

    Basically, PID is a member of the btnAdd_Click method and this variable is torn down once execution leaves the method.  If you want to keep the value then declare the variable in page class.  Or a better design is to move the Insert logic into its own method that returns the Identity value.

    In the future, if code produces errors then post the error.

    The post states you are using a MySQL Database but the code is MS SQL (T-SQL).  

    Lastly, use the Visual Studio debugger to step through the code.

    https://msdn.microsoft.com/en-us/library/y740d9d3.aspx

    Wednesday, February 28, 2018 2:12 PM