Problems making an insert with 8 different SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problems making an insert with 8 different SP

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-01-13 : 08:14:37

I have an insert process divided in 8 Stored Procedures. Depending on the user selections in a form of the application, are executed a combination of 1, 2 or 3 of these SP. For example can be executed the SP 1, or the SP 3 and 5, or the SP 4, 6 and 8.

All this happens when the user submits a form from the app, and works fine. The problem is after the user has submitted the form, if he/she change his/her mind and wants to do other selections in the same form and re-submit it, then the old entry data are not updated.

For example, if one user submitted the form and executed the SP 3 and 5, then he/she goes back and change the form selections, re-submit the form, and it is executed the SP 6 and 8, the SP 3 and 5 are not updated, so, now I will have in the database the data from SP 3, 5, 6 and 8, when I only wanted the last one, the SP 6 and 8.

How can I solve this problem?

Thank you,
Cesar

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-13 : 08:24:57

It's a design issue.
Probably should redisplay the form so that the user is updating the data in the database based on the info after the update.
You might also need to prevent the same data being updated from diffent connections.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cesark
Posting Yak Master

215 Posts

Posted - 2005-01-13 : 09:17:04

Hi,

"Probably should redisplay the form so that the user is updating the data in the database based on the info after the update."

This web form is dynamic, this means that the user can hide and/or show different parts of the form. So, if he/she change the old form selections and show and fill other parts of the form, only the new SP will be exectued, and the old ones exectued will not be updated putting null values.

I don' t know how to do it?..

cesark
Posting Yak Master

215 Posts

Posted - 2005-01-13 : 11:22:09

My question is a pure database question. But first I should explain a simple example of my application form page to understand the scenario.

In the application imagine a web form with two check boxes, A and B. If you select the box A, two input text elements appears (input text Quality_A and input text Quality_B), and if you select the text box B two different input text elements appears (input text Quality_C and input text Quality_D).

Imagine that you select the check box A, and fill the two input text Quality_A and Quality_B thus:

Quality_A: A pair of trousers ref. 258
Quality_B: A jacket ref. 632

Then you submit the form and the SP called insert_Product_A makes the update into the DB. After that, you change your opinion and you prefer to fill the form selecting the check box B and fill the fields Quality_C and Quality_D instead of Quality_A and Quality_B. To do so, you press a link to go back to the form page, and the form is load with the previous typed data querying the database correctly (this is not the problem now). Then you will get the form with the old correct typed data, so, the form with the check box A selected and the two text boxes:

Quality_A: A pair of trousers ref. 258
Quality_B: A jacket ref. 632

But now you select the check box B instead of A, and the text boxes Quality_A and Quality_B are hidden, and instead appears the other two text fields Quality_C and Quality_D, and you fill it:

Quality_C: A pair of trousers ref. 258
Quality_D: A jacket ref. 632

Then you submit the form and the SP called insert_Product_B makes the update into the DB. But now, you will get into the DB the four fields filled, Quality_A, Quality_B, Quality_C and Quality_D, but your last decision was introduce only data for Quality_C and Quality_D fields. This happened because in every case the update is made with two different SP. In this very simple case, if I wanted to solve the problem I would have only to write the SP insert_Product_A and insert_Product_B so that every time are executed the fields Quality_C and Quality_D are updated as null in the SP insert_Product_A, and the fields Quality_A and Quality_B are updated as null in the SP insert_Product_B.

I could do the same in my case, but the number of possible form fields in the page is very high and also the number of possible combinations between them. Then in every SP I use to update the database, I would have to update as null values the rest of the fields, which in my case are 125 fields.

So, every SP of the 8, would be something like:


USE market
GO
CREATE PROC insert_HQ
@Offer_id bigint, @Param1 numeric(6,2) , @Param2 smallint , 
@Param3 smallint, @Param4 smallint, 
@Param5 numeric(8,2),  @Param6 smallint, 
@Param7 smallint, @Param8 numeric(8,2), 
@Param9 smallint, @Param10 varchar(256), @Param11 int, 
@Param12 smallint, @Param13 numeric(8,2), 
@Param14 smallint, @Param15 varchar(1000), 
@Param16 varchar(256) output   


-- Then to make null the rest of values

@Param17 numeric(6,2) = null, @Param18 smallint = null, 
@Param19 smallint = null, @Param20 smallint = null, 
@Param21 numeric(8,2) = null,  @Param22 smallint = null, 
@Param23 smallint = null, @Param24 numeric(8,2) = null, 
@Param25 smallint = null, @Param26 varchar(256) = null, @Param27 int = null, 
@Param28 smallint = null, @Param29 numeric(8,2) = null, 
@Param30 smallint = null, @Param31 varchar(1000) = null, etc,...
.....
-- and thus until the parameter  @Param141

This is a good solution?

cesark
Posting Yak Master

215 Posts

Posted - 2005-01-13 : 17:54:01

I have a better solution, here is a piece of the logic that decides the SP to execute:


Sub updateData(Sender As Object, E As EventArgs)
 Dim inf_user As New Session_menu()
     
  If difQual_High.Checked Then  
    If Not difQual_Mid.Checked Then 
     If Not difQual_Low.Checked Then   ' Case checked - unchecked - unchecked 
		
	   If Dif_Mad_QH.Checked Then
 
         ‘ Exec the SP up_QH_M	  
         ‘ Exec a SP to put null values except for up_QH_M fields 
           Response.Redirect("new_offer_ok.aspx")

	   Else
 
        ‘ Exec the SP up_QH 
        ‘ Exec a SP to put null values except for up_QH fields 
          Response.Redirect("new_offer_ok.aspx")

	   End If	  

 
 
     Else  ' Case checked - unchecked - checked  
	 
	   If Dif_Mad_QH.Checked Then
 
                 ‘ Exec the SP up_QH_M


        If Dif_Mad_QL.Checked Then

               ‘ Exec the SP up_QL_M
               ‘ Exec a SP to put null values except for up_QH_M and up_QL_M fields  	
                 Response.Redirect("new_offer_ok.aspx")


        
		Else
		
		‘ Exec the SP up_QL    
                ‘ Exec a SP to put null values except for up_QH_M and up_QL fields  	
                  Response.Redirect("new_offer_ok.aspx")

		
		End If
				
		

	   Else

        ‘ Exec the SP up_QH    
		

        If Dif_Mad_QL.Checked Then

          ‘ Exec the SP up_QL_M  	            	  
          ‘ Exec a SP to put null values except for up_QH and up_QL_M fields  	
            Response.Redirect("new_offer_ok.aspx")


	 
        Else
     
          ‘ Exec the SP up_QL    
          ‘ Exec a SP to put null values except for up_QH and up_QL fields  	
            Response.Redirect("new_offer_ok.aspx")
 

	    End If	  
	   End If	

	  	 	  	 
     End If	     
	   
                        
    Else  

Etc,...
........

What do you think?

cesark
Posting Yak Master

215 Posts

Posted - 2005-01-17 : 05:14:52

Because if I use one only SP, in the application I have to repeat a lot of the same code passing the same data to the SP. This is because the same kind of data update, for example the block ‘up_QH_M’ is used many times along the big subroutine in the application. Then I have to repeat exactly the same code many times passing the data to the SP, instead, if I use a different SP every time I can write one only subroutine for every SP, and call these subroutines from the big subroutine, without having to write them every time is needed in the big subroutine. Thus, reusing code.

For example, big sub: (ASP.NET VB language)


Sub updateData(Sender As Object, E As EventArgs)
 Dim inf_user As New Session_menu()
     
  If difQual_High.Checked Then  
    If Not difQual_Mid.Checked Then 
     If Not difQual_Low.Checked Then   ' Case checked - unchecked - unchecked 
		
	   If Dif_Mad_QH.Checked Then
 
         ‘ Exec the SP up_QH_M calling up_QH_M Sub	  
              ‘ Exec the SP up_QM_M calling up_QM_M Sub       
                Response.Redirect("new_offer_ok.aspx")

	   Else
...

‘ Those "Exec the SP up_QH_M" and "Exec the SP up_QM_M" are repeated many times along the sub joint with others. 

End Sub

And the reusable code: (ASP.NET VB language)


Sub up_QH_M()

        Dim CmdInsert As New SqlCommand("update_HQ_M", strConnection)
        CmdInsert.CommandType = CommandType.StoredProcedure
	    
        CmdInsert.Parameters.Add("@Offer_id", SqlDbType.bigint)
        CmdInsert.Parameters("@Offer_id").Value = inf_user.offer_id

        CmdInsert.Parameters.Add("@Product_id", SqlDbType.smallint, 2)
        CmdInsert.Parameters("@Product_id").Value = product_list.SelectedItem.Value
   
        ...

        strConnection.open()
          CmdInsert.ExecuteNonQuery
        strConnection.close()
	

End Sub

Do you understand it now?

cesark
Posting Yak Master

215 Posts

Posted - 2005-01-17 : 05:20:12

This is the explanation why I use 8 different SP instead of only one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources