Formview using scope_identity(), ItemInserted event RSS

• • Reply
  

  tennsmith

  0 Points

  1 Post

  Formview using scope_identity(), ItemInserted event

  Nov 10, 2012 08:11 PM|LINK

  FYI...I only started using SQL and .NET about a year ago, so my terminology may be off a lil bit, but I'll try my best to explain my problem.

  Generally I don't post questions because I can almost always google to find the answer I'm looking for.  However, I'm stumped on this one.  So I'm building a custom helpdesk call ticket system for mainly a learning experience, but my job is gonna use it when I finish.  Ocassionally when a staff member inserts a new call ticket they will need to upload some sort of a file.  My SQL database has a few tables, but the important ones are Call_Tickets and Call_Tickets_File_Uploads.  When I insert a new call ticket, I need to grab the newest primary key value from Call_Tickets to use with an insert statement in the Call_Tickets_File_Uploads table. 

  How do I make the newRecordID available in the FormView1_ItemInserted event so I can process the insert of the uploaded file into the database?

  The iteminserted event is what I've read I need to use, but everything I find only has to do with using a Declarative SQLDataSource, which I am not using.  Below are a couple of code snippets that are relevant.  If I left anything out, let me know and I'll post more.

  This is from the code behind on my aspx page:

  protected void FormView1_ItemInserting(object sender, FormViewInsertEventArgs e)
          {
              DropDownList workType = (DropDownList)FormView1.FindControl("ins_ddl_workType");
              DropDownList subject = (DropDownList)FormView1.FindControl("ins_ddl_subject");
              TextBox description = (TextBox)FormView1.FindControl("ins_txt_Description");
              DropDownList priority = (DropDownList)FormView1.FindControl("ins_ddl_priority");
              Guid currentUserId = GetUser();
              int empID = CallTicketData.SelectUserRequestorEmpID(currentUserId);
              CallTicketData.InsertStaffCallTicket(empID, int.Parse(subject.Text), int.Parse(workType.Text), description.Text, int.Parse(priority.Text));
          }

  This is from my DAL:

  public static void InsertStaffCallTicket(int emp_ID, int subject_ID, int workType_ID, string description, int priorityID)
          {
              SqlConnection conn = GetConnection();
              SqlCommand comm = new SqlCommand("Insert_Staff_Call_Ticket", conn);
              comm.CommandType = CommandType.StoredProcedure;
              comm.Parameters.Add("empID", SqlDbType.Int).Value = emp_ID;
              comm.Parameters.Add("subjectID", SqlDbType.Int).Value = subject_ID;
              comm.Parameters.Add("workTypeID", SqlDbType.Int).Value = workType_ID;
              comm.Parameters.Add("description", SqlDbType.NVarChar).Value = description;
              comm.Parameters.Add("priorityID", SqlDbType.Int).Value = priorityID;
              comm.Parameters.Add("NewrecordID", SqlDbType.Int).Direction = ParameterDirection.Output;
              
              try
              {
                  conn.Open();
                  comm.ExecuteNonQuery();
              }
              finally
              {
                  conn.Close();
              }
          }

  Here is my SQL Stored Procedure:

  ALTER PROCEDURE [dbo].[Insert_Staff_Call_Ticket]
  	-- Add the parameters for the stored procedure here
  	@empID int
     ,@description nvarchar (max)
     ,@subjectID int
     ,@workTypeID int
     ,@priorityID int
     ,@NewrecordID int OUTPUT AS
  BEGIN
  	-- SET NOCOUNT ON added to prevent extra result sets from
  	-- interfering with SELECT statements.
  	SET NOCOUNT ON;
  
      -- Insert statements for procedure here
  	INSERT INTO Call_Tickets (Emp_ID, Description, Subject_ID, Work_Type_ID, Priority_ID, Resolution)
  	VALUES (@empID,@description,@subjectID,@workTypeID,@priorityID, '')
  	
  	SET @NewrecordID = SCOPE_IDENTITY()

  When I run the stored procedure in SSMS, the scope_identity returns the correct value so I know the SQL is right.

• • Reply
  

  ToughMan

  Participant

  1490 Points

  635 Posts

  Re: Formview using scope_identity(), ItemInserted event

  Nov 11, 2012 02:53 AM|LINK

  tennsmith

  When I run the stored procedure in SSMS, the scope_identity returns the correct value so I know the SQL is right.

  public static void InsertStaffCallTicket(int emp_ID, int subject_ID, int workType_ID, string description, int priorityID)
          {
              SqlConnection conn = GetConnection();
              SqlCommand comm = new SqlCommand("Insert_Staff_Call_Ticket", conn);
              comm.CommandType = CommandType.StoredProcedure;
              comm.Parameters.Add("empID", SqlDbType.Int).Value = emp_ID;
              comm.Parameters.Add("subjectID", SqlDbType.Int).Value = subject_ID;
              comm.Parameters.Add("workTypeID", SqlDbType.Int).Value = workType_ID;
              comm.Parameters.Add("description", SqlDbType.NVarChar).Value = description;
              comm.Parameters.Add("priorityID", SqlDbType.Int).Value = priorityID;
              comm.Parameters.Add("NewrecordID", SqlDbType.Int).Direction = ParameterDirection.Output;
              
              try
              {
                  conn.Open();
                  comm.ExecuteNonQuery();
      Response.Write(com.Parameters["NewrecordId"].DefaultValue.ToString());
              }
              finally
              {
                  conn.Close();
              }
          }

  • Edited by Frank Jiang - MSFT on Nov 22, 2012 09:47 AM
  • Edited by Frank Jiang - MSFT on Nov 22, 2012 09:47 AM
  • Marked as answer by Frank Jiang - MSFT on Nov 22, 2012 09:48 AM