Avatar of Mark Klein
Mark Klein
Flag for United States of America asked on

access just-entered fields of a detailsview control

I am trying to access two fields that were just entered into a Sql Server table thru a detailsview control.  One field is the auto-generated identity field (that old problem) and the other field is a decimal. Because the insertion happens in the DV, I don't know how to use a scope_identity to get the identity column.  I tried unsuccessfully to use e.Values in the ItemInserted event. How do I access those fields for further manipulation in another module?
ASP.NETMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Mark Klein

8/22/2022 - Mon

Assuming you already have your details view set up correctly, shouldn't it be something like this?

<asp:SqlDataSource       …

 InsertCommand  = “INSERT INTO [tblPayments] ([BillingName], [BillingAmount]) VALUES (@BillingName, @BillingAmount);  Select @Payment_rowid=SCOPE_IDENTITY();”
<asp:SqlDataSource       …

 InsertCommand  = “INSERT INTO [yourTable] ([field1], [field2]) VALUES (@value1, @value2);  Select @value_rowid=SCOPE_IDENTITY();”


I pushed the button so quicky.

On the same DV markup, you have this insert parameter

  <asp:parameter direction=”Output” name=”value_rowid” type=”Int32¿ />

Then the code behind:

Protected Sub dtlPmt_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
       Dim thisRowID As Integer
       thisRowID = e.Command.Parameters(”@value_rowid”).Value
End Sub

There are other ways to do this.

Your help has saved me hundreds of hours of internet surfing.
Mark Klein

Sammy, these are some good ideas, but I am still struggling.  Firstly, I'm using LinqDataSource, not SqlDataSource.  Besides the autogenerated BidId, I am also trying to retrieve BidAmount. I did modify my set of input parameters for the LDS to look like
<asp:LinqDataSource ID="BidsLDS" runat="server" 
        ContextTypeName="lucidequipmentDataContext" EnableInsert="True" 
        EntityTypeName="" TableName="Bids" Where = "BuyerId == @currentUserId" >
            <asp:Parameter DbType=Guid name=currentUserId />
            <asp:Parameter Name = "equipmentId" DefaultValue="@SelectedMasterId"    />
            <asp:Parameter Name = "BuyerId" DefaultValue="@buyerId" />
            <asp:Parameter Name = "BidAmount"  direction=inputoutput  dbtype = decimal />
            <asp:Parameter Name = "BidDate" DefaultValue= "@bidDate" />

Open in new window

you can see where I added the "direction=inputoutput.

I already have event handlers for BidLDS_inserted and for the DV CollectBidDV_itemInserted.  I tried to put the "onInserted=" into the Linq code above and it threw an error about the signature, so since I already had the handlers and they were being accessed (I've been debugging), I dropped that.

In the DV_ItemInserted handler, I tried adding code
Dim bidamount As String = CType("bidamount", String)
to collect the bidAmount, but only got the string "bidamount". Using "decimal" threw a casting error.

I don't see how the row_id will catch the autogenerated bidId, nor do I see where to put the SELECT statement with the Scope_Identity.  I'm capable of generating a stored proc if that will help.

Thanks for your patience with my ignorance as I am an old geezer trying to recover my coding chops.

can you show your code behind?
Mark Klein

the relevant handlers are towards the bottom; I'm collecting values to feed to my send mail sub, which is working.

Imports System.Web.Security
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.DynamicData
Imports System.Web.Routing
Imports System.Web.UI.WebControls.Expressions
Imports System.Web.UI.WebControls.GridView

Partial Class BuyerPages_PlaceABid
    Inherits System.Web.UI.Page
    Dim currentUser As MembershipUser = Membership.GetUser()
    Dim currentUserId As Guid = DirectCast(currentUser.ProviderUserKey, Guid)
    Dim BuyerId As Guid = currentUserId

    Protected Sub BidSelectionListGV_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles BidSelectionListGV.SelectedIndexChanged
        ShowDetailsButton.Visible = True
        PlaceBidsButton.Visible = True
        ManageBidsButton.Visible = True
        Dim rowIndex As Integer = BidSelectionListGV.SelectedIndex
        Dim selectedMasterId As Integer = BidSelectionListGV.DataKeys(rowIndex).Value
        ViewState("eqIndex") = selectedMasterId
    End Sub
    Protected Sub EqTypesLDS_Selecting(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles EqTypesLDS.Selecting
        Dim DetailsIndex As Integer = ViewState("eqIndex")
        e.WhereParameters("equipment_master_id") = DetailsIndex
    End Sub

    Protected Sub PlaceBidsButton_Click(sender As Object, e As System.EventArgs) Handles PlaceBidsButton.Click
        'turn on (make visible) the DetailsView to collect the bid
        CollectBidDV.Visible = True
    End Sub

    Protected Sub ManageBidsButton_Click(sender As Object, e As System.EventArgs) Handles ManageBidsButton.Click
        'turn off the bid collection DV
        CollectBidDV.Visible = False
        'turn off the equipment details GV
        EqDetailsGV.Visible = False
        'turn off the bid success label
        BidSuccessLabel.Visible = False
        'turn on gridview showing bid list for current user
        ManageBidsGV.Visible = True
    End Sub

    Protected Sub BidsLDS_Selecting(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles BidsLDS.Selecting
        e.WhereParameters("currentUserId") = currentUserId
    End Sub

    Protected Sub CollectBidDV_ItemInserting(sender As Object, e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles CollectBidDV.ItemInserting
        'define equipmentId, currentUserId, BidDate and of course the newly collected bid amount
        'then stuff them all into the Bids table
        'first, the equipmentId comes from the viewstate collection

         Dim EquipmentId As Integer = ViewState("eqIndex")
        e.Values("EquipmentId") = ViewState("eqIndex")

        'the BuyerId by our usual method
        Dim BuyerId As Guid = currentUserId
        e.Values("buyerId") = currentUserId

        'and the datetime
        e.Values("biddate") = DateTime.Now
        Dim currentDate As DateTime = DateTime.Now

    End Sub

    Protected Sub BidsLDS_Inserted(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceStatusEventArgs) Handles BidsLDS.Inserted
        CollectBidDV.Visible = False
        BidSuccessLabel.Visible = True

        'now change the Status in the equipment_master table
        Dim EquipmentId As Integer = ViewState("eqIndex")

        Dim connectionString As String = ConfigurationManager.ConnectionStrings("lucidequipmentConnectionString").ConnectionString
        Dim updateSql As String = "UPDATE equipment_master SET equipment_master.statusId='4' WHERE equipment_master.id = " & EquipmentId

        Using myConnection As New SqlConnection(connectionString)

            Dim myCommand As New SqlCommand(updateSql, myConnection)
            myCommand.Parameters.Add("@id", SqlDbType.Int).Value = EquipmentId
        End Using

        'send mail to seller
        Session("keyId") = EquipmentId

        'Dim myBidAmount As String = CollectBidDV.Rows(0).Cells(2).ToString()

        'get currentuser
        Dim currentUser As MembershipUser = Membership.GetUser()
        Dim currentUserId As Guid = DirectCast(currentUser.ProviderUserKey, Guid)
        Dim buyerId As Guid = currentUserId
        ' Dim bidid As Integer = CollectBidDV.SelectedValue
        'Dim bidId As Integer = Session.Item("BidId")

    End Sub

    Protected Sub ManageBidsLDS_Selecting(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles ManageBidsLDS.Selecting
        e.WhereParameters("currentUserId") = currentUserId
    End Sub

    Protected Sub ShowDetailsButton_Command(sender As Object, e As System.Web.UI.WebControls.CommandEventArgs) Handles ShowDetailsButton.Command
        EqDetailsGV.Visible = True
    End Sub

    Protected Sub DetailValuesLDS_Selecting(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles DetailValuesLDS.Selecting
        e.WhereParameters("equipment_master_id") = ViewState("eqIndex")
    End Sub

    Protected Sub CollectBidDV_ItemInserted(sender As Object, e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles CollectBidDV.ItemInserted
        Dim msgName As String = "bidsubmitted"
        Dim bidamount As String = CType("bidamount", String)
        ' Dim thisRowID As Integer
        'thisRowID = e.Command.Parameters("@value_rowid").Value
        Dim bidid As Integer = CollectBidDV.SelectedValue
        SendSystemMail.SendSysMail(msgName, bidid)
    End Sub
End Class

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mark Klein

even though BidAmount is one of the insert parameters in the BidLDS data source and in the DV itself, I get an error "Type BidAmount is not defined"

Tried putting a Dim as .. before it, but no go.
Mark Klein

here's the code that solved the problem. Needed nothing in the aspx , put this in the .vb code-behind.  It's actually very close to the originally suggested solution, just had to find the appropriate pieces.
 Dim newbid As Bid
        newbid = CType(e.Result, Bid)
        Dim newbidId As Integer = newbid.BidId

Open in new window

After I got the autogenerated Id, I could call the table and get the bid amount
Mark Klein

Had to personally make adjustments for my environment
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy