Getting Scope_Identity / Output Value from Stored Procedure
Hello,
I am not able to extract the value of an output parameter. I am using an objectDataSource that references a SQL Stored Procedure. The error I get is "Object reference not set to an instance of an object."
here is the codebehind that fires the error
Thank you for your consideration
I am not able to extract the value of an output parameter. I am using an objectDataSource that references a SQL Stored Procedure. The error I get is "Object reference not set to an instance of an object."
here is the codebehind that fires the error
protected void WorkOrderDataSource_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
{
if (e.Exception == null)
{
FeedbackText.Text = e.OutputParameters["@WorkOrderID"].ToString();
}
} public static DataTable WorkOrderInsert(int WorkOrderID, string CustomerFirstName,
string CustomerLastName, string CustomerPhone, string WorkDescription,
int DepartmentID, string DepartmentPhone, int LocationID, string LocationType,
string RPFirstName, string RPLastName, string RPEmail, string RPApprovalDate,
string PMFirstName, string PMLastName, string PMEmail, string PMApprovalDate)
{
DataTable WorkOrderData = new DataTable();
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionAIMDB"].ConnectionString);
//must check for null parameters
if (CustomerFirstName == null) { CustomerFirstName = String.Empty; }
if (CustomerLastName == null) { CustomerLastName = String.Empty; }
if (CustomerPhone == null) { CustomerPhone = String.Empty; }
if (WorkDescription == null) { WorkDescription = String.Empty; }
if (DepartmentPhone == null) { DepartmentPhone = String.Empty; }
if (LocationType == null) { LocationType = String.Empty; }
if (RPFirstName == null) { RPFirstName = String.Empty; }
if (RPLastName == null) { RPLastName = String.Empty; }
if (RPEmail == null) { RPEmail = String.Empty; }
if (RPApprovalDate == null) { RPApprovalDate = String.Empty; }
if (PMFirstName == null) { PMFirstName = String.Empty; }
if (PMLastName == null) { PMLastName = String.Empty; }
if (PMEmail == null) { PMEmail = String.Empty; }
if (PMApprovalDate == null) { PMApprovalDate = String.Empty; }
try
{
SqlCommand cmd = new SqlCommand("WorkOrderInsert", connection);
//create parameters
//cmd.Parameters.AddWithValue("@WorkOrderID", WorkOrderID);
cmd.Parameters.Add("@WorkOrderID", SqlDbType.Int);
cmd.Parameters["@WorkOrderID"].Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("@CustomerFirstName", CustomerFirstName);
cmd.Parameters.AddWithValue("@CustomerLastName", CustomerLastName);
cmd.Parameters.AddWithValue("@CustomerPhone", CustomerPhone);
cmd.Parameters.AddWithValue("@WorkDescription", WorkDescription);
cmd.Parameters.AddWithValue("@DepartmentID", DepartmentID);
cmd.Parameters.AddWithValue("@DepartmentPhone", DepartmentPhone);
cmd.Parameters.AddWithValue("@LocationID", LocationID);
cmd.Parameters.AddWithValue("@LocationType", LocationType);
cmd.Parameters.AddWithValue("@RPFirstName", RPFirstName);
cmd.Parameters.AddWithValue("@RPLastName", RPLastName);
cmd.Parameters.AddWithValue("@RPEmail", RPEmail);
cmd.Parameters.AddWithValue("@RPApprovalDate", RPApprovalDate);
cmd.Parameters.AddWithValue("@PMFirstName", PMFirstName);
cmd.Parameters.AddWithValue("@PMLastName", PMLastName);
cmd.Parameters.AddWithValue("@PMEmail", PMEmail);
cmd.Parameters.AddWithValue("@PMApprovalDate", PMApprovalDate);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(WorkOrderData);
connection.Close();
}
catch
{
connection.Close();
}
return WorkOrderData;
} <asp:UpdatePanel ID="MainUpdatePanel" runat="server">
<ContentTemplate>
<div class="exampleWrapper">
<div style="float: left; width: 600px">
<asp:Wizard ID="wzd" runat="Server" Width="100%" DisplaySideBar="False" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana"
Font-Size="0.8em" OnActiveStepChanged="wzd_ActiveStepChanged" ActiveStepIndex="3">
<HeaderStyle BackColor="#5D7B9D" BorderStyle="Solid" Font-Bold="True" Font-Size="0.9em"
ForeColor="White" HorizontalAlign="Left" />
<HeaderTemplate>
<table style="width: 100%" cellpadding="0" cellspacing="0">
<tr>
<td class="wizardTitle">
<%= wzd.ActiveStep.Title%>
</td>
<td>
<table style="width: 100%; border-collapse: collapse;">
<tr>
<td style="text-align: right">
<span class="wizardProgress">Steps:</span>
</td>
<asp:Repeater ID="SideBarList" runat="server">
<ItemTemplate>
<td class="stepBreak">
</td>
<td class="<%# GetClassForWizardStep(Container.DataItem) %>" title="<%# DataBinder.Eval(Container, "DataItem.Name")%>">
<%# wzd.WizardSteps.IndexOf(Container.DataItem as WizardStep) + 1 %>
</td>
</ItemTemplate>
</asp:Repeater>
</tr>
</table>
</td>
</tr>
</table>
</HeaderTemplate>
<NavigationButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid"
BorderWidth="1px" Font-Names="Verdana" Font-Size="0.8em" ForeColor="#284775" />
<SideBarButtonStyle BorderWidth="0px" Font-Names="Verdana" ForeColor="White" />
<SideBarStyle BackColor="#7C6F57" BorderWidth="0px" Font-Size="0.9em" VerticalAlign="Top" />
<SideBarTemplate>
</SideBarTemplate>
<StepStyle BorderWidth="0px" ForeColor="#5D7B9D" />
<WizardSteps>
<asp:WizardStep runat="server">
<div class="stepWrapper">
<ucUserInfo:UserInfo ID="userinfoControl" runat="server" />
</div>
</asp:WizardStep>
<asp:WizardStep runat="server">
<div class="stepWrapper">
<ucWorkOrder:WorkOrder ID="workorderControl" runat="server" />
</div>
</asp:WizardStep>
<asp:WizardStep runat="server">
<div class="stepWrapper">
<ucApprovals:Approvals ID="approvalsControl" runat="server" />
</div>
</asp:WizardStep>
<asp:WizardStep runat="server" StepType="Complete">
<div class="stepWrapper">
<div align="center">
<asp:ObjectDataSource ID="WorkOrderDataSource" runat="server" InsertMethod="WorkOrderInsert"
TypeName="WorkOrderBLL" OnInserted="WorkOrderDataSource_Inserted">
<InsertParameters>
<asp:Parameter Direction="Output" Name="WorkOrderID" Type="Int32" />
<asp:ControlParameter ControlID="userInfoControl" Name="CustomerFirstName" PropertyName="CustomerFirstName"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="userInfoControl" Name="CustomerLastName" PropertyName="CustomerLastName"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="userInfoControl" Name="CustomerPhone" PropertyName="CustomerPhone"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="workorderControl" Name="WorkDescription" PropertyName="WorkDescription"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="workorderControl" Name="DepartmentID" PropertyName="Department"
Direction="Input" Type="Int32" />
<asp:ControlParameter ControlID="workorderControl" Name="DepartmentPhone" PropertyName="DepartmentPhone"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="workorderControl" Name="LocationID" PropertyName="Address"
Direction="Input" Type="Int32" />
<asp:ControlParameter ControlID="workorderControl" Name="LocationType" PropertyName="LocationType"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="approvalsControl" Name="RPFirstName" PropertyName="RPFirstName"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="approvalsControl" Name="RPLastName" PropertyName="RPLastName"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="approvalsControl" Name="RPEmail" PropertyName="RPEmail"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="approvalsControl" Name="RPApprovalDate" PropertyName="RPApprovalDate"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="approvalsControl" Name="PMFirstName" PropertyName="PMFirstName"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="approvalsControl" Name="PMLastName" PropertyName="PMLastName"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="approvalsControl" Name="PMEmail" PropertyName="PMEmail"
Direction="Input" Type="String" />
<asp:ControlParameter ControlID="approvalsControl" Name="PMApprovalDate" PropertyName="PMApprovalDate"
Direction="Input" Type="String" />
</InsertParameters>
<SelectParameters>
<asp:Parameter Name="WorkOrderID" Type="Int32" />
<asp:Parameter Name="CustomerFirstName" Type="String" />
<asp:Parameter Name="CustomerLastName" Type="String" />
<asp:Parameter Name="CustomerPhone" Type="String" />
<asp:Parameter Name="WorkDescription" Type="String" />
<asp:Parameter Name="DepartmentID" Type="Int32" />
<asp:Parameter Name="DepartmentPhone" Type="String" />
<asp:Parameter Name="LocationID" Type="Int32" />
<asp:Parameter Name="LocationType" Type="String" />
<asp:Parameter Name="RPFirstName" Type="String" />
<asp:Parameter Name="RPLastName" Type="String" />
<asp:Parameter Name="RPEmail" Type="String" />
<asp:Parameter Name="RPApprovalDate" Type="String" />
<asp:Parameter Name="PMFirstName" Type="String" />
<asp:Parameter Name="PMLastName" Type="String" />
<asp:Parameter Name="PMEmail" Type="String" />
<asp:Parameter Name="PMApprovalDate" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
<asp:Button ID="SaveButton" runat="server" Text="Save" OnClick="SaveButton_Click">
</asp:Button></div>
</div>
</asp:WizardStep>
</WizardSteps>
</asp:Wizard>
</div>
<div class="previewWrapper">
<ucPreview:Preview ID="previewControl" runat="server" />
</div>
</div>
<asp:Label ID="FeedbackText" runat="server"></asp:Label>
</ContentTemplate>
</asp:UpdatePanel>USE [AIM]
GO
/****** Object: StoredProcedure [dbo].[WorkOrderInsert] Script Date: 03/14/2011 12:28:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Martin Jordan
-- Create date: 3/9/2011
-- Description: Insert WorkOrder related information
-- =============================================
ALTER PROCEDURE [dbo].[WorkOrderInsert]
-- Add the parameters for the stored procedure here
(
@WorkOrderID int OUTPUT,
@CustomerFirstName nvarchar(25),
@CustomerLastName nvarchar(50),
@CustomerPhone nvarchar(20),
@WorkDescription nvarchar(4000),
@DepartmentID int,
@DepartmentPhone nvarchar(20),
@LocationID int,
@LocationType nvarchar(20),
@RPFirstName nvarchar(25),
@RPLastName nvarchar(50),
@RPEmail nvarchar(50),
@RPApprovalDate date,
@PMFirstName nvarchar(25),
@PMLastName nvarchar(50),
@PMEmail nvarchar(50),
@PMApprovalDate date
)
AS
BEGIN TRANSACTION
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- select
-- insert
INSERT INTO [FAC_WorkOrder]
([CustomerFirstName],
[CustomerLastName],
[CustomerPhone],
[WorkDescription],
[DepartmentID],
[DepartmentPhone],
[LocationID],
[LocationType],
[RPFirstName],
[RPLastName],
[RPEmail],
[RPApprovalDate],
[PMFirstName],
[PMLastName],
[PMEmail],
[PMApprovalDate])
VALUES
(@CustomerFirstName,
@CustomerLastName,
@CustomerPhone,
@WorkDescription,
@DepartmentID,
@DepartmentPhone,
@LocationID,
@LocationType,
@RPFirstName,
@RPLastName,
@RPEmail,
@RPApprovalDate,
@PMFirstName,
@PMLastName,
@PMEmail,
@PMApprovalDate)
SET @WorkOrderID = SCOPE_IDENTITY()
SELECT SCOPE_IDENTITY()
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error in inserting into FAC_Department.', 16, 1)
RETURN
END
COMMITThank you for your consideration
ASKER
Hmm? That's already in there, did I put it in the wrong place?
Have you declared the parameter @WorkOrderID?
ASKER
yes, i define it in the SP here: @WorkOrderID int OUTPUT,
in the BLL here: cmd.Parameters.Add("@WorkO
cmd.Parameters["@WorkOrder
in the markup here: <asp:Parameter Direction="Output" Name="WorkOrderID" Type="Int32" />
in the BLL here: cmd.Parameters.Add("@WorkO
cmd.Parameters["@WorkOrder
in the markup here: <asp:Parameter Direction="Output" Name="WorkOrderID" Type="Int32" />
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
nm
ASKER
not doing it this way.. have too little time to wait for someone to pore over my code and suggest where something might be changed.
this is a time sensitive project
this is a time sensitive project
SET @WorkOrderID = SCOPE_IDENTITY()