SQLTeam.com | Weblogs | Forums

SP is inserting only ONE row of records. Any ideas?


#1

Greetings experts,

I am pretty stumped with this Stored Procedure.

I have an ASP.NET web form that dynamically creates rows so user can add as many records as possible.

By default, the first row is created. Once user fills out the cells in that row, s/he clicks to add another row.

The problem we are having now is that when the records are submitted to the database, only the first row gets inserted into the database. Any additional rows are ignored.

Can someone please help to get this resolved?

I have been stumped on this issue now for two days.

Here is my current SP:

ALTER PROCEDURE [dbo].[sp_AircraftSchedule]
@pid int,
@txYr smallint,
@ptype varchar(50),
@regno varchar(50),
@city varchar(50),
@cnty varchar(50),
@state varchar(50),
@mfgMake varchar(50),
@mfgModel varchar(50),
@yrBuilt int,
@SN varchar(50),
@dtePurchased date,
@aCost decimal(18,2),
@issues varchar(50),
@issuedetails varchar(250),
@hrsbtwn varchar(50),
@lastsince varchar(50),
@LasthrOv varchar(50),
@totHrsOnAifm varchar(50),
@AvionExtrEq varchar(500),
@fdate datetime,
@tID int
AS
BEGIN
  SET NOCOUNT ON;
  if exists (select * from [AircraftScheduleE] where pid = @pid AND TaxYear =@txYr AND Registration_No = @regno)
   begin
    UPDATE [dbo].[AircraftScheduleE]
    SET [TaxYear] = @txYr,
      [PurchaseType] = @ptype,
	  [Registration_No] = @regno,
      [City] = @city,
      [County] = @cnty,
      [State] = @state,
      [MFG_Make] = @mfgMake,
      [MFG_Model] = @mfgModel,
      [Year_Built] = @yrBuilt,
      [SerialNumber] = @SN,
      [DatePurchased] = @dtePurchased,
      [Aircraft_Cost] = ISNULL(@aCost,0),
      [aircraftissues] = @issues,
      [IssueDetails] = @issuedetails,
      [Hours_BTW_Overall] = @hrsbtwn,
      [Hours_Last_Since] = @lastsince,
      [Last_Hour_Overhauled] = @LasthrOv,
      [Total_HoursOnAirframe] = @totHrsOnAifm,
      [Avionics_Extra_Equip] = @AvionExtrEq,
      [FileDate] = @fdate
      where pid = @pid AND TaxYear =@txYr AND Registration_No = @regno
    end
   else
   	begin
 	INSERT INTO [dbo].[AircraftScheduleE]
	(
	 [PID],
	 [TaxYear],
	 [PurchaseType],
	 [Registration_No],
	 [City],
	 [County],
	 [State],
	 [MFG_Make],
	 [MFG_Model],
	 [Year_Built],
	 [SerialNumber],
	 [DatePurchased],
	 [Aircraft_Cost],
	 [aircraftissues],
	 [IssueDetails],
	 [Hours_BTW_Overall],
	 [Hours_Last_Since],
	 [Last_Hour_Overhauled],
	 [Total_HoursOnAirframe],
	 [Avionics_Extra_Equip],
	 [FileDate],
	 TaxpayerID
	) 
	VALUES
	(
	 @pid,
	 @txYr,
	 @ptype,
	 @regno,
	 @city,
	 @cnty,
	 @state,
	 @mfgMake,
	 @mfgModel,
	 @yrBuilt,
	 @SN,
	 @dtePurchased,
	 ISNULL(@aCost,0),
	 @issues,
	 @issuedetails,
	 @hrsbtwn,
	 @lastsince,
	 @LasthrOv,
	 @totHrsOnAifm,
	 @AvionExtrEq,
	 @fdate,
	 @tID
	)
  end

END


#2

not sure this is relevant, but that Procedure will insert one row - "storing" the @parameters into the various Columns for that row.

So if your ASP.NET form has a grid of data that the user enters the Procedure needs to be called once PER ROW when the Grid is "saved" by the user.

Sounds like the SUBMIT is only processing the first row in the Grid?


#3

Hi Kristen,

I had .NET experts review my code and determined it is working as expected.

They suggested I make my SP need to insert comma separated values but not sure how to do that.

Essentially, the way the .net code works is that you have an ID called Registration_No.

If you enter a value say, V123 in one row, click add new row and enter another value say TU00.

Once you hit Next button, two detail forms are created, one for each Registration_No.

Once you feel those and submit to the database, only details values for the first value of V123 get inserted into the database.

The second Registration_No and associated details are ignored.


#4

That would be nuts IMHO. Many columns, many rows, all sorts of issues with embedded commas and quotes in the Users' own data ...

You can pass an Array Object, which would do a similar job - i.e. it would encapsulate the Rows and Columns into a TableVariable, but its somewhat complex to do, but rather than change the SQL Procedure to be capable of processing N-rows I would leave it as it is and call it N-times from the APP instead. That will be less efficient, but assuming it isn't being called lots of times a second that won't be an issue, and it will be a lot easier than making the Procedure handle N-rows and N-columns.

There might be a smarter way to this (ie.. "natively") in ASP.NET, its not something I'm familiar with (but it must be a commonly occurring requirement), so might be worth a Google for methods of creating multiple rows.


#5

P.S. A different approach would be to use AJAX to "submit" each row as it is completed, rather than the user creating N-rows and THEN inserting them into the database.

What happens if the user gets half way through adding the rows and their PC crashes? Should they have nothing? (i.e. "all or nothing") or would it be OK if all the rows that they had already completed were in the database (i.e. only "PART" of the total data is in the database, which is fine if they remember to complete it, but generally speaking not good news if "half finished" data is lying around in the DB !!


#6

Well, we created an auto save for the user so that in the case the PC crashes, s/he can get back to where s/he was before crashing without losing data.

Creating the multiple rows as I stated earlier, is not the problem.

We are doing that successfully. What makes this current problem challenging is that you have to create the Registration numbers first. That's the spec. Then once the Registration numbers are created, then you use them to create detailed records associated with each account number.

For instance, using similar example from my last post, I enter Registration_No V1030 in first row, I add another blank row and enter Registration_No T101P.

Here is the key, based on the fact that two Registration_Nos are created, two detailed sections are automatically created, each section associated with each of the values of Registration_No.

Section One is assigned Registration_No V1030 and Section Two is assigned value for Registration_NO T101P.

Once these sections are completed and submitted to the database, only the first row with Registration_No T101P gets inserted into the database.

The second is ignored.

When I spoke of coma separated values, this is the example they gave:

https://www.aspsnippets.com/Articles/Save-and-Retrieve-Dynamic-TextBox-values-in-GridView-to-SQL-Server-Database.aspx

The issue here is that the INSERT statement is embedded in asp.net while in our case, we are using stored procedure.


#7

I've only glanced at it, but try it: try putting a "," in one of the data boxes, at my quick glance I reckon that will break it. Huge amount of code for the job at hand, and all sorts of traps and bug opportunities. Also prone to "sql injection" - if you build a test-bed, using that code, try putting (variations on) ,'--; DELETE SomeTableName;-- in one of the fields. With the correct "value" that will cause that table in the database to be emptied ... its a dreadful way of building an application I'm afraid. But of course you could do something similar but use Procedures instead, and properly parameterised they will be immune to SQL Injection.

I don't know how you debug your application, but you haven't got any error handling in your Procedure so it may be failing without you getting any indication. Also, I presume? you don't actually know how many times it is called (you observation is that "The second is ignored" - it might well be that it IS being called twice, but on BOTH occasions with the correct Parameters to update the FIRST row - so what you need to find out, by debugging, is what is ACTUALLY happening).

Personally I would add some Logging code. We have logging, as I will describe below, in EVERY procedure in our applications. That logs EVERY Procedure call and updates the log record when that procedure returns (recording either Error=0 or an Error Number). Our APP creates millions of such logging rows a day - so in terms of "throughput" it is extremely unlikely to be a burden on the server).

You could use SQL Profiler to spy on the SQL statements sent to the server instead, but IME that only tells part of the story and finding things like "where did the error occur within the procedure", "What were the parameters?" and "What child Sub-Procedure was called" are hard to answer with SQL Profiler, but all of those are quickly answered with Logging.

Create a Logging table with columns like:

ID -- as IDENTITY
StartDateTime
FinishDateTime
ErrorNo
ProcedureName
Parameters -- as NVarchar(MAX)
Comment -- as NVarchar(MAX)

If you have some sort of Session ID I recommend including that too (AND passing it as a parameter to every Procedure that you call - for us its the first parameter in every procedure we write). Then you can report on the chronological sequence of procedures, and sub-procedure calls, that occurred from a given session.

Then add a call to LOG the Procedure name and Parameters (at the top of each of the procedures you want to log)

DECLARE @LogID int, @Parameters NVarchar(MAX)
SELECT @Parameters='@pid=' + COALESCE(CONVERT(varchar(20), @pid), 'NULL')
                  +', @txYr=' + COALESCE(CONVERT(varchar(20), @txYr), 'NULL')
                  +', @ptype=' + COALESCE('''' + @ptype + '''', 'NULL')
... etc ...
EXEC @LogID = dbo.YourLogRecordCreate
             @ErrorNo = -999 -- Pseudo value indicating "running" or "aborted"
             , @ProcedureName = 'sp_AircraftSchedule'
             , @Parameters = @Parameters

and then at the bottom of your Procedure call another procedure to Log the "Exit" - including the Error No if any.

EXEC dbo.YourLogRecordUpdate
             @LogID = @LogID
             , @ErrorNo = @YourActualErrorNo --0=No error
             , @Comment = NULL -- Or any other "useful result data" such as RowCount

YourLogRecordCreate should insert a new row in Log table, and set the StartDateTime = GetDate(). It should return the ID of the log record (for the caller to use to update the log record when it returns / exits)

YourLogRecordUpdate should update the existing log record and set FinishDateTime = GetDate() and optionally set the Comment columns

In case important: note that procedures that start with "sp_" cause SQL to check for the existence of that procedure in the MASTER database, so that "name resolution" is slower than for procedures which start with some other prefix - which then just go straight to the procedure (in the current database).

Similarly, if you do

EXEC sp_AircraftSchedule

then SQL will check the user's current schema for that procedure and, when not found, with THEN check the "dbo" schema. Whereas if you do:

EXEC dbo.sp_AircraftSchedule
or
EXEC SpecificSchemaName.sp_AircraftSchedule

SQL will go directly to the appropraite schema.

You may, of course, want SQL to use the current user's schema - e.g. you have different copies of the same named Procedure / View / etc. in order to handle user-specific permissions, and so on - but most of the time that's not the case - so important to explicitly name the Schema when you do NOT want it to be ambiguous :slight_smile:"