locked
Cannot insert explicit value for identity column in table 'ShopCartItem' when IDENTITY_INSERT is set to OFF. RRS feed

  • Question

  • User-119139783 posted

     This is the error i got when i tried to add my item into the shopping cart.

    Cannot insert explicit value for identity column in table 'ShopCartItem' when IDENTITY_INSERT is set to OFF.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    Exception Details: System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'ShopCartItem' when IDENTITY_INSERT is set to OFF.

    Source Error: 

    Line 97:             cmd3.Parameters.AddWithValue("@ProductName", objProduct.Title)
    Line 98: cmd3.Parameters.AddWithValue("@ProID", ProID)
    Line 99: cmd3.ExecuteNonQuery()
    Line 100:
    Line 101: NumItem = NumItem + 1

    Source File: D:\Ngee Ann poly stuff\Year 3\IHP\FYP\App_Code\ShopCart.vb    Line: 99 

    Stack Trace: 

    [SqlException (0x80131904): Cannot insert explicit value for identity column in table 'ShopCartItem' when IDENTITY_INSERT is set to OFF.]
    System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
    System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
    System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
    System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
    System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
    System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
    System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
    ShopCart.AddItem(Int32 ProID, Int32 quantity) in D:\Ngee Ann poly stuff\Year 3\IHP\FYP\App_Code\ShopCart.vb:99
    ProductDetails.btnBuy_Click(Object sender, EventArgs e) in D:\Ngee Ann poly stuff\Year 3\IHP\FYP\ProductDetails.aspx.vb:51
    System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
    System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
    System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565



    Version Information: Microsoft .NET Framework Version:2.0.50727.3082; ASP.NET Version:2.0.50727.3082

     

     

    My codes for Shopcart class file.

    Imports Microsoft.VisualBasic
    Imports System.Data.SqlClient
    Imports System.Data

    Public Class ShopCart
        Public ShopCartID As Integer
        Public MemID As Integer
        Public NumItem As Integer
        Public ShippingCharge As Double
        Public TaxCharge As Double
        Public OrderPlaced As Integer

        Public Sub Create()

            Dim strConn As String
            strConn = ConfigurationManager.ConnectionStrings("IHP09ConnectionString").ToString
            Dim conn As New SqlConnection(strConn)

            Dim strSql As String
            strSql = "INSERT INTO ShopCart(MemID) VALUES(@MemID)"

            Dim cmd As New SqlCommand(strSql, conn)

            cmd.Parameters.AddWithValue("@MemID", MemID)

            conn.Open()

            cmd.ExecuteNonQuery()

            cmd.CommandText = "SELECT MAX(ShopCartID) As ShopCartID " & _
            "FROM ShopCart WHERE MemID=@MemID"

            Dim da As New SqlDataAdapter(cmd)

            Dim ds As New DataSet

            da.Fill(ds, "ShopCart")

            conn.Close()

            ShopCartID = ds.Tables("ShopCart").Rows(0)("ShopCartID")

        End Sub

        Public Sub AddItem(ByVal ProID As Integer, _
                           ByVal quantity As Integer)

            Dim strConn As String
            strConn = ConfigurationManager.ConnectionStrings("IHP09ConnectionString").ToString
            Dim conn As New SqlConnection(strConn)

            Dim objProduct As New Product
            objProduct.ID = ProID
            objProduct.GetDetails()

            Dim strSql As String
            strSql = "SELECT Quantity FROM ShopCartItem " & _
                     "WHERE ProID=@ProID " & _
                     "AND ShopCartID=@ShopCartID "
            Dim cmd1 As New SqlCommand(strSql, conn)

            cmd1.Parameters.AddWithValue("@ProID", ProID)
            cmd1.Parameters.AddWithValue("@ShopCartID", ShopCartID)

            Dim da1 As New SqlDataAdapter(cmd1)

            Dim ds As New DataSet

            conn.Open()

            da1.Fill(ds, "ShopCartItem")

            If ds.Tables("ShopCartItem").Rows.Count <> 0 Then

                strSql = "UPDATE ShopCartItem SET Quantity=@Quantity " & _
                "WHERE ProID=@ProID AND ShopCartID=@ShopCartID"

                Dim cmd2 As New SqlCommand(strSql, conn)

                cmd2.Parameters.AddWithValue("@Quantity", quantity)
                cmd2.Parameters.AddWithValue("@ProID", ProID)
                cmd2.Parameters.AddWithValue("@ShopCartID", ShopCartID)
                cmd2.ExecuteNonQuery()

            Else

                strSql = "INSERT INTO ShopCartItem " & _
                "(ShopCartID, Quantity, Price, Name, ProID)" & _
                " VALUES (@ShopCartId, @Quantity, @ProductPrice, " & _
                "@ProductName, @ProID)"

                Dim cmd3 As New SqlCommand(strSql, conn)

                cmd3.Parameters.AddWithValue("@ShopCartId", ShopCartID)
                cmd3.Parameters.AddWithValue("@Quantity", quantity)
                cmd3.Parameters.AddWithValue("@ProductPrice", objProduct.Price)
                cmd3.Parameters.AddWithValue("@ProductName", objProduct.Title)
                cmd3.Parameters.AddWithValue("@ProID", ProID)
                cmd3.ExecuteNonQuery()

                NumItem = NumItem + 1





            End If

            conn.Close()

        End Sub

        Public Function GetCartItems() As DataSet
            Dim strConn As String
            strConn = ConfigurationManager.ConnectionStrings("IHP09ConnectionString").ToString
            Dim conn As New SqlConnection(strConn)

            Dim strSql As String
            strSql = "SELECT ShopCartID, ProductID, Name, Price, Quantity, " & _
                     "Price * Quantity as Total " & _
                     "FROM ShopCartItem " & _
                     "WHERE ShopCartID=@ShopCartID"
            Dim cmd As New SqlCommand(strSql, conn)

            cmd.Parameters.AddWithValue("@ShopCartID", ShopCartID)

            Dim da As New SqlDataAdapter(cmd)

            Dim ds As New DataSet

            conn.Open()

            da.Fill(ds, "ShopCartItem")

            conn.Close()

            Return ds
        End Function

        Public Function ComputeSubTotal() As Double
            Dim dblSubTotal As Double
            ' Define a conection to database
            ' Read connection string from the web.config file.
            Dim strConn As String
            strConn = ConfigurationManager.ConnectionStrings("IHP09ConnectionString").ToString
            Dim conn As New SqlConnection(strConn)
            ' Calculate the subtotal.
            Dim strSql As String
            strSql = "SELECT SUM(Price * Quantity) as SubTotal " & _
            "FROM ShopCartItem " & _
            "WHERE ShopCartID=@ShopCartID"
            ' Define an Command object to execute the SQL statement
            Dim cmd As New SqlCommand(strSql, conn)
            ' Add parameter to the SQL command
            cmd.Parameters.AddWithValue("@ShopCartID", ShopCartID)
            ' Define a data adapter to fetch data
            Dim da As New SqlDataAdapter(cmd)
            ' Define a data set to hold the data fetched
            Dim ds As New DataSet
            ' Open database connection
            conn.Open()
            ' Fetch data to a table named as "ShopCartItem" in the dataset
            da.Fill(ds, "ShopCartItem")
            ' Close the database connection
            conn.Close()
            ' Read the result
            If ds.Tables("ShopCartItem").Rows.Count <> 0 Then
                dblSubTotal = ds.Tables("ShopCartItem").Rows(0)("SubTotal")
            Else
                dblSubTotal = 0.0
            End If
            Return dblSubTotal
        End Function

        Public Sub GetActiveShoppingCart()





            Dim strConn As String
            strConn = ConfigurationManager.ConnectionStrings("IHP09ConnectionString").ToString
            Dim conn As New SqlConnection(strConn)

            Dim strSql As String

            strSql = "SELECT sci.ShopCartID " & _
                    "FROM ShopCart sc INNER JOIN ShopCartItem sci " & _
                    "ON sc.ShopCartID=sci.ShopCartID " & _
                    "WHERE sc.MemID=@MemID " & _
                    " AND sc.OrderPlaced=0"

            Dim cmd As New SqlCommand(strSql, conn)

            cmd.Parameters.AddWithValue("@MemID", MemID)

            Dim da As New SqlDataAdapter(cmd)
            ' Define a data set to hold the data fetched
            Dim ds As New DataSet

            conn.Open()
            da.Fill(ds, "ShopCart")
            conn.Close()

            If ds.Tables("ShopCart").Rows.Count <> 0 Then
                ShopCartID = ds.Tables("ShopCart").Rows(0)("ShopCartID")
                NumItem = ds.Tables("ShopCart").Rows.Count

            End If









        End Sub















    End Class

     

    Calling the method to add the item in shopping cart in webform.

     

    Protected Sub btnBuy_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBuy.Click
            If Session("MemID") Is Nothing Then
                Response.Redirect("MemberLogin.aspx")
            End If

            Dim intProductID, intQuantity As Integer

            intProductID = CType(Request.QueryString("ProID"), Integer)
            intQuantity = CType(txtQty.Text, Integer)

            Dim objShopCart As New ShopCart

            'Check whether a shopping cart has been created.
            If Session("ShopCart") Is Nothing Then
                'Create a new shopping cart
                objShopCart.MemID = CType(Session("MemID"), Integer)
                objShopCart.Create()
            Else
                'Use the existing shopping cart
                objShopCart = Session("ShopCart")
            End If

            'Add the selected product to shopping cart
            objShopCart.AddItem(intProductID, intQuantity)

            'Save the shopping cart in session variable
            Session("ShopCart") = objShopCart

            'Re-direct to Shopping Cart page
            Response.Redirect("ShoppingCart.aspx")

        End Sub

    End Class

     

     

    Tuesday, June 16, 2009 3:11 AM

Answers

  • User-1956254743 posted

    Pls go though the following solution:

    http://forums.asp.net/t/1197052.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 16, 2009 3:40 AM