Using Scope_Identity() variable in a different SQLDataSource ad-hoc

.NET
1

Hello everyone,
I am making a set of tables that have relational keys associated with them like an order system. Basically I am trying to store the Customer Info, then the order and so on and so forth, but I need to store the “customer” ID in the Order table in a relational field, I thought I could accomplish this using Scope_identity() but apparently I can’t figure out how to do it correctly. I will include my code with a stepthrough of the process and any help would be great.
Thanks in advance,
Nick G

First I insert the into into tblPatInfo, which works correctly;

    <asp:SqlDataSource ID="InsertPatInfo" runat="server" ConnectionString="<%$ ConnectionStrings:DataConnectionString %>"
    providername="<%$ ConnectionStrings:DataConnectionString.ProviderName %>"
        InsertCommand = "Insert into tblPatInfo(PatName, PatAge, PatState, PatCountry, PatPhone, PatCell) VALUES 
        (@PatName, @PatAge, @PatState, @PatCountry, @PatPhone, @PatCell); SET @NewPatID = SCOPE_IDENTITY()">
        <InsertParameters>
            <asp:ControlParameter ControlID = "PatInfoName" Name="PatName" PropertyName="text"/>
            <asp:ControlParameter ControlID = "PatInfoAge" Name="PatAge" PropertyName="text" />
            <asp:ControlParameter ControlID = "PatInfoState" Name="PatState" PropertyName="selectedvalue" />
            <asp:ControlParameter ControlID = "PatInfoCountry" Name="PatCountry" PropertyName="selectedvalue" />
            <asp:ControlParameter ControlID = "PatInfoPhone" Name="PatPhone" PropertyName = "text" />
            <asp:ControlParameter ControlID = "PatInfoCell" Name="PatCell" PropertyName="Text" />
            <asp:Parameter DbType="Int32" Direction="Output" Name="NewPatID" />
        </InsertParameters>

I then use the event_handler for the inserted information to assign the NewPatID variable.

    Protected Sub InsertPatInfo_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles InsertPatInfo.Inserted, InsInqInfo.Inserted
        Dim NewPatID As Integer = e.Command.Parameters("@NewPatID").Value
    End Sub

I then try to use this variable in the next insert statement in a different SqlDataSource;

  <asp:SqlDataSource ID="InsInqInfo" runat="server" ConnectionString="<%$ ConnectionStrings:DataConnectionString %>"
        providerName="<%$ ConnectionStrings:DataConnectionString.ProviderName %>"
        InsertCommand = "Insert into tblInquirer(InqPatID, InqName, InqState, InqCountry, InqPhone, InqRelation, InqVia, InqCareLevel, InqProgram) VALUES 
        (@NewPatID, @InqName, @InqState, @InqCountry, @InqPhone, @InqRelation, @InqVia, @InqCareLevel, @InqProgram)">
        <InsertParameters>
<asp:ControlParameter ControlID="NewPatID" Name="NewPatID" PropertyName="value" />
            <asp:ControlParameter ControlID = "InqName" Name="InqName" PropertyName="text"/>
            <asp:ControlParameter ControlID = "InqStateList" Name="InqState" PropertyName="selectedvalue" />
            <asp:ControlParameter ControlID = "InqCountry" Name="InqCountry" PropertyName="selectedvalue" />
            <asp:ControlParameter ControlID = "InqPhone" Name="InqPhone" PropertyName="Text" />
            <asp:ControlParameter ControlID = "radInqRel" Name="InqRelation" PropertyName="selectedvalue" />
            <asp:ControlParameter ControlID = "InitInqVia" Name="InqVia" PropertyName = "selectedvalue" />
            <asp:ControlParameter ControlID = "CareLevel" Name="InqCareLevel" PropertyName="selectedvalue" />
            <asp:ControlParameter ControlID = "ProgSelect" Name="InqProgram" PropertyName="selectedvalue" />     
        </InsertParameters>
    </asp:SqlDataSource>

However, I keep receiving the error that @NewPatID isn’t set and I just don’t know where to go from here, I have been digging for days now, and still no luck.

Here is my button click_event call for these datasources, as well as more that I currently am not executing but will also use the @NewPatID variable as well as the scope_identity() I will pull from the Insert into tblInquirer once I figure out how to do it correctly. Thank you for taking the time to look through this, anything pointing me in the right direction will be great.

    Protected Sub Button7_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button7.Click
 
        InsertPatInfo.Insert()
        'InsInquiryInfo.Insert()
        'InsClinInfo.Insert()
        InsInqInfo.Insert()
        MultiView1.ActiveViewIndex = 4
    End Sub
2

Definitely doable. Personally, I would dodge the datasource controls entirely but I’m pedantic like that.

The problem you are running into is that your little local variable runs out of scope after the page executes so the next command can’t grab it. Easiest thing to do would be to stuff it into a HiddenField control, but you could roll your own ViewState backed property too.

3

Thank you for your reply, is there really no other way than storing it in a hidden field? I tried assigning it to a session() variable and that wasn’t accessible either. What approach would I take if I wasn’t to use an SqlDataSource, I am pretty new to .Net, I always coded in PHP and this scope_identity stuff is getting extremely frustrating.
Thanks again,
Nick

4

Wow my vb is rusty, and my webforms for that matter. You could do this with SqlDataSource controls too.

What you are doing could work, the trick is your NewId variable has the wrong scope – it is a member of the InsertPatInfo_Inserted function, it should be a member of the page’s class. Then you should be able to stash the ID in a variable and grab it down the line.

I would actually just make them one single sql insert statement, much cleaner and easier than passing the ID around, and fewer calls to the server. To do so, just string the 2 statements in one of your SqlDataSource controls and use that.

You might want to check out ASP.NET web pages, it is a more PHP like experience and definitely makes sense for smaller sites.