locked
Querying last created record from a mdb file almost complete really need assistance RRS feed

  • Question

  • User303351178 posted

    So i have these 2 pages of code;

    Imports System.Data
    Imports System.Data.OleDb
    Imports System.Web.Configuration


    Checkout
    Partial Class Checkout
        Inherits System.Web.UI.Page
        Dim DbConnection As OleDbConnection
        Dim ConnString As String = "Provider = Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|MMGVDB.mdb"
        Dim insertString As String = "Insert Into MMGVCustomer (ShippingName, ShippingAddress, ShippingSuburb, ShippingPostcode, BillingName, BillingAddress, BillingSuburb, BillingPostcode, CardType, CardNo, CardExpiryDate, OrderStatus) Values (@ShippingName, @ShippingAddress, @ShippingSuburb, @ShippingPostcode, @BillingName, @BillingAddress, @BillingSuburb, @BillingPostcode, @CardType, @CardNo, @CardExpiryDate, @OrderStatus)"
        Dim GetCustomerOrderNameString As String = "Select OrderNumber From MMGVCustomer Where ShippingName = ?"
        Dim OrderID As Integer
        Dim SetOrderId As New Product





        Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

            LblTotalPrice.Text = "Total: " & ShoppingCart.Instance.GetSubTotal().ToString("C")

        End Sub

        Protected Sub BtnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSubmit.Click

            Using DbConnection As New OleDbConnection(ConnString)
                Using DbCommand As New OleDbCommand(insertString, DbConnection)

                    DbCommand.CommandType = CommandType.Text
                    DbCommand.Parameters.AddWithValue("ShippingName", TxtShippingName.Text)
                    DbCommand.Parameters.AddWithValue("ShippingAddress", TxtShippingAddress.Text)
                    DbCommand.Parameters.AddWithValue("ShippingSuburb", TxtShippingSuburb.Text)
                    DbCommand.Parameters.AddWithValue("ShippingPostcode", TxtShippingPostcode.Text)
                    DbCommand.Parameters.AddWithValue("BillingName", TxtBillingName.Text)
                    DbCommand.Parameters.AddWithValue("BillingAddress", TxtBillingAddress.Text)
                    DbCommand.Parameters.AddWithValue("BillingSuburb", TxtBillingSuburb.Text)
                    DbCommand.Parameters.AddWithValue("BillingPostcode", TxtBillingPostcode.Text)
                    DbCommand.Parameters.AddWithValue("CardType", DDLPaymentType.Text)
                    DbCommand.Parameters.AddWithValue("CardNo", TxtCCNo.Text)
                    DbCommand.Parameters.AddWithValue("CardExpiryDate", TxtCCED.Text)
                    DbCommand.Parameters.AddWithValue("OrderStatus", "Processing")
                    DbConnection.Open()
                    DbCommand.ExecuteNonQuery()
                    DbCommand.CommandText = GetCustomerOrderNameString
                    OrderID = DbCommand.ExecuteScalar()
                    SetOrderId.OrderNo = OrderID
                End Using


            End Using



            Response.Redirect("CheckoutConfirmation.aspx")

        End Sub



    End Class

    Checkout Confirmation

    Imports System.Data
    Imports System.Data.OleDb
    Imports System.Web.Configuration

    Partial Class CheckoutConfirmation

        Inherits System.Web.UI.Page
        Dim DbConnection As OleDbConnection
        Dim ConnString As String = "Provider = Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|MMGVDB.mdb"
        Dim GetOrderID As New Product

        


        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

            If Not IsPostBack Then
                BindData()
            End If

            LblOrderNo.Text = GetOrderID.OrderNo

        End Sub

        Protected Sub BindData()

            gvShoppingCart.DataSource = ShoppingCart.Instance.Items
            gvShoppingCart.DataBind()
        End Sub

        Protected Sub gvShoppingCart_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Handles gvShoppingCart.RowDataBound

            If e.Row.RowType = DataControlRowType.Footer Then
                e.Row.Cells(3).Text = "Total: " & ShoppingCart.Instance.GetSubTotal().ToString("C")
            End If
        End Sub


    End Class

    I want the order no of the record created on the checkout page to be displayed on a label in the checkout confirmation page. I've been trying to accomplish this for hours and am really struggling to find a solution. It is the last main part of my website and am really desperate for soem assistance. The code complies but the "Lblorderno" does not change. I would be really grateful for any assistance with this.

    Sunday, September 25, 2011 7:44 AM

Answers

  • User1508394307 posted

    There are few ways

    SELECT TOP 1 ordernumber FROM...
    SELECT MAX(ordernumber) FROM...

    or using @@Identity

    SELECT ordernumber FROM... WHERE PK=@@Identity

    http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record 

    Using @@Identity is the most preferable way, because it returns the value of an autoincrement column that is generated on the same connection. Using TOP and MAX can return wrong result when two users add an order at the same time. You can use TOP and MAX when you will have another unique identification for the current user, like sessionID, or customerID.

    For example:

    SELECT TOP 1 ordernumber FROM... WHERE usersession='sessionkeyhere'

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 25, 2011 8:24 AM

All replies

  • User1508394307 posted

    There are few ways

    SELECT TOP 1 ordernumber FROM...
    SELECT MAX(ordernumber) FROM...

    or using @@Identity

    SELECT ordernumber FROM... WHERE PK=@@Identity

    http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record 

    Using @@Identity is the most preferable way, because it returns the value of an autoincrement column that is generated on the same connection. Using TOP and MAX can return wrong result when two users add an order at the same time. You can use TOP and MAX when you will have another unique identification for the current user, like sessionID, or customerID.

    For example:

    SELECT TOP 1 ordernumber FROM... WHERE usersession='sessionkeyhere'

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 25, 2011 8:24 AM
  • User3866881 posted

    Hello Fargo94:)

    Anothere way——If your OrderId is auto-generated stepped from 1 by 1, you can try this:'

    Select top 1 OrderId from OrderTable order by OrderId desc

    Thus you can ALWAYS get the last record's number

    Hope this helps:)

    Monday, September 26, 2011 9:33 PM