Update data if exists

Question

I can update the data if the product size is exists. Assume that the table has a existing product_size (size 7) where product_stocks is 15, and I want to add size. For example I added a product stocks(15pcs.) on product_size(size 7), so the product stocks of size 7 is will be 30pcs. My problem is how can I add the data if the product size is not exists?

   Public Sub addProductsExtension()
        Try
            dbConnection()
            search_query = "SELECT * FROM tblproducts_extension WHERE product_code = @product_code;"
            command = New SqlCommand
            With command
                .Connection = connection
                .CommandText = search_query
                .Parameters.Clear()
                .Parameters.AddWithValue("@product_code", productsView.txtPCode.Text)
                dataReader = .ExecuteReader()
                If dataReader.HasRows Then
                    While dataReader.Read
                        updateExtension()
                        MsgBox("Updated stocks.")
                    End While
                Else
                    addExtension()
                End If
            End With
        Catch ex As SqlException
            MsgBox("Error : " + ex.Message)
        Finally
            connection.Close()
            command.Dispose()
        End Try
    End Sub 

    'these are the codes for adding size and stocks
    Public Sub addExtension()
        Try
            dbConnection()
            insert_query = "INSERT INTO tblproducts_extension(product_size, product_stocks, product_code) VALUES(@product_size, @product_stocks, @product_code);"
            command = New SqlCommand
            With command
                .Connection = connection
                .CommandText = insert_query
                .Parameters.Clear()
                .Parameters.AddWithValue("@product_size", productsView.comboSize.SelectedItem)
                .Parameters.AddWithValue("@product_stocks", productsView.txtProductStocks.Text)
                .Parameters.AddWithValue("@product_code", productsView.txtPCode.Text)
                result = .ExecuteNonQuery()
                If result = 0 Then
                    MsgBox("Error in adding data.")
                Else
                    MsgBox("Successfully added data.")
                End If
            End With
        Catch ex As SqlException
            MsgBox("Error : " + ex.Message)
        Finally
            connection.Close()
            command.Dispose()
        End Try
    End Sub

    'these are the codes for updating stocks if the product size is exists
Public Sub updateExtension()
        Try
            dbConnection()
            update_query = "UPDATE tblproducts_extension SET product_stocks = product_stocks + @product_stocks WHERE product_size = @product_size AND product_code = @product_code;"
            command = New SqlCommand
            With command
                .Connection = connection
                .CommandText = update_query
                .Parameters.Clear()
                .Parameters.AddWithValue("@product_size", productsView.comboSize.SelectedItem)
                .Parameters.AddWithValue("@product_stocks", productsView.txtProductStocks.Text)
                .Parameters.AddWithValue("@product_code", productsView.txtPCode.Text)
                .ExecuteNonQuery()
            End With
        Catch ex As SqlException
            MsgBox("Error : " + ex.Message)
        Finally
            connection.Close()
            command.Dispose()
        End Try
    End Sub

Answer 1

Your current code doesn't work because, in the initial SELECT, you search only for product_code. This is not enough to decide if you want to add or update a record because you need to add the new quantity to a product with a specific size. So you can simply add this other parameter to your select query (like you do in the UPDATE) and your code should work as is.

However, we can use the MERGE keyword that will update or insert your data with just one call.

string query = @"MERGE tblproducts_extension T
                 USING (1 as dummy) as S 
                 ON T.product_size = @product_size AND T.product_code = @product_code
                 WHEN NOT MATCHED THEN 
                     INSERT (product_code, product_size, product_stocks)
                     VALUES (@product_code, @product_size, @newQuantity)
                 WHEN MATCHED THEN 
                     UPDATE SET product_stocks = T.product_stocks + @newQuantity;";