One button insert update button or Enable Disable Insert and update buttons

Southern_Gentleman
Southern_Gentleman used Ask the Experts™
on
I have a webform that has dropdowns and textboxes. I created a stored procedure and code behind to insert the data, now i'm trying to consolidate an update were the user would only need to use one button. Once the button is inserted or saved the button is clicked it would only show update. It would only show update or do an update if the Primary Key is created so there would have to be a select statement as well. I could even accept a way where there are two buttons. After empty webform is loaded the insert button is enabled but update disabled and once insert button is clicked it is disabled indefinitely and update is enabled indefinitely since there is already a newly created primary key.  

here are the insert and updates in the code behind:

Insert code:
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs)

        Dim strConnString As String = ConfigurationManager.ConnectionStrings("MerchData").ConnectionString
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand()

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "[dbo].[InsertPurchaseOrder]"
        cmd.Parameters.Add("@Suppint", SqlDbType.VarChar).Value = ddlSupplier.Text.Trim()
        cmd.Parameters.Add("@VendorCode", SqlDbType.VarChar).Value = tbsuppliercode.Text.Trim()
        cmd.Parameters.Add("@classgroupAllocID", SqlDbType.VarChar).Value = If(ddlclass.Text = String.Empty, DBNull.Value, CType(ddlclass.Text, String))
        cmd.Parameters.Add("@Season", SqlDbType.VarChar).Value = If(ddlSeason.Text = String.Empty, DBNull.Value, CType(ddlSeason.Text, String))
        cmd.Parameters.Add("@quarter", SqlDbType.VarChar).Value = If(ddlquarter.Text = String.Empty, DBNull.Value, CType(ddlquarter.Text, String))
        cmd.Parameters.Add("@Month", SqlDbType.VarChar).Value = If(ddlMonth.Text = String.Empty, DBNull.Value, CType(ddlMonth.Text, String))
        cmd.Parameters.Add("@DateAllocated", SqlDbType.Date).Value = If(DateAllocated.Text = String.Empty, DBNull.Value, CType(DateAllocated.Text, Date))
        cmd.Parameters.Add("@StyleCreated", SqlDbType.VarChar).Value = If(ddlStyleCreated.Text = String.Empty, DBNull.Value, CType(ddlStyleCreated.Text, String))
        cmd.Parameters.Add("@EDIT", SqlDbType.Bit).Value = If(ChkEdit.Checked, 1, 0)
        cmd.Parameters.Add("@Category", SqlDbType.VarChar).Value = If(ddlCategory.Text = String.Empty, DBNull.Value, CType(ddlCategory.Text, String))
        cmd.Parameters.Add("@POID", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
            Dim poid As String = cmd.Parameters("@POID").Value.ToString()
            lblPOID.Text = poid
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
            con.Dispose()
        End Try

    End Sub

Open in new window


Here is the update
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("MerchData").ConnectionString
    Dim con As New SqlConnection(strConnString)
    Dim cmd As New SqlCommand()

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "[Merch].[UpdatePurchaseOrderReturnIDwithOutput]"
        cmd.Parameters.Add("@Suppint", SqlDbType.VarChar).Value = ddlSupplier.Text.Trim()
        cmd.Parameters.Add("@VendorCode", SqlDbType.VarChar).Value = tbsuppliercode.Text.Trim()
        cmd.Parameters.Add("@classgroupAllocID", SqlDbType.VarChar).Value = If(ddlclass.Text = String.Empty, DBNull.Value, CType(ddlclass.Text, String))
        cmd.Parameters.Add("@Season", SqlDbType.VarChar).Value = If(ddlSeason.Text = String.Empty, DBNull.Value, CType(ddlSeason.Text, String))      
        cmd.Parameters.Add("@quarter", SqlDbType.VarChar).Value = If(ddlquarter.Text = String.Empty, DBNull.Value, CType(ddlquarter.Text, String))
        cmd.Parameters.Add("@Month", SqlDbType.VarChar).Value = If(ddlMonth.Text = String.Empty, DBNull.Value, CType(ddlMonth.Text, String)
        cmd.Parameters.Add("@DateAllocated", SqlDbType.Date).Value = If(DateAllocated.Text = String.Empty, DBNull.Value, CType(DateAllocated.Text, Date))
        cmd.Parameters.Add("@StyleCreated", SqlDbType.VarChar).Value = If(ddlStyleCreated.Text = String.Empty, DBNull.Value, CType(ddlStyleCreated.Text, String))
        cmd.Parameters.Add("@EDIT", SqlDbType.Bit).Value = If(ChkEdit.Checked, 1, 0)
        cmd.Parameters.Add("@Category", SqlDbType.VarChar).Value = If(ddlCategory.Text = String.Empty, DBNull.Value, CType(ddlCategory.Text, String))
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
    Throw ex
        con.Close()
        con.Dispose()
    End Try

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I would look at a single stored procedure:  processPurchaseOrder.

You can check the value of lblPOID.Text to see if the button text is "Insert" or "Update"

The single button calls processPurchaseOrder.  I'm not a SQL Server Expert but from a quick Google, an OUTPUT parameter in a procedure acts as an "input/ouptut" parameter.  Inside the procedure, if POID is passed in, execute the update statement.  If it is null, execute the insert and and assign POID.  The .Net code can then retrieve the value.

Author

Commented:
The hitch that i have is that i'm using two sql tables to insert my single web form and i'm using a scope identity to take the primary key to insert it as a foreign key. If i do use a single stored procedure, i still have to code it for the onclick event. Is there an example code for the single stored procedure and code behind.

My current insert stored procedure is the following
ALTER PROCEDURE [dbo].[InsertPurchaseOrder]
	(
	@Suppint Varchar(50),
	@VendorCode Varchar(10),
	@classgroupAllocID Varchar(10),
	@Season Varchar(4),
	@quarter Varchar(50),
	@Month Varchar(50),
	@DateAllocated Date,
	@StyleCreated Varchar(5),
	@EDIT BIT,
	@Category VARCHAR(50),
	@POID INT OUTPUT
	)
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO [Merch].[PurchaseOrder] (Suppint,VendorCode,classgroupAllocID,Season,[quarter],CreateDate)
	VALUES (@Suppint,@VendorCode,@classgroupAllocID,@Season,@quarter,GETDATE());
	SET @POID = SCOPE_IDENTITY();
	
	INSERT INTO [Merch].[purchaseOrder2](POID,[Month],DateAllocated,StyleCreated,EDIT,Category,CreateDate) 
	VALUES (@POID,@Month,@DateAllocated,@StyleCreated,@EDIT,@Category,GetDATE());

End

Open in new window


**if the name of the stored procedure for the update code behind is different than the insert it was a mistake. I forgot to shorten it for demo purposes.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>i still have to code it for the onclick event

Sorry but I'm not following what you are trying to tell me.

In the processPurchaseOrder stored procedure, first thin is look at the POID value.  If it is null, you are doing an "insert".  If not null, you are doing an "update".  Should be simple IF/THEN logic. in the procedure.  Inside the procedure, you can do as many inserts/updates/deletes on as many tables as you want.  The .Net code doesn't care.

You have a single button and the onclick event calls a single SQL Server stored procedure.  All the "magic" is performed in the SQL Server procedure looking at the value passed in for POID.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Author

Commented:
I'm not doing a gridview so i'm not going to do a delete.  no matter how you do the insert and update in a single store procedure, i still have to map the columns in the stored procedure's table columns to the individual textbox and dropdown item controls from the web application, that's is why i have to do a code behind. All i'm looking for is a example from the code samples i put in my original question.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I cannot provide you a working example.  I don't have access to SQL Server or .Net at the moment.

I'm confused:  I thought you already had the insert code working.

I'm suggesting a small tweak to the codebehind to look at the value of lblPOID.Text when the form loads.  If it is empty, change the button text to "Insert".  If it has a value, the button text is "Update".

Most of the rest of the code is the same.

Inside the stored procedure, look at the value of POID.  If it is null it signifies an Insert and the code you have works.

Add an IF statement inside the procedure something like (untested):
ALTER PROCEDURE [dbo].[ProcessPurchaseOrder]
	(
	@Suppint Varchar(50),
	@VendorCode Varchar(10),
	@classgroupAllocID Varchar(10),
	@Season Varchar(4),
	@quarter Varchar(50),
	@Month Varchar(50),
	@DateAllocated Date,
	@StyleCreated Varchar(5),
	@EDIT BIT,
	@Category VARCHAR(50),
	@POID INT OUTPUT
	)
AS
BEGIN
	SET NOCOUNT ON;

	IF POID is null then
		BEGIN
			INSERT INTO [Merch].[PurchaseOrder] (Suppint,VendorCode,classgroupAllocID,Season,[quarter],CreateDate)
			VALUES (@Suppint,@VendorCode,@classgroupAllocID,@Season,@quarter,GETDATE());
			SET @POID = SCOPE_IDENTITY();
			
			INSERT INTO [Merch].[purchaseOrder2](POID,[Month],DateAllocated,StyleCreated,EDIT,Category,CreateDate) 
			VALUES (@POID,@Month,@DateAllocated,@StyleCreated,@EDIT,@Category,GetDATE());
		END
	ELSE
		BEGIN
			--perform your updates
		END
	END
End

Open in new window

Author

Commented:
I got that part already, I'm looking for guidance of the onclick or button event.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
So, you do or do not have Insert working with the button click?

Author

Commented:
I do have the insert function from the my initial question, I keep inserting a new line in the tables. So there is something wrong either with the stored procedure or i need to add an update code with the insert code in the Code Behind of the ASP page.
All the examples i see online don't use parameters in the click event and i need to worry about security.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>All the examples i see online don't use parameters in the click event and i need to worry about security.

This is what is confusing me:  What parameters do you think you need to add?

>>I do have the insert function from the my initial question,

Then you have everything I'm saying you need.  There is no code change on the .Net side other than to change the button text and tweak the POID parameter.

You call the same stored procedure from the same block of code.  Nothing should need to change.  Everything is driven by lblPOID.Text.  If it doens't have a value, you will be inserting.  If it has a value, you'll be updating.  It should be that simple.

Untested but from the docs and MSDN forums, declare it as InputOutput and set the value:
prm = New SqlClient.SqlParameter
With prm
  .ParameterName = "@Msg"
  .SqlDbType = SqlDbType.VarChar
  .Direction = ParameterDirection.InputOutput
  .Size = 200
  .Value = If(lblPOID.Text = String.Empty, DBNull.Value, CType(lblPOID.Text, String))
End With
cmd.Parameters.Add(prm)

Open in new window


https://social.msdn.microsoft.com/Forums/sqlserver/en-US/01049a0e-c255-4172-9353-192e10a745e9/default-output-parameter-value-is-not-returned?forum=transactsql
https://docs.microsoft.com/en-us/dotnet/api/system.data.parameterdirection?view=netframework-4.8

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial