access just-entered fields of a detailsview control

Mark Klein
Mark Klein used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
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();”
 OnInserted=”dtlPayment_ItemInserted”
          …
</asp:SqlDataSource>
<asp:SqlDataSource       …

 InsertCommand  = “INSERT INTO [yourTable] ([field1], [field2]) VALUES (@value1, @value2);  Select @value_rowid=SCOPE_IDENTITY();”
 OnInserted=”dtlPmt_ItemInserted”
          …
</asp:SqlDataSource>


Top Expert 2011

Commented:
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.
Top Expert 2011

Commented:
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
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" >
        <WhereParameters>
            <asp:Parameter DbType=Guid name=currentUserId />
        </WhereParameters>
        <InsertParameters>
            <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" />
        </InsertParameters>
    </asp:LinqDataSource>

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.
Top Expert 2011

Commented:
can you show your code behind?

Author

Commented:
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.ChangeMode(DetailsViewMode.Insert)
        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)
            myConnection.Open()

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

        '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
        'LatestBidId.LatestBid(currentUserId)
        '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

Top Expert 2011
Commented:
What happens if you replace this:

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

Open in new window

With this below??

Protected Sub BidsLDS_Inserted(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceStatusEventArgs) Handles BidsLDS.Inserted
        Dim r As New BidAmount
 
        r = CType(e.Result, BidAmount)
 
        Console.WriteLine(r.id)
    End Sub


Make whatever additional changes you think you may need.

Author

Commented:
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.

Author

Commented:
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

Author

Commented:
Had to personally make adjustments for my environment

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial