nickg21 0 Newbie Poster

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()">
            <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" />

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)">
<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" />     

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

        MultiView1.ActiveViewIndex = 4
    End Sub
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.